Home » SQL & PL/SQL » SQL & PL/SQL » Using alias
Using alias [message #200315] Mon, 30 October 2006 06:00 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I am writting a query to export sql file to csv.
So I am using ||','|| to export to csv.

The query I am working on today is too long and there are many CASE expressions which are going to be repeate in one or other way.
I want to use alias for a column but I am not able to do that which is working if I do not use ||','|| in place of comma.
My query is somewhat like the below :---

SELECT i.item||','||i.descr||','||TO_NUMBER(i.avg_hist)||','||sku.oh||','||
       (CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
              (w.week1 + w.week2 + w.week3 + w.week4)
            WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
              (w.week1 + w.week2 + w.week3)
            WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
              (w.week1 + w.week2)
       ELSE w.week1
        END) ||','|| i.loc
FROM.......
.....

I want to use alias for CASE statements like below :
||','||sku.oh aa||','||
       (CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
(w.week1 + w.week2 + w.week3 + w.week4)
 WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
 (w.week1 + w.week2 + w.week3)
 WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
 (w.week1 + w.week2)
 ELSE w.week1
   END) bb ||','|| i.loc cc
I want to use the aliases as 'aa,bb,cc'.
But this gives the error as "FROM clause not found."

Please suggest me a way to overcome this problem as I have to use ||','|| to export it to CSV file.

Thanks,
Mona

[Updated on: Mon, 30 October 2006 06:02]

Report message to a moderator

Re: Using alias [message #200346 is a reply to message #200315] Mon, 30 October 2006 07:35 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Use an inline view e.g.


select
aa || bb from
(SELECT (CASE ...  END) as aa,
        (CASE ... END) as bb,
         ...
from ...)
Re: Using alias [message #200498 is a reply to message #200346] Tue, 31 October 2006 02:52 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I used the inline view. But as you can see in the below code that I have to display column values like NetMonth1, NetMonth2, NetMonth3 and NetMonth4. There are more columns which I have not given here for simplicity.
I have used the alias to SUM the value of NetMonth1, NetMonth2, NetMonth3 and NetMonth4. But this is giving error here. It seems that I have to go more level up. Please advice as this is giving the error as "ORA-00904: "NETMONTH3": invalid identifier". The error is coming as I am trying to use the alias NetMonth1, NetMonth2, NetMonth3 in the formulas like (NetMonth2 + POmonth3 - DMDmonth3) in the same select statement.
Please suggest me a way to overcome this problem.
SELECT item,descr,iavg_hist,oh,POmonth1,POmonth2,POmonth3,POmonth4,
       DMDmonth1,DMDmonth2,DMDmonth3,DMDmonth4,
       (oh + POmonth1 - DMDmonth1) NetMonth1,
       (NetMonth1 + POmonth2 - DMDmonth2) NetMonth2,
       (NetMonth2 + POmonth3 - DMDmonth3) NetMonth3,
       (NetMonth3 + POmonth4 - DMDmonth4) NetMonth4
  FROM (SELECT i.item,i.descr,TO_NUMBER(i.avg_hist) iavg_hist,sku.oh,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week1 + w.week2 + w.week3 + w.week4)
                    WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week1 + w.week2 + w.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week1 + w.week2)
               ELSE w.week1
                END POmonth1,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
	       	       (w.week5 + w.week6 + w.week7 + w.week8)
	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
	               (w.week4 + w.week5 + w.week6 + w.week7)
	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
	               (w.week3 + w.week4 + w.week5 + w.week6)
	       ELSE 
	               (w.week2 + w.week3 + w.week4 + w.week5)
               END POmonth2,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
       	               (w.week9 + w.week10 + w.week11 + w.week12)
       	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
       	               (w.week8 + w.week9 + w.week10 + w.week11)
       	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
       	               (w.week7 + w.week8 + w.week9 + w.week10)
       	       ELSE 
       	               (w.week6 + w.week7 + w.week8 + w.week9)
               END POmonth3,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week13 + w.week14 + w.week15 + w.week16)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week12 + w.week13 + w.week14 + w.week15)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week11 + w.week12 + w.week13 + w.week14)
               ELSE 
                       (w.week10 + w.week11 + w.week12 + w.week13)
               END POmonth4,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week1 + w1.week2 + w1.week3 + w1.week4)
                   WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week1 + w1.week2 + w1.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week1 + w1.week2)
               ELSE 
                       (w1.week1)
               END DMDmonth1,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week5 + w1.week6 + w1.week7 + w1.week8)
                    WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week4 + w1.week5 + w1.week6 + w1.week7)
                    WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week3 + w1.week4 + w1.week5 + w1.week6)
               ELSE 
                       (w1.week2 + w1.week3 + w1.week4 + w1.week5)
               END DMDmonth2,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week9 + w1.week10 + w1.week11 + w1.week12)
                     WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week8 + w1.week9 + w1.week10 + w1.week11)
                     WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week7 + w1.week8 + w1.week9 + w1.week10)
               ELSE 
                       (w1.week6 + w1.week7 + w1.week8 + w1.week9)
               END DMDmonth3,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week13 + w1.week14 + w1.week15 + w1.week16)
                      WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week12 + w1.week13 + w1.week14 + w1.week15)
                      WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week11 + w1.week12 + w1.week13 + w1.week14)
               ELSE 
                       (w1.week10 + w1.week11 + w1.week12 + w1.week13)
               END DMDmonth4
          FROM stsc.item i, stsc.sku sku, 
               springs.si_skuproj_w w,
               springs.si_skuproj_w w1
         WHERE (i.item=sku.item)
           AND ((sku.item=w.item(+))
           AND (sku.loc=w.loc(+)))
           AND ((w.item=w1.item)
           AND (w.loc=w1.loc))
           AND w1.columnheader='CBMPDepDmd' 
           AND w.columnheader='SchedRcpts' 
           AND (w.loc='175' OR w.loc='FIN') 
           AND (w1.loc='175' OR w1.loc='FIN')
           AND i.admin_group >' '
           AND (i.inv_class='L020' OR i.inv_class='L021')  
         ORDER BY i.admin_group, w.item, w.loc)


