Home » SQL & PL/SQL » SQL & PL/SQL » Value multiple (Sql Plus, Oracle 10G, Windows)
Value multiple [message #686697] Fri, 25 November 2022 10:30 Go to next message
hissam78
Messages: 186
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;
We have an amount = 3000 and val = 3
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
somebody can help how to get this output in Sql.


regards,
Re: Value multiple [message #686698 is a reply to message #686697] Fri, 25 November 2022 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why "amount" and "1" are different?
How do you come to a total of 21000?

Re: Value multiple [message #686700 is a reply to message #686698] Fri, 25 November 2022 16:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3191
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
/
Now the execution:

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>
SY.
Re: Value multiple [message #686701 is a reply to message #686700] Fri, 25 November 2022 21:44 Go to previous messageGo to next message
hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN
Senior Member
Thanks a lot Michel Cadot and Solomon Yakobson for reply

really I am very sorry Experts, it was my mistake, total needed 18000 rather than 21000.

basic concept behind this query is like our mathematics multiplying table

User input 6 meaning 6 months than total amount of item is 3000
so
this item amount will multiply by first month 3000x1 = 3000, 3000x2 = 6000, 3000x3=9000 than
calculate the total 18000.

is there any other way to find the solution or need to follow the same solution that Solomon Yakonson giving.

Thanks again for quick response.


Re: Value multiple [message #686702 is a reply to message #686700] Fri, 25 November 2022 22:19 Go to previous messageGo to next message
hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN
Senior Member
also need to use in report this query

Thankful
Re: Value multiple [message #686703 is a reply to message #686702] Sat, 26 November 2022 05:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3191
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
/
Now:

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>
SY.
Re: Value multiple [message #686710 is a reply to message #686703] Mon, 28 November 2022 02:08 Go to previous messageGo to next message
hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN
Senior Member
Dear Solomon Yakobson,
very much grateful to you, I will try to execute PLSQL, because its first time for me. if any issue, will request for kind guidance, Thanks again Dear Expert.
Regards,
Re: Value multiple [message #686711 is a reply to message #686703] Mon, 28 November 2022 11:05 Go to previous messageGo to next message
hissam78
Messages: 186
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 #686713 is a reply to message #686711] Tue, 29 November 2022 02:09 Go to previous messageGo to next message
hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN
Senior Member
Dear Experts,
I stuck badly, not able to resolve, somebody can help please.


Thanks,

Regards,
Re: Value multiple [message #686714 is a reply to message #686711] Tue, 29 November 2022 02:37 Go to previous messageGo to next message
hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN
Senior Member
Dear Experts,
I have execute the attached Sql script with System user in Oracle 10G, but it is showing the following error,

ORA-06550: line 1, column 8:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

:= . ( @ % ;
  • Attachment: stragg.sql
    (Size: 0.30KB, Downloaded 65 times)
Re: Value multiple [message #686722 is a reply to message #686714] Fri, 02 December 2022 07:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3191
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: "
This will prompt for user input. Then execute:

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
/
SY.

[Updated on: Fri, 02 December 2022 07:22]

Report message to a moderator

Re: Value multiple [message #686728 is a reply to message #686722] Sat, 03 December 2022 00:44 Go to previous messageGo to next message
hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN
Senior Member
Thank you very much Solomon Yakobson for reply,
I have tried a lot but error showing, attached in png format.
please have a look into this error.

regards,
Re: Value multiple [message #686729 is a reply to message #686728] Sat, 03 December 2022 04:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3191
Registered: January 2010
Location: Connecticut, USA
Senior Member
Did you create STRAGG? There are plenty of examples on the web.

SY.
Re: Value multiple [message #686730 is a reply to message #686729] Sat, 03 December 2022 04:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3191
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: "
Then run:

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
/
For example:

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>
SY.
Re: Value multiple [message #686734 is a reply to message #686730] Wed, 07 December 2022 17:02 Go to previous message
hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN
Senior Member
Dear Solomon Yakobson,
Thanks a lot for your kind guidance, grateful to you for help,



regards,
Previous Topic: Performance issue with Update statement having select Statement
Next Topic: Update difference between two dates in months
Goto Forum:
  


Current Time: Sat Jan 28 20:20:18 CST 2023