Solved

SQL query

Posted on 2014-04-08
2
409 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
Comment Utility
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
Comment Utility
>>" 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now