Regards,
Mona

[Updated on: Tue, 31 October 2006 02:56]

Report message to a moderator

Re: Using alias [message #200500 is a reply to message #200315] Tue, 31 October 2006 03:01 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
NetMonth3 is not assigned anywhere in the inline view. That is why you are getting the error.
Re: Using alias [message #200502 is a reply to message #200500] Tue, 31 October 2006 03:08 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I don't know your name.
As I told you that NetMOnth1...4 is not used in the inline view,
and I can't even use it as this is also using the other aliases from the inline view. So I can't put both together.
Please look into my code and suggest me a way to make this work out.
(oh + POmonth1 - DMDmonth1) NetMonth1,
       (NetMonth1 + POmonth2 - DMDmonth2) NetMonth2,
       (NetMonth2 + POmonth3 - DMDmonth3) NetMonth3,
       (NetMonth3 + POmonth4 - DMDmonth4) NetMonth4


Thanks,
MOna
Re: Using alias [message #200503 is a reply to message #200315] Tue, 31 October 2006 03:11 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
If netMonth3 is actually a column in the table, then you just need to select it in the inline view along with the other stuff e.g.

SELECT item,descr,iavg_hist,oh,POmonth1,POmonth2,POmonth3,POmonth4,
       DMDmonth1,DMDmonth2,DMDmonth3,DMDmonth4,
       (oh + POmonth1 - DMDmonth1) NetMonth1,
       (NetMonth1 + POmonth2 - DMDmonth2) NetMonth2,
       (NetMonth2 + POmonth3 - DMDmonth3) NetMonth3,
       (NetMonth3 + POmonth4 - DMDmonth4) NetMonth4
  FROM (SELECT NetMonth3, 
               i.item,i.descr,TO_NUMBER(i.avg_hist) iavg_hist,sku.oh,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week1 + w.week2 + w.week3 + w.week4)
                    WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week1 + w.week2 + w.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week1 + w.week2)

[Updated on: Tue, 31 October 2006 03:12]

Report message to a moderator

Re: Using alias [message #200504 is a reply to message #200503] Tue, 31 October 2006 03:13 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

NetMonth1..4 is only alias and not a column.
Please look into my code once.

Thanks,
Mona
Re: Using alias [message #200505 is a reply to message #200315] Tue, 31 October 2006 03:18 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I have looked into your code and it doesn't make any sense. This is the inline view:

SELECT i.item,i.descr,TO_NUMBER(i.avg_hist) iavg_hist,sku.oh,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week1 + w.week2 + w.week3 + w.week4)
                    WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week1 + w.week2 + w.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week1 + w.week2)
               ELSE w.week1
                END POmonth1,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
	       	       (w.week5 + w.week6 + w.week7 + w.week8)
	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
	               (w.week4 + w.week5 + w.week6 + w.week7)
	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
	               (w.week3 + w.week4 + w.week5 + w.week6)
	       ELSE 
	               (w.week2 + w.week3 + w.week4 + w.week5)
               END POmonth2,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
       	               (w.week9 + w.week10 + w.week11 + w.week12)
       	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
       	               (w.week8 + w.week9 + w.week10 + w.week11)
       	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
       	               (w.week7 + w.week8 + w.week9 + w.week10)
       	       ELSE 
       	               (w.week6 + w.week7 + w.week8 + w.week9)
               END POmonth3,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week13 + w.week14 + w.week15 + w.week16)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week12 + w.week13 + w.week14 + w.week15)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week11 + w.week12 + w.week13 + w.week14)
               ELSE 
                       (w.week10 + w.week11 + w.week12 + w.week13)
               END POmonth4,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week1 + w1.week2 + w1.week3 + w1.week4)
                   WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week1 + w1.week2 + w1.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week1 + w1.week2)
               ELSE 
                       (w1.week1)
               END DMDmonth1,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week5 + w1.week6 + w1.week7 + w1.week8)
                    WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week4 + w1.week5 + w1.week6 + w1.week7)
                    WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week3 + w1.week4 + w1.week5 + w1.week6)
               ELSE 
                       (w1.week2 + w1.week3 + w1.week4 + w1.week5)
               END DMDmonth2,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week9 + w1.week10 + w1.week11 + w1.week12)
                     WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week8 + w1.week9 + w1.week10 + w1.week11)
                     WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week7 + w1.week8 + w1.week9 + w1.week10)
               ELSE 
                       (w1.week6 + w1.week7 + w1.week8 + w1.week9)
               END DMDmonth3,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week13 + w1.week14 + w1.week15 + w1.week16)
                      WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week12 + w1.week13 + w1.week14 + w1.week15)
                      WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week11 + w1.week12 + w1.week13 + w1.week14)
               ELSE 
                       (w1.week10 + w1.week11 + w1.week12 + w1.week13)
               END DMDmonth4
          FROM stsc.item i, stsc.sku sku, 
               springs.si_skuproj_w w,
               springs.si_skuproj_w w1
         WHERE (i.item=sku.item)
           AND ((sku.item=w.item(+))
           AND (sku.loc=w.loc(+)))
           AND ((w.item=w1.item)
           AND (w.loc=w1.loc))
           AND w1.columnheader='CBMPDepDmd' 
           AND w.columnheader='SchedRcpts' 
           AND (w.loc='175' OR w.loc='FIN') 
           AND (w1.loc='175' OR w1.loc='FIN')
           AND i.admin_group >' '
           AND (i.inv_class='L020' OR i.inv_class='L021')  
         ORDER BY i.admin_group, w.item, w.loc 


