April 12, 2005 at 3:20 am
If I use a view inside a view, is it recomended and how will it affect the performance. I need this becasue I cant use an Order by clause in the inner view.
April 12, 2005 at 6:17 am
In your INNER VIEW why can't you use SELECT TOP 100 PERCENT ... ORDER BY fieldx?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 12, 2005 at 6:29 am
Becasue I am using a Union ALL
SELECT BatchNoFormulation.BAMF_ID AS BatchNoID, BatchNoFormulation.BAMF_BatchNo AS BatchNo,
BOMTFormulationDet.BTFD_StageProcessID AS StageProcessID,
StageMaster.STAG_StageDesc + ' - ' + ProcessMaster.PROC_ProcessDesc AS StageProcessDescription,
BOMTFormulationDet.BTFD_ItemID AS MainItemCode, NULL AS SubstituteItemCode, BOMTFormulationDet.BTFD_GradeID AS GradeID,
BOMTFormulationDet.BTFD_SourceID AS SourceID, BOMTFormulationDet.BTFD_Qty AS Quantity,
ManufacturingCode.MFCD_MfgCode AS ManufacturingCode, ManufacturingCode.MFCD_MfgDesc AS ManufacturingDescription, 'N' AS IsSubstituteItem,
BOMTFormulationDet.BTFD_ID AS TransactionID, BMRTypeDetail.BMRD_StageOrder AS StageOrder,
BMRTypeDetail.BMRD_ProcessOrder AS ProcessOrder, BOMTFormulationDet.BTFD_LOTNo AS LotNo
FROM BOMTFormulationDet INNER JOIN
BOMTFormulation ON BOMTFormulationDet.BTFD_BMFTBOMID = BOMTFormulation.BMFT_BOMID INNER JOIN
BMRTypeDetail ON BOMTFormulationDet.BTFD_StageProcessID = BMRTypeDetail.BMRD_ID INNER JOIN
StageMaster ON BMRTypeDetail.BMRD_STAGID = StageMaster.STAG_ID INNER JOIN
ProcessMaster ON BMRTypeDetail.BMRD_ProcessID = ProcessMaster.PROC_ID INNER JOIN
BatchNoFormulation ON BOMTFormulation.BMFT_BAMFID = BatchNoFormulation.BAMF_ID INNER JOIN
ManufacturingCode ON BatchNoFormulation.BAMF_MfgCode = ManufacturingCode.MFCD_MfgID LEFT OUTER JOIN
BOMTFormulationDispensed ON BMRTypeDetail.BMRD_ID = BOMTFormulationDispensed.BFDI_StageProcessID AND
BatchNoFormulation.BAMF_ID = BOMTFormulationDispensed.BFDI_BAMFID
WHERE BOMTFormulationDispensed.BFDI_IsDispensed = 1
UNION ALL
SELECT BatchNoFormulation.BAMF_ID AS BatchNoID, BatchNoFormulation.BAMF_BatchNo AS BatchNo,
BOMTFormulationDet.BTFD_StageProcessID AS StageProcessID,
StageMaster.STAG_StageDesc + ' - ' + ProcessMaster.PROC_ProcessDesc AS StageProcessDescription,
BOMTFormulationDet.BTFD_ItemID AS MainItemCode, BOMTFormulationSub.BTFS_SubstituteItemID AS SubstituteItemCode,
BOMTFormulationSub.BTFS_GradeID AS GradeID, BOMTFormulationSub.BTFS_SourceID AS SourceID, BOMTFormulationSub.BTFS_Qty AS Quantity,
ManufacturingCode.MFCD_MfgCode AS ManufacturingCode, ManufacturingCode.MFCD_MfgDesc AS ManufacturingDescription, 'Y' AS IsSubstituteItem,
BOMTFormulationSub.BTFS_ID AS TransactionID, BMRTypeDetail.BMRD_StageOrder AS StageOrder,
BMRTypeDetail.BMRD_ProcessOrder AS ProcessOrder, BOMTFormulationSub.BTFS_LOTNo AS LotNo
FROM BOMTFormulationSub INNER JOIN
BOMTFormulation INNER JOIN
StageMaster INNER JOIN
BMRTypeDetail ON StageMaster.STAG_ID = BMRTypeDetail.BMRD_STAGID INNER JOIN
ProcessMaster ON BMRTypeDetail.BMRD_ProcessID = ProcessMaster.PROC_ID INNER JOIN
BOMTFormulationDet ON BMRTypeDetail.BMRD_ID = BOMTFormulationDet.BTFD_StageProcessID ON
BOMTFormulation.BMFT_BOMID = BOMTFormulationDet.BTFD_BMFTBOMID ON
BOMTFormulationSub.BTFS_BTFDID = BOMTFormulationDet.BTFD_ID INNER JOIN
BatchNoFormulation ON BOMTFormulation.BMFT_BAMFID = BatchNoFormulation.BAMF_ID INNER JOIN
ManufacturingCode ON BatchNoFormulation.BAMF_MfgCode = ManufacturingCode.MFCD_MfgID LEFT OUTER JOIN
BOMTFormulationDispensed ON BMRTypeDetail.BMRD_ID = BOMTFormulationDispensed.BFDI_StageProcessID AND
BatchNoFormulation.BAMF_ID = BOMTFormulationDispensed.BFDI_BAMFID
WHERE (BOMTFormulationDispensed.BFDI_IsDispensed = 1))
April 12, 2005 at 10:04 pm
According to BOL, Combining Results with UNION, you can have a trailing ORDER BY clause in a UNION ALL query, that affects the resulting set. If you desire individual ORDER BY, then use the TOP 100 ... ORDER BY ... within two Views then:
SELECT * from vw_first
UNION ALL
SELECT * FROM vw_second
ORDER BY ...
Just remember that the UNION ALL will force a re-compare of each set, to remove dupes, so that the UNION ALL's ORDER BY provides the final order for the set.
Andy
April 13, 2005 at 6:50 am
Actually UNION ALL doesn't remove dups, it's UNION that does that.
April 13, 2005 at 6:59 am
Yes exactly, Union removes dups and Union ALL includes them
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy