Home » SQL & PL/SQL » SQL & PL/SQL » Value multiple (Sql Plus, Oracle 10G, Windows)
| Value multiple [message #686697] |
Fri, 25 November 2022 10:30  |
 |
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
|
Senior Member |
|
|
Dear Experts
Following is the Table and Insert statement.
CREATE TABLE TEST
(
TITLE VARCHAR2(199 BYTE),
AMOUNT NUMBER,
VALUE NUMBER
)
INSERT INTO TEST ( TITLE, AMOUNT, VAL ) VALUES (
Switch, 3000, 3);
COMMIT;
now we need to calculate,
val is an Input at runtime
val = 3, e.g. val always change 4,5 or 7, or 8
So
3000 multiply with 1 = 3000
3000 multiply with 2 = 6000
3000 multiply with 3 = 9000
its multiply upto 3 because the input value is 3 it may vary, so accordingly column will increase
Out put
Title Amount 1 2 3 Total
Switch 30000 3000 6000 9000 21000
regards,
|
|
|
|
|
|
| Re: Value multiple [message #686700 is a reply to message #686698] |
Fri, 25 November 2022 16:45  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I concur with Michel - why do you add amount twice? Anyway, you can't do it in pure SQL if VAL is known at run time only. Things like this are done using tools like reports. Or, you could do something like this in SQL*Plus, but since you are on 10g that doesn't have LISTAGG you will have to create STRAGG function (there are plenty examples on the web including this site):
column select_list new_value select_list noprint
accept user_input prompt "Please enter value: "
with t as (
select 'amount * ' || n || ' "' || n || '"' x
from test,
lateral(
select level n
from dual
connect by level <= &&user_input
)
)
select stragg(x || ',') select_list
from t
/
select title "Title",
amount "Amount",
&select_list
amount * (&&user_input + 1) * &&user_input / 2 + amount "Total"
from test
/
SQL> column select_list new_value select_list noprint
SQL> accept user_input prompt "Please enter value: "
Please enter value: 3
SQL> with t as (
2 select 'amount * ' || n || ' "' || n || '"' x
3 from test,
4 lateral(
5 select level n
6 from dual
7 connect by level <= &&user_input
8 )
9 )
10 select stragg(x || ',') select_list
11 from t
12 /
old 7: connect by level <= &&user_input
new 7: connect by level <= 3
SQL> select title "Title",
2 amount "Amount",
3 &select_list
4 amount * (&&user_input + 1) * &&user_input / 2 + amount "Total"
5 from test
6 /
old 3: &select_list
new 3: amount * 1 "1",amount * 2 "2",amount * 3 "3",
old 4: amount * (&&user_input + 1) * &&user_input / 2 + amount "Total"
new 4: amount * (3 + 1) * 3 / 2 + amount "Total"
Title Amount 1 2 3 Total
---------- ---------- ---------- ---------- ---------- ----------
Switch 3000 3000 6000 9000 21000
SQL> accept user_input prompt "Please enter value: "
Please enter value: 5
SQL> with t as (
2 select 'amount * ' || n || ' "' || n || '"' x
3 from test,
4 lateral(
5 select level n
6 from dual
7 connect by level <= &&user_input
8 )
9 )
10 select stragg(x || ',') select_list
11 from t
12 /
old 7: connect by level <= &&user_input
new 7: connect by level <= 5
SQL> select title "Title",
2 amount "Amount",
3 &select_list
4 amount * (&&user_input + 1) * &&user_input / 2 + amount "Total"
5 from test
6 /
old 3: &select_list
new 3: amount * 1 "1",amount * 2 "2",amount * 3 "3",amount * 4 "4",amount * 5 "5",
old 4: amount * (&&user_input + 1) * &&user_input / 2 + amount "Total"
new 4: amount * (5 + 1) * 5 / 2 + amount "Total"
Title Amount 1 2 3 4 5 Total
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Switch 3000 3000 6000 9000 12000 15000 48000
SQL>
|
|
|
|
|
|
|
|
| Re: Value multiple [message #686703 is a reply to message #686702] |
Sat, 26 November 2022 05:32 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Actually, solution I posted is wrong - it will not work if table has multiple rows. Correct solution is simpler. I also adjusted total based on your reply:
column select_list new_value select_list noprint
set verify off
accept user_input prompt "Please enter value: "
select stragg('amount * ' || level || ' "' || level || '",') select_list
from dual
connect by level <= &&user_input
/
select title "Title",
amount "Amount",
&select_list
amount * (&&user_input + 1) * &&user_input / 2 "Total"
from test
/
SQL> select * from test;
TITLE AMOUNT
---------- ----------
Switch 3000
Router 2000
Adapter 1000
SQL> column select_list new_value select_list noprint
SQL> set verify off
SQL> accept user_input prompt "Please enter value: "
Please enter value: 7
SQL> select stragg('amount * ' || level || ' "' || level || '",') select_list
2 from dual
3 connect by level <= &&user_input
4 /
SQL> select title "Title",
2 amount "Amount",
3 &select_list
4 amount * (&&user_input + 1) * &&user_input / 2 "Total"
5 from test
6 /
Title Amount 1 2 3 4 5 6 7 Total
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Switch 3000 3000 6000 9000 12000 15000 18000 21000 84000
Router 2000 2000 4000 6000 8000 10000 12000 14000 56000
Adapter 1000 1000 2000 3000 4000 5000 6000 7000 28000
SQL> accept user_input prompt "Please enter value: "
Please enter value: 3
SQL> select stragg('amount * ' || level || ' "' || level || '",') select_list
2 from dual
3 connect by level <= &&user_input
4 /
SQL> select title "Title",
2 amount "Amount",
3 &select_list
4 amount * (&&user_input + 1) * &&user_input / 2 "Total"
5 from test
6 /
Title Amount 1 2 3 Total
---------- ---------- ---------- ---------- ---------- ----------
Switch 3000 3000 6000 9000 18000
Router 2000 2000 4000 6000 12000
Adapter 1000 1000 2000 3000 6000
SQL>
|
|
|
|
|
|
| Re: Value multiple [message #686711 is a reply to message #686703] |
Mon, 28 November 2022 11:05 |
|
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
|
Senior Member |
|
|
Dear Solomon,
I have tried to understand stragg function using below site, but do not understand how to use, appreciated if you guide to understand,
https://community.oracle.com/tech/apps-infra/discussion/1022313/convert-columns-to-row-equivalent-to-stragg-function-in-oracle-sql
I have tried the following website but showing error
https://www.ndsapps.com/webhelp/stragg_sql_query_only.htm
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 28 22:13:58 2022
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set wrap off
SQL> set recap off
SP2-0158: unknown SET option "recap"
SQL> set recsep off
SQL> column list wrap on
SQL> set serveroutput on
SQL> column select_list new_value select_list noprint
SQL> set verify off
SQL> accept user_input prompt "Please enter value: "
Please enter value: 6
SQL> select stragg('amount * ' || level || ' "' || level || '",') select_list
2 from dual
3 connect by level <= &&user_input
4 ;
select stragg('amount * ' || level || ' "' || level || '",') select_list
*
ERROR at line 1:
ORA-00904: "STRAGG": invalid identifier
SQL>
Thanks,
Regards,
[Updated on: Mon, 28 November 2022 11:54] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Value multiple [message #686722 is a reply to message #686714] |
Fri, 02 December 2022 07:21 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Why did you wrap SELECT in BEGIN END? And where is column select_list new_value select_list noprint? Execute:
column select_list new_value select_list noprint
set verify off
accept user_input prompt "Please enter value: "
select stragg('amount * ' || level || ' "' || level || '",') select_list
from dual
connect by level <= &&user_input
/
select title "Title",
amount "Amount",
&select_list
amount * (&&user_input + 1) * &&user_input / 2 "Total"
from test
/
[Updated on: Fri, 02 December 2022 07:22] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Value multiple [message #686730 is a reply to message #686729] |
Sat, 03 December 2022 04:44 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And if you have (which you shouldn't) troubles with STRAGG you could use MODEL:
Run this for user input:
column select_list new_value select_list noprint
set verify off
accept user_input prompt "Please enter value: "
select select_list
from dual
model
dimension by (1 as row_num)
measures(cast(null as varchar2(4000)) select_list)
rules
iterate(1000) until iteration_number + 1 = &&user_input
(
select_list[1] = select_list[1] || 'amount * ' || (iteration_number + 1) || ' "' || (iteration_number + 1) || '",'
)
/
select title "Title",
amount "Amount",
&select_list
amount * (&&user_input + 1) * &&user_input / 2 "Total"
from test
/
SQL> column select_list new_value select_list noprint
SQL> set verify off
SQL> accept user_input prompt "Please enter value: "
Please enter value: 5
SQL> select select_list
2 from dual
3 model
4 dimension by (1 as row_num)
5 measures(cast(null as varchar2(4000)) select_list)
6 rules
7 iterate(1000) until iteration_number + 1 = &&user_input
8 (
9 select_list[1] = select_list[1] || 'amount * ' || (iteration_number + 1) || ' "' || (iteration_number + 1) || '",'
10 )
11 /
SQL> select title "Title",
2 amount "Amount",
3 &select_list
4 amount * (&&user_input + 1) * &&user_input / 2 "Total"
5 from test
6 /
Title Amount 1 2 3 4 5 Total
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Switch 3000 3000 6000 9000 12000 15000 45000
Router 2000 2000 4000 6000 8000 10000 30000
Adapter 1000 1000 2000 3000 4000 5000 15000
SQL> accept user_input prompt "Please enter value: "
Please enter value: 3
SQL> select select_list
2 from dual
3 model
4 dimension by (1 as row_num)
5 measures(cast(null as varchar2(4000)) select_list)
6 rules
7 iterate(1000) until iteration_number + 1 = &&user_input
8 (
9 select_list[1] = select_list[1] || 'amount * ' || (iteration_number + 1) || ' "' || (iteration_number + 1) || '",'
10 )
11 /
SQL> select title "Title",
2 amount "Amount",
3 &select_list
4 amount * (&&user_input + 1) * &&user_input / 2 "Total"
5 from test
6 /
Title Amount 1 2 3 Total
---------- ---------- ---------- ---------- ---------- ----------
Switch 3000 3000 6000 9000 18000
Router 2000 2000 4000 6000 12000
Adapter 1000 1000 2000 3000 6000
SQL>
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 17:12:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
