[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

SQL query

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
ukerandi
Asked:
ukerandi
2 Solutions
 
Anthony PerkinsCommented:
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
 
PortletPaulCommented:
>>" 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now