Search that text for NetMonth3 and tell me where it appears.
Re: Using alias [message #200506 is a reply to message #200504] Tue, 31 October 2006 03:19 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Mona,
I didn't understand the link between having alias and generating a csv file.
Can you please throw some light on that?

By
Vamsi.
Re: Using alias [message #200508 is a reply to message #200505] Tue, 31 October 2006 03:26 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I am pasting the code again so that you can see the aliases.
SELECT item,descr,iavg_hist,oh,POmonth1,POmonth2,POmonth3,POmonth4,
       DMDmonth1,DMDmonth2,DMDmonth3,DMDmonth4,
       (oh + POmonth1 - DMDmonth1) NetMonth1,
       (NetMonth1 + POmonth2 - DMDmonth2) NetMonth2,
       (NetMonth2 + POmonth3 - DMDmonth3) NetMonth3,
       (NetMonth3 + POmonth4 - DMDmonth4) NetMonth4
  FROM (SELECT i.item,i.descr,TO_NUMBER(i.avg_hist) iavg_hist,sku.oh,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week1 + w.week2 + w.week3 + w.week4)
                    WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week1 + w.week2 + w.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week1 + w.week2)
               ELSE w.week1
                END POmonth1,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
	       	       (w.week5 + w.week6 + w.week7 + w.week8)
	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
	               (w.week4 + w.week5 + w.week6 + w.week7)
	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
	               (w.week3 + w.week4 + w.week5 + w.week6)
	       ELSE 
	               (w.week2 + w.week3 + w.week4 + w.week5)
               END POmonth2,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
       	               (w.week9 + w.week10 + w.week11 + w.week12)
       	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
       	               (w.week8 + w.week9 + w.week10 + w.week11)
       	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
       	               (w.week7 + w.week8 + w.week9 + w.week10)
       	       ELSE 
       	               (w.week6 + w.week7 + w.week8 + w.week9)
               END POmonth3,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week13 + w.week14 + w.week15 + w.week16)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week12 + w.week13 + w.week14 + w.week15)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week11 + w.week12 + w.week13 + w.week14)
               ELSE 
                       (w.week10 + w.week11 + w.week12 + w.week13)
               END POmonth4,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week1 + w1.week2 + w1.week3 + w1.week4)
                   WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week1 + w1.week2 + w1.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week1 + w1.week2)
               ELSE 
                       (w1.week1)
               END DMDmonth1,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week5 + w1.week6 + w1.week7 + w1.week8)
                    WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week4 + w1.week5 + w1.week6 + w1.week7)
                    WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week3 + w1.week4 + w1.week5 + w1.week6)
               ELSE 
                       (w1.week2 + w1.week3 + w1.week4 + w1.week5)
               END DMDmonth2,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week9 + w1.week10 + w1.week11 + w1.week12)
                     WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week8 + w1.week9 + w1.week10 + w1.week11)
                     WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week7 + w1.week8 + w1.week9 + w1.week10)
               ELSE 
                       (w1.week6 + w1.week7 + w1.week8 + w1.week9)
               END DMDmonth3,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week13 + w1.week14 + w1.week15 + w1.week16)
                      WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week12 + w1.week13 + w1.week14 + w1.week15)
                      WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week11 + w1.week12 + w1.week13 + w1.week14)
               ELSE 
                       (w1.week10 + w1.week11 + w1.week12 + w1.week13)
               END DMDmonth4
          FROM stsc.item i, stsc.sku sku, 
               springs.si_skuproj_w w,
               springs.si_skuproj_w w1
         WHERE (i.item=sku.item)
           AND ((sku.item=w.item(+))
           AND (sku.loc=w.loc(+)))
           AND ((w.item=w1.item)
           AND (w.loc=w1.loc))
           AND w1.columnheader='CBMPDepDmd' 
           AND w.columnheader='SchedRcpts' 
           AND (w.loc='175' OR w.loc='FIN') 
           AND (w1.loc='175' OR w1.loc='FIN')
           AND i.admin_group >' '
           AND (i.inv_class='L020' OR i.inv_class='L021')  
         ORDER BY i.admin_group, w.item, w.loc)


