Post reply

VIEW INSIDE A VIEW

  • April 12, 2005 at 3:20 am

    #88680

    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

    #552105

    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

    #552112

    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

    #552282

    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

    #552394

    Actually UNION ALL doesn't remove dups, it's UNION that does that.

  • April 13, 2005 at 6:59 am

    #552397

    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