x

What does this part of the SQL code do?

Don't use SQL in advance situations, What does this do:

join ACTPHY01 on RPLWNB = B1LWNB WHERE B1IAST ='A' and                        
((SELECT SUM(C.B1MDNB+C.B1MENB+C.B1M7NR+C.B1M8NR+C.B1M9NR) FROM                          
 ACTPHY01 C WHERE C.B1LKNB=RPI8NS and C.B1HXCD=RPMBXU) > 0 OR                            
EXISTS (SELECT 1 FROM CSHPHY01 A                    

???

SELECT RPI8NS as STM_A, RPMBXU as STM_B, RPLWNB as Service_Account,                      
RPMDXU as Customer, RPBUDF as Bill_Date, RPMCXU as Bill_Cycle,                           
RPMLXU as Act_Type, RPMEXU as Address_1, RPMFXU as Address_2,                            
RPPNXU as Address_3, RPPOXU as Address_4, RPPPXU as Address_5,                           
RPMGXU as City, RPMHXU as State, RPMIXU as Zip_Code, K8FENN as                           
Overdue_Amount, RPJANS as Ovr_Chrg, RPJBNS as Regulated_Amt, RPJCNS                      
As Un_Reg_Amt, RPMMXU as NPA, RPMNXU as Exe, RPMOXU as Number,                           
substr(RPBVDF +19000000, 5, 2) ||'-'|| substr(RPBVDF +19000000, 7,                       
2) ||'-'|| substr(RPBVDF +19000000, 1, 4) as Due_Date, RPBWDF as                         
DX_Date, max(HQTQDT) as PAY_Dte FROM LTRPHY01 left outer join                            
PSUPHY01 on RPLWNB = HQLWNB and HQFRSS ='P' and HQZ5ST ='P' left                         
outer join pldphy01 on RPBUDF=k8tidt and RPlwnb=k8lwnb and k8fmnn=1                      
left outer join ACTPHY01 on RPLWNB = B1LWNB WHERE B1IAST ='A' and                        
((SELECT SUM(C.B1MDNB+C.B1MENB+C.B1M7NR+C.B1M8NR+C.B1M9NR) FROM                          
 ACTPHY01 C WHERE C.B1LKNB=RPI8NS and C.B1HXCD=RPMBXU) > 0 OR                            
EXISTS (SELECT 1 FROM CSHPHY01 A WHERE RPI8NS= a.S3LKNB AND RPMBXU=                      
a.S3HXCD AND A.S3HJCD IN ('E','F') AND A.S3UXNP in (Select                               
max(b.S3UXNP) from CSHPHY01 B WHERE A.S3LWNB=B.S3LWNB)))                                 
GROUP BY RPI8NS, RPMBXU, RPLWNB, RPMDXU, RPBUDF, RPMCXU,                                 
RPMLXU, RPMEXU, RPMFXU, RPPNXU, RPPOXU, RPPPXU, RPMGXU, RPMHXU,                              
RPMIXU, K8FENN, RPJANS, RPJBNS, RPJCNS, RPMMXU, RPMNXU, RPMOXU,                              
RPBVDF, RPBWDF ORDER BY RPMIXU                    

more ▼

asked Oct 14, 2009 at 04:37 PM in Default

avatar image

Mike Korrigan
11 1 1 3

If there's any way you can repost that with better formatting it may be easier for us to read.

Oct 14, 2009 at 10:30 PM graz ♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It's just an insane join predicate. That is, you have a query joining in the table ACTPHY01 exclusively when that ridiculous predicate is satisfied.

I hope that the schema and queries you are working with were automatically generated because they are definitely not hand baked with love.

more ▼

answered Oct 14, 2009 at 11:05 PM

avatar image

Michael Haren
51 2 2

(comments are locked)
10|1200 characters needed characters left

Looks like this is for Oracle

more ▼

answered Oct 15, 2009 at 09:14 AM

avatar image

Squirrel 1
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x23

asked: Oct 14, 2009 at 04:37 PM

Seen: 958 times

Last Updated: Nov 11, 2009 at 06:13 AM

Copyright 2016 Redgate Software. Privacy Policy