?
Solved

SQL query

Posted on 2014-04-08
2
Medium Priority
?
417 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 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

Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 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