Home Rows are missing from SQL query
Reply: 0

Rows are missing from SQL query

user2791
1#
user2791 Published in June 19, 2018, 6:18 pm

I need to have the count of assets on SalesOrderLine where SalesOrderLine.Shipped <> 'C'. The following SQL query doesn't retrieve any rows if there's no rows in SalesOrderLine or for all rows there SalesOrderLine.Shipped = 'C'. Could you please advise where am I wrong here?

To be more specific:

  • All columns have character data type (mostly nvarchar).
  • If I remove

    AND SalesOrderLine.Shipped <> ''C''
    

    from WHERE part, it retrieves some rows. Of course it's not what I need.

Here is an example of one SO with 140 assets that for all, SalesOrderLine.Shipped = 'C':

Order No    Customer Name   Customer PO Asset Count SO Status   Released    Raised Date Order Type  Address Deliveries (Connotes)
O30540  ITPG Group  IAL4445, IAL3062    140 Cancelled   NO  11/01/2015  Sold    NYIAL5554, ITPL6032 NULL

Here is the query:

('SELECT SalesOrderHeader.OrderNo AS [Order No], SalesOrderHeader.CustomerName AS [Customer Name], SalesOrderHeader.Reference1 AS [Customer PO], COUNT(SalesOrderLine.Barcode) AS [Asset Count], 
        LookUpData.CategoryLink AS [SO Status], (CASE WHEN isReleased = ''1'' THEN ''YES'' ELSE ''NO'' END) AS Released, SalesOrderHeader.RaisedDate AS [Raised Date], SalesOrderHeader.Ordertype AS [Order Type], 
        SalesOrderHeader.DeliverySuburb + SalesOrderHeader.DeliveryAddress1 AS [Address], (CASE WHEN LEN(pre_trimmed.DeliveryNo) < 1 THEN pre_trimmed.DeliveryNo ELSE LEFT(pre_trimmed.DeliveryNo , LEN(pre_trimmed.DeliveryNo)-1) END) AS [Deliveries (Connotes)]  
FROM SalesOrderHeader 
        INNER JOIN LookUpData ON SalesOrderHeader.Status = LookUpData.CategoryName 
        INNER JOIN SalesOrderLine ON SalesOrderHeader.OrderNo = SalesOrderLine.OrderNo 
        LEFT JOIN DeliveryHeader ON SalesOrderHeader.OrderNo = DeliveryHeader.OrderNo
CROSS APPLY
(
    SELECT DeliveryNo + '' ('' + Connote + ''), ''
    FROM DeliveryHeader AS intern
    WHERE DeliveryHeader.OrderNo = intern.OrderNo
    FOR XML PATH('''')
) pre_trimmed (DeliveryNo)
    WHERE 
        ' + @FieldName + ' Like ''%' + @FieldValue + '%'' 
        AND (LookUpData.Category = ''SOStatus'' AND SalesOrderLine.Shipped <> ''C'')  
    GROUP BY SalesOrderHeader.OrderNo , SalesOrderHeader.CustomerName, SalesOrderHeader.Reference1 , 
        LookUpData.CategoryLink, SalesOrderHeader.RaisedDate, SalesOrderHeader.Ordertype, 
        SalesOrderHeader.DeliverySuburb, SalesOrderHeader.DeliveryAddress1, pre_trimmed.DeliveryNo, SalesOrderHeader.isReleased 
        ORDER BY SalesOrderHeader.CustomerName, SalesOrderHeader.OrderNo')
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305191 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO