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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Well, this is ugly.

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2009-03-18 : 10:05:56
Please consider the following query which does work: 

SELECT dbo.somast.[fcompany],
       dbo.somast.[fcustno],
       dbo.somast.[forderdate],
       sorels.fsono,
       dbo.somast.[fsorev],
       dbo.somast.[fcustpono],
       sorels.identity_column,
       sorels.fsono,
       sorels.fenumber,
       sorels.frelease,
       coalesce (InMast.fOnHand, 0) AS [On Hand],
       (SORELS.fshipbook + SORELS.fshipbuy + sorels.fshipmake) AS shipped,
       sorels.finvqty,
       sorels.forderqty,
       COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                              + sorels.finumber
                                              + sorels.frelease),
                 0
       )
          AS SHIPPEDNOTINVOICED,
       (SORELS.forderqty - SORELS.finvqty
        - COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                                 + sorels.finumber
                                                 + sorels.frelease),
                    0
          ))
       * SORELS.funetprice
          AS NewFNBOAMT,
       (SELECT    TOP 1
               poitem.fpono
          FROM    POITEM_EXT
               INNER JOIN
                  poitem
               ON poitem.identity_column = poitem_ext.fkey_id
         WHERE ltrim (rtrim (sorels.fsono)) =
                  ltrim (rtrim (poitem_ext.SALESORDERNO)))
          AS [PO Num]

  FROM          SORELS
             JOIN
                SOMAST
             ON SOMAST.FSONO = SORELS.FSONO
          JOIN
             SOITEM
          ON SOITEM.FSONO = SORELS.FSONO AND SOITEM.FINUMBER = SORELS.FINUMBER
       LEFT OUTER JOIN
          inmast InMast
       ON (SORels.fPartNo = InMast.fPartNo)
          AND (SORels.fpartRev = InMast.fRev)

 WHERE     SOMAST.FSTATUS = 'OPEN'
       AND FMASTERREL = 0
       AND somast.forderdate >= CONVERT (DATETIME, '01/01/2002')


It works, but is slower than molasses because it is scanning each record in the main query against the sub query.

When I try to following it runs quickly and I don't get an error, but I receive all Nulls for P.salesorderno. 

SELECT dbo.somast.[fcompany],
       dbo.somast.[fcustno],
       dbo.somast.[forderdate],
       sorels.fsono,
       dbo.somast.[fsorev],
       dbo.somast.[fcustpono],
       sorels.identity_column,
       sorels.fsono,
       sorels.fenumber,
       sorels.frelease,
       coalesce (InMast.fOnHand, 0) AS [On Hand],
       (SORELS.fshipbook + SORELS.fshipbuy + sorels.fshipmake) AS shipped,
       sorels.finvqty,
       sorels.forderqty,
       COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                              + sorels.finumber
                                              + sorels.frelease),
                 0
       )
          AS SHIPPEDNOTINVOICED,
       (SORELS.forderqty - SORELS.finvqty
        - COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                                 + sorels.finumber
                                                 + sorels.frelease),
                    0
          ))
       * SORELS.funetprice
          AS NewFNBOAMT,
       P.FPONO
  FROM             SORELS
                JOIN
                   SOMAST
                ON SOMAST.FSONO = SORELS.FSONO
             JOIN
                SOITEM
             ON SOITEM.FSONO = SORELS.FSONO
                AND SOITEM.FINUMBER = SORELS.FINUMBER
          LEFT OUTER JOIN
             inmast InMast
          ON (SORels.fPartNo = InMast.fPartNo)
             AND (SORels.fpartRev = InMast.fRev)
       LEFT OUTER JOIN
          (SELECT    TOP 1
                  poitem.fpono, poitem_ext.salesorderno
             FROM    POITEM_EXT
                  INNER JOIN
                     poitem
                  ON poitem.identity_column = poitem_ext.fkey_id
            WHERE len (poitem_ext.salesorderno) = 6) P
       ON ltrim (rtrim (sorels.fsono)) = ltrim (rtrim (P.SALESORDERNO))
 WHERE     SOMAST.FSTATUS = 'OPEN'
       AND FMASTERREL = 0
       AND somast.forderdate >= CONVERT (DATETIME, '01/01/2002')


