Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ssimon
Starting Member
16 Posts |
 Posted - 2011-04-12 : 15:54:43
|
I am trying to move some Crystal Reports over to SSRS and came across a huge query that someone had built, I believe can be shortened by using CTE. I have tried multiple CTE queries but I have failed to find the right one. (I have listed the query below the link for the picture). Note - In the Product_Structure table I have added two parts that should not show on the report (102433 and 100190 - just to show that there are sub parts to some but not all top level parts or PARPRT_02). Thanks for ANY assistance, ideas, etc.. If image does not work-> https://picasaweb.google.com/lh/photo/Blru0HftV9cnL9wUwdsU1GygwcGaoBsP-W-oz_51Xl8?feat=directlink
SELECT "Part_Master"."PRTNUM_01", "Product_Structure"."COMPRT_02", "Part_Master"."TYPE_01", "Part_Master_2"."TYPE_01", "Product_Structure_2"."COMPRT_02", "Part_Master_3"."TYPE_01", "Product_Structure_3"."COMPRT_02", "Part_Master"."PMDES1_01", "Part_Master_2"."PMDES1_01", "Part_Master_3"."PMDES1_01", "Product_Structure_4"."COMPRT_02", "Product_Structure"."QTYPER_02", "Product_Structure_2"."QTYPER_02", "Product_Structure_5"."COMPRT_02", "Product_Structure_6"."COMPRT_02", "Product_Structure"."EFFDTE_02", "Product_Structure_2"."EFFDTE_02", "Product_Structure_3"."EFFDTE_02", "Product_Structure_4"."EFFDTE_02", "Product_Structure_5"."EFFDTE_02", "Product_Structure_6"."EFFDTE_02", "Part_Master_2"."BOMUOM_01", "Part_Master_3"."BOMUOM_01", "Part_Master_4"."TYPE_01", "Part_Master_5"."TYPE_01", "Part_Master_6"."TYPE_01", "Part_Master_7"."TYPE_01", "Part_Master_4"."PMDES1_01", "Part_Master_5"."PMDES1_01", "Part_Master_6"."PMDES1_01", "Part_Master_7"."PMDES1_01", "Product_Structure_3"."QTYPER_02", "Product_Structure_4"."QTYPER_02", "Product_Structure_5"."QTYPER_02", "Product_Structure_6"."QTYPER_02", "Part_Master_4"."BOMUOM_01", "Part_Master_5"."BOMUOM_01", "Part_Master_6"."BOMUOM_01", "Part_Master_7"."BOMUOM_01", "Part_Master"."COMCDE_01", "Part_Master_2"."COMCDE_01", "Part_Master_3"."COMCDE_01", "Part_Master_4"."COMCDE_01", "Part_Master_5"."COMCDE_01", "Part_Master_6"."COMCDE_01", "Part_Master_7"."COMCDE_01" FROM ((((((((((("dbo"."Part_Master" "Part_Master" LEFT OUTER JOIN "dbo"."Product_Structure" "Product_Structure" ON "Part_Master"."PRTNUM_01"="Product_Structure"."PARPRT_02") LEFT OUTER JOIN "dbo"."Part_Master" "Part_Master_2" ON "Product_Structure"."COMPRT_02"="Part_Master_2"."PRTNUM_01") LEFT OUTER JOIN "dbo"."Product_Structure" "Product_Structure_2" ON "Product_Structure"."COMPRT_02"="Product_Structure_2"."PARPRT_02") LEFT OUTER JOIN "dbo"."Part_Master" "Part_Master_3" ON "Product_Structure_2"."COMPRT_02"="Part_Master_3"."PRTNUM_01") LEFT OUTER JOIN "dbo"."Product_Structure" "Product_Structure_3" ON "Product_Structure_2"."COMPRT_02"="Product_Structure_3"."PARPRT_02") LEFT OUTER JOIN "dbo"."Product_Structure" "Product_Structure_4" ON "Product_Structure_3"."COMPRT_02"="Product_Structure_4"."PARPRT_02") LEFT OUTER JOIN "dbo"."Part_Master" "Part_Master_4" ON "Product_Structure_3"."COMPRT_02"="Part_Master_4"."PRTNUM_01") LEFT OUTER JOIN "dbo"."Part_Master" "Part_Master_5" ON "Product_Structure_4"."COMPRT_02"="Part_Master_5"."PRTNUM_01") LEFT OUTER JOIN "dbo"."Product_Structure" "Product_Structure_5" ON "Product_Structure_4"."COMPRT_02"="Product_Structure_5"."PARPRT_02") LEFT OUTER JOIN "dbo"."Part_Master" "Part_Master_6" ON "Product_Structure_5"."COMPRT_02"="Part_Master_6"."PRTNUM_01") LEFT OUTER JOIN "dbo"."Product_Structure" "Product_Structure_6" ON "Product_Structure_5"."COMPRT_02"="Product_Structure_6"."PARPRT_02") LEFT OUTER JOIN "dbo"."Part_Master" "Part_Master_7" ON "Product_Structure_6"."COMPRT_02"="Part_Master_7"."PRTNUM_01" WHERE ("Part_Master"."PRTNUM_01"='4235' AND "Part_Master"."COMCDE_01"='DOC' OR "Part_Master"."PRTNUM_01"='4235' AND "Part_Master_2"."COMCDE_01"='DOC' OR "Part_Master"."PRTNUM_01"='4235' AND "Part_Master_3"."COMCDE_01"='DOC' OR "Part_Master"."PRTNUM_01"='4235' AND "Part_Master_4"."COMCDE_01"='DOC' OR "Part_Master"."PRTNUM_01"='4235' AND "Part_Master_5"."COMCDE_01"='DOC' OR "Part_Master"."PRTNUM_01"='4235' AND "Part_Master_6"."COMCDE_01"='DOC' OR "Part_Master"."PRTNUM_01"='4235' AND "Part_Master_7"."COMCDE_01"='DOC') ORDER BY "Part_Master"."PRTNUM_01", "Product_Structure"."COMPRT_02", "Product_Structure"."EFFDTE_02"DESC, "Product_Structure_2"."COMPRT_02", "Product_Structure_2"."EFFDTE_02"DESC, "Product_Structure_3"."COMPRT_02", "Product_Structure_3"."EFFDTE_02"DESC, "Product_Structure_4"."COMPRT_02", "Product_Structure_4"."EFFDTE_02"DESC, "Product_Structure_5"."COMPRT_02", "Product_Structure_5"."EFFDTE_02"DESC, "Product_Structure_6"."COMPRT_02", "Product_Structure_6"."EFFDTE_02" DESCedit: took out code tag because of long lines |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-04-12 : 16:51:23
|
Just some clean up helps:I'll work on cte next... but for anyone else looking:SELECT pm.PRTNUM_01, ps.COMPRT_02, pm.TYPE_01, pm2.TYPE_01, ps2.COMPRT_02, pm3.TYPE_01, ps3.COMPRT_02, pm.PMDES1_01, pm2.PMDES1_01, pm3.PMDES1_01, ps4.COMPRT_02, ps.QTYPER_02, ps2.QTYPER_02, ps5.COMPRT_02, ps6.COMPRT_02, ps.EFFDTE_02, ps2.EFFDTE_02, ps3.EFFDTE_02, ps4.EFFDTE_02, ps5.EFFDTE_02, ps6.EFFDTE_02, pm2.BOMUOM_01, pm3.BOMUOM_01, pm4.TYPE_01, pm5.TYPE_01, pm6.TYPE_01, pm7.TYPE_01, pm4.PMDES1_01, pm5.PMDES1_01, pm6.PMDES1_01, pm7.PMDES1_01, ps3.QTYPER_02, ps4.QTYPER_02, ps5.QTYPER_02, ps6.QTYPER_02, pm4.BOMUOM_01, pm5.BOMUOM_01, pm6.BOMUOM_01, pm7.BOMUOM_01, pm.COMCDE_01, pm2.COMCDE_01, pm3.COMCDE_01, pm4.COMCDE_01, pm5.COMCDE_01, pm6.COMCDE_01, pm7.COMCDE_01FROM dbo.Part_Master pm LEFT OUTER JOIN dbo.Product_Structure As ps ON pm.PRTNUM_01 = ps.PARPRT_02LEFT OUTER JOIN dbo.Part_Master As pm2 ON ps.COMPRT_02 = pm2.PRTNUM_01LEFT OUTER JOIN dbo.Product_Structure As ps2 ON Product_Structure.COMPRT_02 = ps2.PARPRT_02LEFT OUTER JOIN dbo.Part_Master As pm3 ON ps2.COMPRT_02 = pm3.PRTNUM_01LEFT OUTER JOIN dbo.Product_Structure As ps3 ON ps2.COMPRT_02 = ps3.PARPRT_02LEFT OUTER JOIN dbo.Product_Structure As ps4 ON ps3.COMPRT_02 = ps4.PARPRT_02LEFT OUTER JOIN dbo.Part_Master As pm4 ON ps3.COMPRT_02 = pm4.PRTNUM_01LEFT OUTER JOIN dbo.Part_Master As pm5 ON ps4.COMPRT_02 = pm5.PRTNUM_01LEFT OUTER JOIN dbo.Product_Structure As ps5 ON ps4.COMPRT_02 = ps5.PARPRT_02LEFT OUTER JOIN dbo.Part_Master As pm6 ON ps5.COMPRT_02 = pm6.PRTNUM_01LEFT OUTER JOIN dbo.Product_Structure As ps6 ON ps5.COMPRT_02 = ps6.PARPRT_02LEFT OUTER JOIN dbo.Part_Master As pm7 ON ps6.COMPRT_02 = pm7.PRTNUM_01--I suspect that you really meant to have the And/Ors grouped like thisWHERE (pm.PRTNUM_01='4235' AND pm.COMCDE_01='DOC')OR (pm.PRTNUM_01='4235' AND pm2.COMCDE_01='DOC')OR (pm.PRTNUM_01='4235' AND pm3.COMCDE_01='DOC')OR (pm.PRTNUM_01='4235' AND pm4.COMCDE_01='DOC')OR (pm.PRTNUM_01='4235' AND pm5.COMCDE_01='DOC')OR (pm.PRTNUM_01='4235' AND pm6.COMCDE_01='DOC')OR (pm.PRTNUM_01='4235' AND pm7.COMCDE_01='DOC')--You should be able to do this also:--pm.PRTNUM_01='4235' AND 'DOC' in (pm1.COMCDE_01, pm2.COMCDE_01, pm3.COMCDE_01, pm4.COMCDE_01, pm5.COMCDE_01, pm6.COMCDE_01, pm7.COMCDE_01)ORDER BY pm.PRTNUM_01, ps.COMPRT_02, ps.EFFDTE_02 DESC, ps2.COMPRT_02, ps2.EFFDTE_02 DESC, ps3.COMPRT_02, ps3.EFFDTE_02 DESC, ps4.COMPRT_02, ps4.EFFDTE_02 DESC, ps5.COMPRT_02, ps5.EFFDTE_02 DESC, ps6.COMPRT_02, ps6.EFFDTE_02 DESC Corey I Has Returned!! |
 |
|
|
|
|
|
|
|
CTE assistance