[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem returning ALL entries from table

Posted on 2007-10-04
8
Medium Priority
?
210 Views
Last Modified: 2010-03-20
I have the following problem when i create SQL queries and have never found out how to do this.

We have tables which hold all Inventory item details (IV00101/ IV00102) and a table which holds data for sales etc (tblKPIData). What i need to do is query these tables showing ALL inventory items whether or not they have a sale to display total sales for that item and the qty currently on hand, below is the query i come up with, but this only shows items that have had sales, i need to see all items even if they have 0 sales;

SELECT     tblKPIData.ItemNumber, VJL.dbo.IV00101.ITEMDESC, SUM(tblKPIData.CustomerPrice) AS Sales, VJL.dbo.IV00102.QTYONHND AS [Qty on Hand]
FROM         VJL.dbo.IV00101 INNER JOIN
                      VJL.dbo.IV00102 ON VJL.dbo.IV00101.ITEMNMBR = VJL.dbo.IV00102.ITEMNMBR LEFT OUTER JOIN
                      tblKPIData ON VJL.dbo.IV00102.ITEMNMBR = tblKPIData.ItemNumber
WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')
GROUP BY tblKPIData.ItemNumber, VJL.dbo.IV00101.ITEMDESC, VJL.dbo.IV00102.QTYONHND, tblKPIData.PriceGroup
ORDER BY tblKPIData.PriceGroup, tblKPIData.ItemNumber

This works, but as said above, just shows items that have sales. I need to show ALL items from IV00101 / IV00102, is this possible?
0
Comment
Question by:ale1981
8 Comments
 
LVL 19

Accepted Solution

by:
NickUpson earned 225 total points
ID: 20012921
this

WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')

needs to add become

WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102) or tblKPIData.DOCDATE is NULL) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')

as in the final results any column that would come from tblKPIData but doesn't due to there being no sales will have a value of NULL
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 75 total points
ID: 20012939
Remove this from the WHERE clause:

tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)

and put it in the ON clause instead.

BTW, you can safely remove the CONVERT function if you use the ISO standard date format for your date literals. Either include the "T" delimiter or remove all the delimiters:

tblKPIData.DOCDATE > '2007-04-03T00:00:00'

or

tblKPIData.DOCDATE > '20070403'
0
 

Author Comment

by:ale1981
ID: 20012954
Sorry, I am quite new to more complex SQL statements, well this is for me anyway, JOINS etc!

Where in the query would i put;

tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)

instead of where it is now?

Thanks, will remove the CONVERT() function ;)
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Assisted Solution

by:elec1cele
elec1cele earned 75 total points
ID: 20013264
it could either be a seperate condition in the on caluse that joins your outer table like

OUTER JOIN
                      tblKPIData ON (VJL.dbo.IV00102.ITEMNMBR = tblKPIData.ItemNumber
and   (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102)))

or you could change you where clause the way Nickupson suggested.

The reason it needs to be in the ON clause of the outer join is cause when it is in the where clause all the entries w/o sales have a NULL value there which makes the where clause return false for all those rows.  The other where clause works cause it test for the NULL case seperatly so it gets included also.
0
 

Author Comment

by:ale1981
ID: 20013901
I tried to change the WHERE clause as NickUpson suggested, but this returns all the Item Numbers and Sales SUM as NULL, this is not as i expected? Here is what i changed the query to;

SELECT     tblKPIData.ItemNumber, VJL.dbo.IV00101.ITEMDESC, SUM(tblKPIData.CustomerPrice) AS Sales, VJL.dbo.IV00102.QTYONHND AS [Qty on Hand]
FROM         VJL.dbo.IV00101 INNER JOIN
                      VJL.dbo.IV00102 ON VJL.dbo.IV00101.ITEMNMBR = VJL.dbo.IV00102.ITEMNMBR LEFT OUTER JOIN
                      tblKPIData ON VJL.dbo.IV00102.ITEMNMBR = tblKPIData.ItemNumber
WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102) OR
                      tblKPIData.DOCDATE IS NULL) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')
GROUP BY tblKPIData.ItemNumber, VJL.dbo.IV00101.ITEMDESC, VJL.dbo.IV00102.QTYONHND, tblKPIData.PriceGroup
ORDER BY tblKPIData.PriceGroup, tblKPIData.ItemNumber

0
 
LVL 19

Expert Comment

by:NickUpson
ID: 20014105
any calculation that includes a null will return a null, hence sum () ruturned null

make that part into    SUM(ifnull(tblKPIData.CustomerPrice,0.0))
0
 

Author Comment

by:ale1981
ID: 20014168
Thanks Nick,

What about the ItemNumbers? These are returning as NULL also if the Sales is NULL?
0
 

Author Comment

by:ale1981
ID: 20014846
I have adjusted the query slightly and it now works :)

SELECT     VJL.dbo.IV00101.ITEMNMBR, VJL.dbo.IV00101.ITEMDESC, SUM({ fn IFNULL(tblKPIData.CustomerPrice, 0.0) }) AS Sales,
                      VJL.dbo.IV00102.QTYONHND AS [Qty on Hand]
FROM         VJL.dbo.IV00101 INNER JOIN
                      VJL.dbo.IV00102 ON VJL.dbo.IV00101.ITEMNMBR = VJL.dbo.IV00102.ITEMNMBR LEFT OUTER JOIN
                      tblKPIData ON VJL.dbo.IV00102.ITEMNMBR = tblKPIData.ItemNumber
WHERE     (tblKPIData.DOCDATE > CONVERT(DATETIME, '2007-04-03 00:00:00', 102) OR
                      tblKPIData.DOCDATE IS NULL) AND (VJL.dbo.IV00102.LOCNCODE = 'HO')
GROUP BY VJL.dbo.IV00101.ITEMDESC, VJL.dbo.IV00102.QTYONHND, tblKPIData.PriceGroup, VJL.dbo.IV00101.ITEMNMBR
ORDER BY VJL.dbo.IV00101.ITEMNMBR, tblKPIData.PriceGroup

Thanks for your help, points will be spread over the solution i used.

Thanks again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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