Solved

SQL query

Posted on 2014-04-08
2
411 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

22 Experts available now in Live!

Get 1:1 Help Now