Can anyone tell me why?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-18 : 10:18:43
In your 2nd query where you have,
 (SELECT    TOP 1......(P.SALESORDERNO))
data set is just one row. Thats not the same as your first query.
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-18 : 10:22:50
In your first query, you are scanning for top 1 match on fpono from poitem for every fsono in Sorels table. In your second query, you are taking top 1 fpono match between POITEM_EXT and poitem and matching the same value against every record in Sorels table. You'll see a lot of nulls because of this.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2009-03-18 : 10:40:23
Well, the problem is that the relationship between poitem_ext.salesorderno and sorels.fsono is not 1 to 1. I simply want to return the first record which corresponds.

I tried this, and it's closer to what I need: 

SELECT dbo.somast.[fcompany],
       dbo.somast.[fcustno],
       dbo.somast.[forderdate],
       sorels.fsono,
       dbo.somast.[fsorev],
       dbo.somast.[fcustpono],
       sorels.identity_column,
       sorels.fsono,
       sorels.fenumber,
       sorels.frelease,
       coalesce (InMast.fOnHand, 0) AS [On Hand],
       (SORELS.fshipbook + SORELS.fshipbuy + sorels.fshipmake) AS shipped,
       sorels.finvqty,
       sorels.forderqty,
       COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                              + sorels.finumber
                                              + sorels.frelease),
                 0
       )
          AS SHIPPEDNOTINVOICED,
       (SORELS.forderqty - SORELS.finvqty
        - COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                                 + sorels.finumber
                                                 + sorels.frelease),
                    0
          ))
       * SORELS.funetprice
          AS NewFNBOAMT,
       P.FPONO
  FROM             SORELS
                JOIN
                   SOMAST
                ON SOMAST.FSONO = SORELS.FSONO
             JOIN
                SOITEM
             ON SOITEM.FSONO = SORELS.FSONO
                AND SOITEM.FINUMBER = SORELS.FINUMBER
          LEFT OUTER JOIN
             inmast InMast
          ON (SORels.fPartNo = InMast.fPartNo)
             AND (SORels.fpartRev = InMast.fRev)
       LEFT OUTER JOIN
          (SELECT    distinct                  poitem.fpono, poitem_ext.salesorderno
             FROM    POITEM_EXT
                  INNER JOIN
                     poitem
                  ON poitem.identity_column = poitem_ext.fkey_id
            WHERE len (poitem_ext.salesorderno) = 6) P
       ON ltrim (rtrim (sorels.fsono)) = ltrim (rtrim (P.SALESORDERNO))
 WHERE     SOMAST.FSTATUS = 'OPEN'
       AND FMASTERREL = 0
       AND somast.forderdate >= CONVERT (DATETIME, '01/01/2002')


However, I still end up with "extra" records. Any ideas?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2009-03-18 : 10:41:27
Oh! What about grouping the subquery and taking the top 1? I'm going to give that a try.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2009-03-18 : 10:49:20
When I try this: 

