SQL Progress Outer Join question : Progress SQL

Experts Exchange

	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

10/01/2009 at 12:02PM PDT, ID: 24778124

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.2

SQL Progress Outer Join question

Asked by edwardq in Progress 4GL Database, Databases Miscellaneous

Tags: Progress SQL

I have a query for our Progress database what I use a DSN from a .net program to get the dataset. I use brio explorer create the queries.

I need to add a table to the query but as you can see in the below, I need to use cross joins to get the data. the table I need to add may not have a record for the product. I need a blank or null returned.

The query is

SELECT AL1.cono, AL4.programid, AL1.whse, AL4.programty, AL5.name as WhseName, AL3.descrip as Description, AL1.prod, AL1.binloc1 as Binloc,
AL6.lastrcptdt as MainLastrcptdt, AL6.usagerate as Mainusagerate, AL6.leadtmavg as MainLeadtMavg, AL6.qtyonorder as MainQtyOnOrder,
AL6.class as MainClass, AL6.linept as MainMax, AL6.orderpt as MainMin, AL1.statustype, AL6.arpVendNo as VendNo, AL3.UnitSell as UnitSellSize,
AL5.Custno, AL4.Shipto,AL1.lastinvdt, AL1.arpwhse,AL6.Statustype as MainStatusType,AL1.user6 as LotQty, AL1.avgcost,
AL1.qtyreservd, AL1.qtyonhand, AL6.lastinvdt as MainLastInvDt, AL1.linept as remoteMax, AL1.orderpt as remoteMin,
AL1.enterdt, AL1.user2 as SpotBuyFl, AL1.usagerate, AL2.normusage, AL1.qtyonorder, AL3.unitcnt, AL1.lastrcptdt,
AL7.normusage as MainNormusage, AL6.avgcost as MAINavgcost, AL6.user6 as MainLotQty, AL8.name as VendName
FROM PUB.icsw AL1, PUB.icswu AL2, PUB.icsp AL3, PUB.zzmtsicsd AL4, PUB.icsd AL5, PUB.icsw AL6, PUB.icswu AL7, PUB.apsv AL8
WHERE (AL1.cono=AL2.cono AND
            AL1.whse=AL2.whse AND
            AL3.cono=AL1.cono AND
            AL3.prod=AL1.prod AND
            AL1.prod=AL2.prod AND
            AL1.cono=AL4.cono AND
            AL1.whse=AL4.whse AND
            AL1.cono=AL5.cono AND
            AL1.whse=AL5.whse AND
            AL6.cono=AL1.cono AND
            AL1.arpwhse=AL6.whse AND
            AL1.prod=AL6.prod AND
            AL7.cono=AL1.cono AND
            AL7.prod=AL1.prod AND
            AL7.whse=AL1.arpwhse AND
            AL8.cono=AL6.cono AND
            AL8.vendno=AL6.arpVendNo) AND
            (AL1.cono=1 AND AL4.programid='ATK'
            ((NOT AL1.statustype='x') OR (AL1.statustype='x' AND AL1.qtyonhand > 0)) )

I need to add the PUB.zzplan table. It attaches to the PUB.icsw AL1 table
AL1.cono=PUB.zzplan.cono
AL1.whse=PUB.zzplan.Rwhse
AL1.prod=PUB.zzplan.prod

to return PUB.zzplan.partclass.

I need it to return all the records that is in the icsw table and return a null/blank for the partclass if there is no record.

Thanks.

View the Solution FREE for 30 Days

20090824-EE-VQP-74 - Hierarchy / EE_QW_3_20080625

1. progressor14,450
2. ahensch6,000
3. FishMonger4,000
4. dlauzon3,000
5. emoreau2,500
5. ragi00172,500
5. duncan_roe2,500
8. ralmada2,000
8. gohord2,000
8. jkr2,000
8. robertfwo...2,000
8. leonstryker2,000
8. Milleniuma...2,000
8. StraySod2,000
8. qayin2,000
16. TheLearn...1,500
16. BillBach1,500
18. CB_Thiru...1,400
18. SidFishes1,400
20. warba1,000
20. jimpen1,000
22. mlmcc750
23. Plucka600
23. skvikram600
25. udayakum...500

1. progressor21,914
2. dlauzon7,000
3. ahensch6,000
4. FishMonger4,000
5. FGomez023,450
6. mlmcc3,050
7. warba3,000
8. emoreau2,500
8. ragi00172,500
8. duncan_roe2,500
8. BillBach2,500
12. ralmada2,000
12. gohord2,000
12. jkr2,000
12. robertfwo...2,000
12. BdLm2,000
12. leonstryker2,000
12. nmcdermaid2,000
12. cs97jjm32,000
12. Milleniuma...2,000
12. mvbernot2,000
12. Jaaze2,000
12. c_pop2,000
12. nutsch2,000
12. jazzIIIlove2,000
12. StraySod2,000
12. qayin2,000

SQL Progress Outer Join question

Asked by edwardq in Progress 4GL Database, Databases Miscellaneous

Tags: Progress SQL

About this solution