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 '09 at 04:37 PM in Default

Mike Korrigan gravatar image

Mike Korrigan
11 1 1 1

If there's any way you can repost that with better formatting it may be easier for us to read.
Oct 14 '09 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 '09 at 11:05 PM

Michael Haren gravatar image

Michael Haren
51 1

Thanks you gave it a good look.

Obiviously I don't understand:

" ACTPHY01 C WHERE C.B1LKNB=RPI8NS and C.B1HXCD=RPMBXU) > 0 "

Is this a logical 0/1, true false ?
Oct 16 '09 at 12:01 PM Mike Korrigan
(comments are locked)
10|1200 characters needed characters left

Looks like this is for Oracle

more ▼

answered Oct 15 '09 at 09:14 AM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

Yes because of || and substr
Oct 15 '09 at 09:36 AM Madhivanan
The database is DB2
Oct 16 '09 at 11:58 AM Mike Korrigan
This is for MS SQL Server
Nov 11 '09 at 06:16 AM Madhivanan
Nov 11 '09 at 11:58 AM Squirrel 1
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x19

asked: Oct 14 '09 at 04:37 PM

Seen: 720 times

Last Updated: Nov 11 '09 at 06:13 AM