Home » SQL & PL/SQL » SQL & PL/SQL » TO Loop 1 to 12 (Oracle 9.2)
TO Loop 1 to 12 [message #410103] Thu, 25 June 2009 04:39 Go to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Hi

This I my scritp. I run it as follows to get from 1 to 12. Changing the BOLD nr from 1 to 12 with union between them. I tried looping but I don't get it. Can some one please help me to loop this from 1 to 12 changing the nr there from 01 to 02 ...12 and the T$TOVR$1 to T$TOVR$2 ... T$TOVR$12. Thanks in advance

Select substr(A.T$SSTR,24,4) REP
,substr(A.T$SSTR,34,6) CUSTNR
,C.T$DSCA
,substr(A.T$SSTR,43,3) WAREHOUSE
,To_Date((A.T$YRNO || '-' || '01' || '-' || '01'),'YYYY-MM-DD') As DateVal, Sum(A.T$TOVR$1) as AMMOUNT
from BAANDB.TTDSST020300 A

JOIN BAANDB.TTccom010300 B ON
substr(A.T$SSTR,34,6) = B.T$CUNO
JOIN BAANDB.TTCMCS019300 C ON
B.T$CTIT=C.T$CTIT

where A.T$CSNO = 1344
GROUP BY substr(A.T$SSTR,24,4)
,C.T$DSCA
,substr(A.T$SSTR,34,6)
,substr(A.T$SSTR,43,3)
,A.T$YRNO

UNION


Select substr(A.T$SSTR,24,4) REP
,substr(A.T$SSTR,34,6) CUSTNR
,C.T$DSCA
,substr(A.T$SSTR,43,3) WAREHOUSE
,To_Date((A.T$YRNO || '-' || '02' || '-' || '01'),'YYYY-MM-DD') As DateVal, Sum(A.T$TOVR$2) as AMMOUNT
from BAANDB.TTDSST020300 A

JOIN BAANDB.TTccom010300 B ON
substr(A.T$SSTR,34,6) = B.T$CUNO
JOIN BAANDB.TTCMCS019300 C ON
B.T$CTIT=C.T$CTIT

where A.T$CSNO = 1344
GROUP BY substr(A.T$SSTR,24,4)
,C.T$DSCA
,substr(A.T$SSTR,34,6)
,substr(A.T$SSTR,43,3)
,A.T$YRNO
Re: TO Loop 1 to 12 [message #410104 is a reply to message #410103] Thu, 25 June 2009 04:41 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Please format your code and put it between [code]your code here[/code] tags.
Thanks
Re: TO Loop 1 to 12 [message #410107 is a reply to message #410103] Thu, 25 June 2009 04:54 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Hi

This I my scritp. I run it as follows to get from 1 to 12. Changing the BOLD nr from 1 to 12 with union between them. I tried looping but I don't get it. Can some one please help me to loop this from 1 to 12 changing the nr there from 01 to 02 ...12 and the T$TOVR$1 to T$TOVR$2 ... T$TOVR$12. Thanks in advance
Select substr(A.T$SSTR,24,4) REP
,substr(A.T$SSTR,34,6) CUSTNR
,C.T$DSCA
,substr(A.T$SSTR,43,3) WAREHOUSE
,To_Date((A.T$YRNO || '-' || '01' || '-' || '01'),'YYYY-MM-DD') As DateVal, Sum(A.T$TOVR$1) as AMMOUNT
from BAANDB.TTDSST020300 A

JOIN BAANDB.TTccom010300 B ON
substr(A.T$SSTR,34,6) = B.T$CUNO 
JOIN BAANDB.TTCMCS019300 C ON
B.T$CTIT=C.T$CTIT

where A.T$CSNO = 1344 
GROUP BY substr(A.T$SSTR,24,4)
,C.T$DSCA
,substr(A.T$SSTR,34,6)
,substr(A.T$SSTR,43,3)
,A.T$YRNO

UNION


Select substr(A.T$SSTR,24,4) REP
,substr(A.T$SSTR,34,6) CUSTNR
,C.T$DSCA
,substr(A.T$SSTR,43,3) WAREHOUSE
,To_Date((A.T$YRNO || '-' || '02' || '-' || '01'),'YYYY-MM-DD') As DateVal, Sum(A.T$TOVR$2) as AMMOUNT
from BAANDB.TTDSST020300 A

JOIN BAANDB.TTccom010300 B ON
substr(A.T$SSTR,34,6) = B.T$CUNO 
JOIN BAANDB.TTCMCS019300 C ON
B.T$CTIT=C.T$CTIT

where A.T$CSNO = 1344 
GROUP BY substr(A.T$SSTR,24,4)
,C.T$DSCA
,substr(A.T$SSTR,34,6)
,substr(A.T$SSTR,43,3)
,A.T$YRNO

Re: TO Loop 1 to 12 [message #410124 is a reply to message #410107] Thu, 25 June 2009 06:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Something along these lines should do it:
Select substr(A.T$SSTR,24,4) REP
      ,substr(A.T$SSTR,34,6) CUSTNR
      ,C.T$DSCA
      ,substr(A.T$SSTR,43,3) WAREHOUSE
      ,To_Date((A.T$YRNO || '-' || '01' || '-' || '01'),'YYYY-MM-DD') As DateVal
      ,Sum(case when l.field_no = 1 then A.T$TOVR$1
                when l.field_no = 2 then A.T$TOVR$2
                when l.field_no = 3 then A.T$TOVR$3
                ...
                when l.field_no = 12 then A.T$TOVR$12
                end) as AMMOUNT
from   BAANDB.TTDSST020300 A  JOIN BAANDB.TTccom010300 B ON substr(A.T$SSTR,34,6) = B.T$CUNO 
                              JOIN BAANDB.TTCMCS019300 C ON B.T$CTIT=C.T$CTIT
                              JOIN (select level field_no from dual connect by level <=12) l
where  A.T$CSNO = 1344 
GROUP BY substr(A.T$SSTR,24,4)
        ,C.T$DSCA
        ,substr(A.T$SSTR,34,6)
        ,substr(A.T$SSTR,43,3)
        ,A.T$YRNO
        ,l.field_no
Re: TO Loop 1 to 12 [message #410322 is a reply to message #410103] Fri, 26 June 2009 04:06 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
I tries this like you said. But now it gives me MISSING KEYWORD

Select substr(A.T$SSTR,24,4) REP
      ,substr(A.T$SSTR,34,6) CUSTNR
      ,C.T$DSCA
      ,substr(A.T$SSTR,43,3) WAREHOUSE
      ,To_Date((A.T$YRNO || '-' || '01' || '-' || '01'),'YYYY-MM-DD') As DateVal
      ,Sum(case when l.field_no = 1 then A.T$TOVR$1
                when l.field_no = 2 then A.T$TOVR$2
                when l.field_no = 3 then A.T$TOVR$3
                when l.field_no = 4 then A.T$TOVR$4
				when l.field_no = 5 then A.T$TOVR$5
				when l.field_no = 6 then A.T$TOVR$6
				when l.field_no = 7 then A.T$TOVR$7
				when l.field_no = 8 then A.T$TOVR$8
				when l.field_no = 9 then A.T$TOVR$9
				when l.field_no = 10 then A.T$TOVR$10
				when l.field_no = 11 then A.T$TOVR$11
                when l.field_no = 12 then A.T$TOVR$12
                end) as AMMOUNT
from   BAANDB.TTDSST020300 A  JOIN BAANDB.TTccom010300 B ON substr(A.T$SSTR,34,6) = B.T$CUNO 
                              JOIN BAANDB.TTCMCS019300 C ON B.T$CTIT=C.T$CTIT
                              JOIN (select level field_no from dual connect by level <=12) l
where  A.T$CSNO = 1344 
GROUP BY substr(A.T$SSTR,24,4)
        ,C.T$DSCA
        ,substr(A.T$SSTR,34,6)
        ,substr(A.T$SSTR,43,3)
        ,A.T$YRNO
        ,l.field_no
Re: TO Loop 1 to 12 [message #410323 is a reply to message #410103] Fri, 26 June 2009 04:09 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
If I understand correct its missing a on after

   JOIN (select level field_no from dual connect by level <=12) l

Re: TO Loop 1 to 12 [message #410328 is a reply to message #410323] Fri, 26 June 2009 05:28 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Try replace "join" keyword with the "cross join" only in the last step and see if that helps.

From next time if you could copy and paste the actual error it will be useful and avoid any ambiguity, if so any.

Regards

Raj
Re: TO Loop 1 to 12 [message #410329 is a reply to message #410103] Fri, 26 June 2009 05:38 Go to previous message
Deon Smit
Messages: 36
Registered: September 2008
Member
Thank you it Worked. I also changed the month value in a similar fasion.

Select substr(A.T$SSTR,24,4) REP
      ,substr(A.T$SSTR,34,6) CUSTNR
      ,C.T$DSCA
      ,substr(A.T$SSTR,43,3) WAREHOUSE
      ,To_Date((A.T$YRNO || '-' || (case when l.field_no =1 then '01'
	  					 		   		 when l.field_no =2 then '02'
	  					 		   		 when l.field_no =3 then '03'
										 when l.field_no =4 then '04'
										 when l.field_no =5 then '05'
										 when l.field_no =6 then '06'
										 when l.field_no =7 then '07'
										 when l.field_no =8 then '08'
										 when l.field_no =9 then '09'
										 when l.field_no =10 then '10'
										 when l.field_no =11 then '11'
										 when l.field_no =12 then '12'
	  
	  
	  
	  					 		   end) || '-' || '01'),'YYYY-MM-DD') As DateVal
      
	  
	  ,Sum(case when l.field_no = 1 then A.T$TOVR$1
                when l.field_no = 2 then A.T$TOVR$2
                when l.field_no = 3 then A.T$TOVR$3
                when l.field_no = 4 then A.T$TOVR$4
				when l.field_no = 5 then A.T$TOVR$5
				when l.field_no = 6 then A.T$TOVR$6
				when l.field_no = 7 then A.T$TOVR$7
				when l.field_no = 8 then A.T$TOVR$8
				when l.field_no = 9 then A.T$TOVR$9
				when l.field_no = 10 then A.T$TOVR$10
				when l.field_no = 11 then A.T$TOVR$11
                when l.field_no = 12 then A.T$TOVR$12
                end) as AMMOUNT
from   BAANDB.TTDSST020300 A  JOIN BAANDB.TTccom010300 B ON substr(A.T$SSTR,34,6) = B.T$CUNO 
                              JOIN BAANDB.TTCMCS019300 C ON B.T$CTIT=C.T$CTIT
                              cross JOIN (select level field_no from dual connect by level <=12) l
where  A.T$CSNO = 1344 
GROUP BY substr(A.T$SSTR,24,4)
        ,C.T$DSCA
        ,substr(A.T$SSTR,34,6)
        ,substr(A.T$SSTR,43,3)
        ,A.T$YRNO
        ,l.field_no
Previous Topic: migration script
Next Topic: using 'with' analytical function
Goto Forum:
  


Current Time: Fri Dec 09 09:31:29 CST 2016

Total time taken to generate the page: 0.10920 seconds