SELECT dbo.somast.[fcompany],
       dbo.somast.[fcustno],
       dbo.somast.[forderdate],
       sorels.fsono,
       dbo.somast.[fsorev],
       dbo.somast.[fcustpono],
       sorels.identity_column,
       sorels.fsono,
       sorels.fenumber,
       sorels.frelease,
       coalesce (InMast.fOnHand, 0) AS [On Hand],
       (SORELS.fshipbook + SORELS.fshipbuy + sorels.fshipmake) AS shipped,
       sorels.finvqty,
       sorels.forderqty,
       COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                              + sorels.finumber
                                              + sorels.frelease),
                 0
       )
          AS SHIPPEDNOTINVOICED,
       (SORELS.forderqty - SORELS.finvqty
        - COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                                 + sorels.finumber
                                                 + sorels.frelease),
                    0
          ))
       * SORELS.funetprice
          AS NewFNBOAMT,
       P.FPONO
  FROM             SORELS
                JOIN
                   SOMAST
                ON SOMAST.FSONO = SORELS.FSONO
             JOIN
                SOITEM
             ON SOITEM.FSONO = SORELS.FSONO
                AND SOITEM.FINUMBER = SORELS.FINUMBER
          LEFT OUTER JOIN
             inmast InMast
          ON (SORels.fPartNo = InMast.fPartNo)
             AND (SORels.fpartRev = InMast.fRev)
       LEFT OUTER JOIN
          (SELECT    poitem.fpono, max(poitem_ext.salesorderno)
             FROM    POITEM_EXT
                  INNER JOIN
                     poitem
                  ON poitem.identity_column = poitem_ext.fkey_id
            WHERE len (poitem_ext.salesorderno) = 6
            group by poitem.fpono) P
       ON ltrim (rtrim (sorels.fsono)) = ltrim (rtrim (P.SALESORDERNO))
 WHERE     SOMAST.FSTATUS = 'OPEN'
       AND FMASTERREL = 0
       AND somast.forderdate >= CONVERT (DATETIME, '01/01/2002')


I get the following error:
Error 3/18/2009 9:47:22 AM 0:00:00.046 SQL Server Database Error: No column was specified for column 2 of 'P'. 2 0
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-18 : 11:07:05
this should be ok,,

SELECT dbo.somast.[fcompany],
       dbo.somast.[fcustno],
       dbo.somast.[forderdate],
       sorels.fsono,
       dbo.somast.[fsorev],
       dbo.somast.[fcustpono],
       sorels.identity_column,
       sorels.fsono,
       sorels.fenumber,
       sorels.frelease,
       coalesce (InMast.fOnHand, 0) AS [On Hand],
       (SORELS.fshipbook + SORELS.fshipbuy + sorels.fshipmake) AS shipped,
       sorels.finvqty,
       sorels.forderqty,
       COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                              + sorels.finumber
                                              + sorels.frelease),
                 0
       )
          AS SHIPPEDNOTINVOICED,
       (SORELS.forderqty - SORELS.finvqty
        - COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(  sorels.fsono
                                                 + sorels.finumber
                                                 + sorels.frelease),
                    0
          ))
       * SORELS.funetprice
          AS NewFNBOAMT,
       P.FPONO
  FROM             SORELS
                JOIN
                   SOMAST
                ON SOMAST.FSONO = SORELS.FSONO
             JOIN
                SOITEM
             ON SOITEM.FSONO = SORELS.FSONO
                AND SOITEM.FINUMBER = SORELS.FINUMBER
          LEFT OUTER JOIN
             inmast InMast
          ON (SORels.fPartNo = InMast.fPartNo)
             AND (SORels.fpartRev = InMast.fRev)
       LEFT OUTER JOIN
          (SELECT    poitem.fpono,SALESORDERNO= max(poitem_ext.salesorderno)
             FROM    POITEM_EXT
                  INNER JOIN
                     poitem
                  ON poitem.identity_column = poitem_ext.fkey_id
            WHERE len (poitem_ext.salesorderno) = 6
            group by poitem.fpono) P
       ON ltrim (rtrim (sorels.fsono)) = ltrim (rtrim (P.SALESORDERNO))
 WHERE     SOMAST.FSTATUS = 'OPEN'
       AND FMASTERREL = 0
       AND somast.forderdate >= CONVERT (DATETIME, '01/01/2002')
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2009-03-18 : 11:07:39
Ha. Cool, thanks sakets.
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-18 : 11:34:07
np
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site