Thanks,
Mona

Re: Using alias [message #200510 is a reply to message #200506] Tue, 31 October 2006 03:29 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Vamsi,
I need to use aliases as my select statement is too big. There are many formulas which needs to be displayed. If I don't take help of aliases here then my code would become 4 times bigger than what you see presently.

Thanks,
Mona
Re: Using alias [message #200511 is a reply to message #200315] Tue, 31 October 2006 03:31 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Can you display out just the lines where the NetMonth3 alias is assigned e.g. for Pomonth4 it's:

CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
(w.week13 + w.week14 + w.week15 + w.week16)
WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
(w.week12 + w.week13 + w.week14 + w.week15)
WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
(w.week11 + w.week12 + w.week13 + w.week14)
ELSE
(w.week10 + w.week11 + w.week12 + w.week13)
END POmonth4,

Show me the one for NetMonth3.

[Updated on: Tue, 31 October 2006 03:33]

Report message to a moderator

Re: Using alias [message #200512 is a reply to message #200511] Tue, 31 October 2006 03:33 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

SELECT item,descr,iavg_hist,oh,POmonth1,POmonth2,POmonth3,POmonth4,
       DMDmonth1,DMDmonth2,DMDmonth3,DMDmonth4,
       (oh + POmonth1 - DMDmonth1) NetMonth1,
       (NetMonth1 + POmonth2 - DMDmonth2) NetMonth2,
       (NetMonth2 + POmonth3 - DMDmonth3) NetMonth3,
       (NetMonth3 + POmonth4 - DMDmonth4) NetMonth4
  FROM (SELECT i.item,i.descr,TO_NUMBER(i.avg_hist) iavg_hist,sku.oh,


You can see that the aliases are being assigned in the SUM.
(NetMonth1 + POmonth2 - DMDmonth2) NetMonth2,
(NetMonth2 + POmonth3 - DMDmonth3) NetMonth3,
(NetMonth3 + POmonth4 - DMDmonth4) NetMonth4

Mona
Re: Using alias [message #200513 is a reply to message #200315] Tue, 31 October 2006 03:37 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
No, you can't do that. You will have to split it into two nested inline views. Something like:

select ....
       NetMonth3 + POmonth4 - DMDmonth4) NetMonth4
from
(select
   ...
   pomonth4, 
   NetMonth2 + POmonth3 - DMDmonth3) NetMonth3,
   dmdmonth4,
   ...
from
(SELECT i.item,i.descr,TO_NUMBER(i.avg_hist) iavg_hist,sku.oh,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week1 + w.week2 + w.week3 + w.week4)
                    WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week1 + w.week2 + w.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week1 + w.week2)
               ELSE w.week1
                END POmonth1,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
	       	       (w.week5 + w.week6 + w.week7 + w.week8)
	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
	               (w.week4 + w.week5 + w.week6 + w.week7)
	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
	               (w.week3 + w.week4 + w.week5 + w.week6)
	       ELSE 
	               (w.week2 + w.week3 + w.week4 + w.week5)
               END POmonth2,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
       	               (w.week9 + w.week10 + w.week11 + w.week12)
       	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
       	               (w.week8 + w.week9 + w.week10 + w.week11)
       	           WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
       	               (w.week7 + w.week8 + w.week9 + w.week10)
       	       ELSE 
       	               (w.week6 + w.week7 + w.week8 + w.week9)
               END POmonth3,
               CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w.week13 + w.week14 + w.week15 + w.week16)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w.week12 + w.week13 + w.week14 + w.week15)
                   WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w.week11 + w.week12 + w.week13 + w.week14)
               ELSE 
                       (w.week10 + w.week11 + w.week12 + w.week13)
               END POmonth4,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week1 + w1.week2 + w1.week3 + w1.week4)
                   WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week1 + w1.week2 + w1.week3)
                   WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week1 + w1.week2)
               ELSE 
                       (w1.week1)
               END DMDmonth1,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week5 + w1.week6 + w1.week7 + w1.week8)
                    WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week4 + w1.week5 + w1.week6 + w1.week7)
                    WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week3 + w1.week4 + w1.week5 + w1.week6)
               ELSE 
                       (w1.week2 + w1.week3 + w1.week4 + w1.week5)
               END DMDmonth2,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week9 + w1.week10 + w1.week11 + w1.week12)
                     WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week8 + w1.week9 + w1.week10 + w1.week11)
                     WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week7 + w1.week8 + w1.week9 + w1.week10)
               ELSE 
                       (w1.week6 + w1.week7 + w1.week8 + w1.week9)
               END DMDmonth3,
               CASE WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 1 and 7 ) THEN
                       (w1.week13 + w1.week14 + w1.week15 + w1.week16)
                      WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 8 and 14) THEN
                       (w1.week12 + w1.week13 + w1.week14 + w1.week15)
                      WHEN (TO_NUMBER(TO_CHAR(w1.startdate,'dd')) BETWEEN 15 and 21) THEN
                       (w1.week11 + w1.week12 + w1.week13 + w1.week14)
               ELSE 
                       (w1.week10 + w1.week11 + w1.week12 + w1.week13)
               END DMDmonth4
          FROM stsc.item i, stsc.sku sku, 
               springs.si_skuproj_w w,
               springs.si_skuproj_w w1
         WHERE (i.item=sku.item)
           AND ((sku.item=w.item(+))
           AND (sku.loc=w.loc(+)))
           AND ((w.item=w1.item)
           AND (w.loc=w1.loc))
           AND w1.columnheader='CBMPDepDmd' 
           AND w.columnheader='SchedRcpts' 
           AND (w.loc='175' OR w.loc='FIN') 
           AND (w1.loc='175' OR w1.loc='FIN')
           AND i.admin_group >' '
           AND (i.inv_class='L020' OR i.inv_class='L021')  
         ORDER BY i.admin_group, w.item, w.loc))
