Solved

SQL query

Posted on 2014-04-08
2
412 Views
Last Modified: 2014-04-09
Hi
Please attached my file and see below
sql, I'm getting after run following sql  0, can anyone explain where iam doing mistake.

Results is zero.
Main sql code
select  SUM(inall.price  *inall.qty_invoiced),ROUND(SUM((Case when inall.qty_invoiced=0 then art.amount  else ((inall.price  *inall.qty_invoiced)/hdr.exch_rate) end)),2) as SalesTotal,
hdr.slsman,MONTH(hdr.inv_date) as SalesMonth,hdr.co_num
from inv_hdr hdr
INNER JOIN inv_item_all inall ON  hdr.inv_num=inall.inv_num
LEFT OUTER JOIN  artran art ON hdr.inv_num=art.inv_num
Left outer join custaddr custadd ON  hdr.cust_num= custadd.cust_num
LEFT Outer Join coitem co ON custadd.cust_num=co.cust_num
where YEAR(hdr.inv_date)= YEAR(getdate())   and (hdr.slsman ='F000001' and hdr.co_num='SFT0003575' ) and art.type in('I','C') and (MONTH(hdr.inv_date))=2
and (SUBSTRING( co.non_inv_acct,1,1)=5 or  co.non_inv_acct is null)
and custadd.cust_seq=0
group by (MONTH(hdr.inv_date)),hdr.slsman,hdr.co_num

Open in new window



zero results for SalesTotal
But if I  run like that without main code its working fine, please show me where iam doing mistake in main SQL
select sum((qty_invoiced* price)/7.47) as Total from inv_item_all where co_num='SFT0003575'
group by co_num

Open in new window

Sqlhelp.xlsx
0
Comment
Question by:ukerandi
2 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 39987696
Your LEFT JOIN artran and LEFT JOIN custaddr  are incorrectly defined.  If you really want a LEFT JOIN on those two tables than you need to write it this way:
SELECT  SUM(inall.price * inall.qty_invoiced),
        ROUND(SUM((CASE WHEN inall.qty_invoiced = 0 THEN art.amount
                        ELSE ((inall.price * inall.qty_invoiced) / hdr.exch_rate)
                   END)), 2) AS SalesTotal,
        hdr.slsman,
        MONTH(hdr.inv_date) AS SalesMonth,
        hdr.co_num
FROM    inv_hdr hdr
        INNER JOIN inv_item_all inall ON hdr.inv_num = inall.inv_num
        LEFT OUTER JOIN artran art ON hdr.inv_num = art.inv_num AND art.type IN ('I', 'C')
        LEFT OUTER JOIN custaddr custadd ON hdr.cust_num = custadd.cust_num AND custadd.cust_seq = 0
        LEFT OUTER JOIN coitem co ON custadd.cust_num = co.cust_num
WHERE   YEAR(hdr.inv_date) = YEAR(GETDATE())
        AND hdr.slsman = 'F000001'  AND hdr.co_num = 'SFT0003575'
        AND MONTH(hdr.inv_date) = 2
        AND (SUBSTRING(co.non_inv_acct, 1, 1) = 5  OR co.non_inv_acct IS NULL)
GROUP BY 
		MONTH(hdr.inv_date),
        hdr.slsman,
        hdr.co_num

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39987739
>>" explain where iam doing mistake"
I'll try

You have 3 LEFT JOINS, but as soon as you reference those in the where clause you need to take particular care. Only for one of these [coitem] have you  correctly tested for a condition AND allowed NULLS. For the other 2 left joined tables NULLS are not permmited by the where clause (and because of this these are the equivalent of an INNER JOIN).
SELECT
      SUM(inall.price * inall.qty_invoiced)
    , ROUND(SUM((CASE WHEN inall.qty_invoiced = 0 THEN art.amount ELSE ((inall.price * inall.qty_invoiced) / hdr.exch_rate) END)), 2) AS SalesTotal
    , hdr.slsman
    , MONTH(hdr.inv_date)                                                                                                             AS SalesMonth
    , hdr.co_num
FROM inv_hdr hdr
      INNER JOIN inv_item_all inall
            ON hdr.inv_num = inall.inv_num
      LEFT OUTER JOIN artran art
            ON hdr.inv_num = art.inv_num
      LEFT OUTER JOIN custaddr custadd
            ON hdr.cust_num = custadd.cust_num
      LEFT OUTER JOIN coitem co
            ON custadd.cust_num = co.cust_num
WHERE YEAR(hdr.inv_date) = YEAR(GETDATE())
      AND (hdr.slsman = 'F000001'
      AND hdr.co_num = 'SFT0003575')
      AND (MONTH(hdr.inv_date)) = 2

-- here you have allowed for NULLS due to the LEFT JOIN
      AND (SUBSTRING(co.non_inv_acct, 1, 1) = 5 OR co.non_inv_acct IS NULL)

-- but here you have not allowed for NULLS
      AND art.type IN ('I', 'C')

-- or here
      AND custadd.cust_seq = 0


GROUP BY
      (MONTH(hdr.inv_date))
    , hdr.slsman
    , hdr.co_num

Open in new window

As suggested by Anthony above, you can either move some conditions into the joins, or you need to amend your where clause to permit non-matching records to survive.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question