Home » SQL & PL/SQL » SQL & PL/SQL » how to use decode function in for loop
how to use decode function in for loop [message #214418] Tue, 16 January 2007 06:43 Go to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
Hi,

How can we use a "decode" function in pl/sql for loop and also i should able to provide an alias name.

for example

decode(jan,jan_y,-7777777) jan_y;
decode(feb,feb_y,-7777777) feb_y;
.
.
.
decode(dec,dec_y,-7777777) dec_y;

instead of writing 12 decode statements can i write "for loop" to display all 12 months

Thanks and Regards,

Ananth
Re: how to use decode function in for loop [message #214421 is a reply to message #214418] Tue, 16 January 2007 07:03 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Not clear what are you going to achieve ?
Do you want to show 12 months as a result of SQL statement or
something like this ?

Rgds.
Re: how to use decode function in for loop [message #214771 is a reply to message #214421] Thu, 18 January 2007 00:42 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
Yes exactly i want to show 12 months as a result of SQL statment.

Thanks and Regards

Ananth
Re: how to use decode function in for loop [message #214783 is a reply to message #214771] Thu, 18 January 2007 01:18 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Does this look like you need ?

SQL> select
  2  to_char(add_months(trunc(sysdate,'YEAR'),rownum-1),'MON') month
  3  from dict where rownum <=12
  4  /

MONTH
------------
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC

12 rows selected.

Rgds.
Re: how to use decode function in for loop [message #214805 is a reply to message #214783] Thu, 18 January 2007 02:53 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
My requirement is

Decode(Month_name, 'jan',round(value,2)) jan,
Decode(Month_name, 'feb',round(value,2)) feb,
Decode(Month_name, 'mar',round(value,2)) mar,
.
.
.
.
.
Decode(Month_name, 'dec',round(value,2)) dec

Now instead of writing 12 decode statements like this can in incorporate this in "FOR LOOP" so that i can write only one decode statement.

Here jan,feb,mar,.........,dec will be acting as column names in my output.

Re: how to use decode function in for loop [message #214809 is a reply to message #214805] Thu, 18 January 2007 03:06 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
>>Now instead of writing 12 decode statements like this can in
>>incorporate this in "FOR LOOP" so that i can write only one
>>decode statement.

No, you can't in static SQL. Because you want
to use 12 columns you have to use 12 expressions:

SQL> with source as (select
  2  to_char(add_months(trunc(sysdate,'YEAR'),rownum-1),'MON') month, rownum mnum
  3  from dict where rownum <=12)
  4  select
  5  max(decode(month,'JAN',mnum)) "JAN",
  6  max(decode(month,'FEB',mnum)) "FEB",
  7  max(decode(month,'MAR',mnum)) "MAR",
  8  max(decode(month,'APR',mnum)) "APR"
  9  from source
 10  /
 
       JAN        FEB        MAR        APR
---------- ---------- ---------- ----------
         1          2          3          4

Rgds.
Re: how to use decode function in for loop [message #214814 is a reply to message #214809] Thu, 18 January 2007 03:19 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
yes we cant use in static sql, but here i am using dynamic sql.
here the month values are dynamic.
Re: how to use decode function in for loop [message #214819 is a reply to message #214814] Thu, 18 January 2007 03:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, you're using Static Sql.
Dynamic Sql is a completely different kettle of fish.
Re: how to use decode function in for loop [message #214821 is a reply to message #214819] Thu, 18 January 2007 03:57 Go to previous messageGo to next message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
This is my code

Function xxx(param1 in number,
param2 in number,
...)
return varchar2 as
s_string varchar2(32700);
begin
s_string:='select column names,'
||'Decode(Month_name, 'jan',round(value,2)) jan,'
||'Decode(Month_name, 'feb',round(value,2)) feb,'
||'Decode(Month_name, 'mar',round(value,2)) mar,'
||'.
.
.
.
.
||'Decode(Month_name, 'dec',round(value,2)) dec'
||'from Table_names'
||'where conditions'
Return s_string;
End xxx;

So, now how can i use FOR LOOP in this function.



Re: how to use decode function in for loop [message #214851 is a reply to message #214821] Thu, 18 January 2007 04:46 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
like this ?

SQL> select * from t;

MONTH_NAME        VALUE
------------ ----------
JAN                   1
FEB                   2
MAR                   3
APR                   4
MAY                   5
JUN                   6
JUL                   7
AUG                   8
SEP                   9
OCT                  10
NOV                  11
DEC                  12

12 rows selected.

SQL> var rc refcursor
SQL> declare
  2   sql_text varchar2(32767) := 'select ';
  3   month varchar2(3);
  4   base_date date := trunc(sysdate,'YEAR');
  5   sep varchar2(1) := '';
  6  begin
  7   for i in 1..12 loop
  8    month := to_char(base_date,'MON');
  9    base_date := add_months(base_date,1);
 10    sql_text := sql_text || sep || 'decode(month_name,''' ||
 11    month || ''',round(value,2)) ' || month;
 12    sep := ',';
 13   end loop;
 14   sql_text := sql_text || ' from t';
 15   open :rc for sql_text;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL> print rc

 JAN  FEB  MAR  APR  MAY  JUN  JUL  AUG  SEP  OCT  NOV  DEC
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
   1
        2
             3
                  4
                       5
                            6
                                 7
                                      8
                                           9
                                               10
                                                    11
                                                         12

12 rows selected.

Rgds.
Re: how to use decode function in for loop [message #214896 is a reply to message #214851] Thu, 18 January 2007 07:39 Go to previous message
ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD
Junior Member
Thanks a lot,

Your logic is working

Previous Topic: how to skip duplicate records in a procedure from processing
Next Topic: How to create Primary Key
Goto Forum:
  


Current Time: Fri Dec 09 17:18:03 CST 2016

Total time taken to generate the page: 0.20937 seconds