Re: Using alias [message #200514 is a reply to message #200513] Tue, 31 October 2006 03:41 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I had asked this only that I have to use a upper level of inline view to solve this. But the problem is I am not getting the idea to solve this.
In the example given by you, it is not clear as in the below code given by you, in the second inline view you are using "NetMonth2 + POmonth3 - DMDmonth3) NetMonth3". This is the point where I am facing the problem. Please suggest me a way to overcome this problem as I can't use this code which is using NetMonth2 to give NetMonth3 as NetMonth2 is also aliased in the same inline view.



select ....
       NetMonth3 + POmonth4 - DMDmonth4) NetMonth4
from
(select
   ...
   pomonth4, 
   NetMonth2 + POmonth3 - DMDmonth3) NetMonth3,
   dmdmonth4,
   ...
from


Thanks,
Mona
Re: Using alias [message #200516 is a reply to message #200315] Tue, 31 October 2006 03:56 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The problem is not solvable the way you are trying to solve it. If you are determined not to use nested inline views then the only solution would be to do something like:

SELECT item,descr,iavg_hist,oh,POmonth1,POmonth2,POmonth3,POmonth4,
       DMDmonth1,DMDmonth2,DMDmonth3,DMDmonth4,
       (oh + POmonth1 - DMDmonth1) NetMonth1,
       (oh + POmonth1 - DMDmonth1 + POmonth2 - DMDmonth2) NetMonth2,
       (oh + POmonth1 - DMDmonth1 + POmonth2 - DMDmonth2 + POmonth3 - DMDmonth3) NetMonth3,
       (oh + POmonth1 - DMDmonth1 + POmonth2 - DMDmonth2 + POmonth3 - DMDmonth3 + POmonth4 - DMDmonth4) NetMonth4,
...


I'm sorry if it's not what you want to hear, but I can't work miracles and invent a new version of SQL that will allow you to have recursive forward referencing in the select list.
Re: Using alias [message #200517 is a reply to message #200516] Tue, 31 October 2006 04:00 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Neal,
I know this way but to make the query smaller, I was asking for some suggestion.

Thanks for your help
Mona
Re: Using alias [message #200561 is a reply to message #200517] Tue, 31 October 2006 06:28 Go to previous messageGo to next message
nico.onea
Messages: 2
Registered: October 2006
Junior Member
Mona,

the following select should work :
SELECT item,descr,iavg_hist,oh,POmonth1,POmonth2,POmonth3,POmonth4,
DMDmonth1,DMDmonth2,DMDmonth3,DMDmonth4,
(oh + POmonth1 - DMDmonth1) NetMonth1,
(oh + POmonth1 - DMDmonth1 + POmonth2 - DMDmonth2) NetMonth2,
(oh + POmonth1 - DMDmonth1 + POmonth2 - DMDmonth2 + POmonth3 - DMDmonth3) NetMonth3,
(oh + POmonth1 - DMDmonth1 + POmonth2 - DMDmonth2 + POmonth3 - DMDmonth3 + POmonth4 - DMDmonth4) NetMonth4
FROM (SELECT i.item,i.descr,TO_NUMBER(i.avg_hist) iavg_hist,sku.oh,
CASE WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 1 and 7 ) THEN
(w.week1 + w.week2 + w.week3 + w.week4)
WHEN (TO_NUMBER(TO_CHAR(w.startdate,'dd')) BETWEEN 8 and 14) THEN

.................................
Regards,
Nicoleta
Re: Using alias [message #200563 is a reply to message #200315] Tue, 31 October 2006 06:35 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
An excellent suggestion, Nicoleta. It's just a shame it appears to be identical to what I already suggested.
Re: Using alias [message #200602 is a reply to message #200563] Tue, 31 October 2006 08:11 Go to previous messageGo to next message
nico.onea
Messages: 2
Registered: October 2006
Junior Member

Neal,

unfortunately I didn't check for new posts before posting the solution. So, no shame on me.

Nicoleta
Re: Using alias [message #201055 is a reply to message #200602] Thu, 02 November 2006 06:53 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ok, no shame then. It would have been nice if one would actually read a thread before posting a reply.

http://www.orafaq.com/forum/fa/448/0/

This one's closed.

MHE
Previous Topic: Renaming Tablespace name
Next Topic: trying to call but got error
Goto Forum:
  


Current Time: Tue Dec 06 16:27:55 CST 2016

Total time taken to generate the page: 0.09312 seconds