SQL Statement not displaying results with zero value

Question

SQL Statement not displaying results with zero value

gedhinch

Old Hand

Points: 302
More actions
April 2, 2014 at 7:23 am

#306036

Hello,
I am currently working on a project to display the pricing of our products, as mentioned above though the discount column in our query isn't displaying the rows with the value "0.00". i am a beginner at SQL server 2012 i just cannot figure out what is wrong with the query.
Use sales_book
Go
SELECT DISTINCT
GBPriceList.[Mfr_Part_Num],
[Long_ description],
[Short_ Description],
--Exhibit_Unpivoted.Exhibit,
WeightKGS,
WarrantyCode,
ProductLine,
Serialized,
ListPrice,
Prod_Class,
Exhibit_Discount.Discount AS Discount
FROM Exhibit_Unpivoted,Exhibit_Discount,GBPriceList,LongDescr,ShortDescr
WhereExhibit_Unpivoted.Exhibit = Exhibit_Discount.Exhibit
AND
GBPriceList.[Mfr_Part_Num] = Exhibit_Unpivoted.Mfr_Part_Number
AND
GBPriceList.[Mfr_Part_Num] = LongDescr.[Mfr_ Part_ Num]
AND
GBPriceList.[Mfr_Part_Num] = ShortDescr.[Mfr_ Part_ Number]
Group By GBPriceList.[Mfr_Part_Num], [Long_ description],
[Short_ Description],
WeightKGS,
WarrantyCode,
ProductLine,
Serialized,
ListPrice,
Prod_Class,
Discount
The resulting outcome is this includes 290,000 rows but there should be approximately 390,000 with the "0.00" discounts results included in the rows after checking the data myself.
Thanks for your help in advance and i hope it's solved soon as it's been driving me nuts for a while.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

samalex SSCertifiable Points: 5429 More actions · Answer 1

You may be able to force the query to return 0.00 by converting the value to numeric(9,2) (or whatever precision you need), but this probably needs to be set in the report or application that's presenting the data to the user instead of in SQL itself.

gedhinch Old Hand Points: 302 More actions · Answer 2

Thanks for your response Samalex, the application at the end is just simply a excel sheet. The problem is that no discounts are been included in the file with the value "0.00" when we have knowledge of approximately 90,000 of them.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Since you said you are new to sql I would like to offer some suggestions.

You should use ANSI-92 style joins instead of the older ANSI-89 style. It is easier to read imho and is less prone to accidental cross joins.

You should use table aliases to make your code easier to read.

You should reference the table (by alias) when referring to columns. This has two benefits. The first is that somebody else knows what table any given column belongs to. Secondly if you have the same name in more than one table you will not get an ambiguous column error.

When using a group by there is no point in also using distinct. Pick one or the other (which ever is appropriate) but not both.

Last but not least you should make your code uniform with formatting so it is easy to read. This is largely a personal preference about what is best.

If at all possible avoid putting spaces in your object names (tables, columns, etc). It is a lot easier to work with if you don't have to wrap everything in []. 😉

Here is your query with aliases and ANSI-92 style joins. I was unable to add aliases to all the columns because I have no idea what table all these belong to.

SELECT

gb.[Mfr_Part_Num],

ld.[Long_ description],

sd.[Short_ Description],

--Exhibit_Unpivoted.Exhibit,

WeightKGS,

WarrantyCode,

ProductLine,

Serialized,

ListPrice,

Prod_Class,

d.Discount

FROM Exhibit_Unpivoted u

inner join Exhibit_Discount d on u.Exhibit = d.Exhibit

inner join GBPriceList pl on pl.Mfr_Part_Num = u.Mfr_Part_Number

inner join LongDescr ld on ld.[Mfr_ Part_ Num] = pl.Mfr_Part_Num

inner join ShortDescr sd on sd.[Mfr_ Part_ Number] = pl.Mfr_Part_Num

Group By pl.[Mfr_Part_Num],

ld.[Long_ description],

sd.[Short_ Description],

WeightKGS,

WarrantyCode,

ProductLine,

Serialized,

ListPrice,

Prod_Class,

Discount

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gedhinch Old Hand Points: 302 More actions · Answer 4

Thanks for your help Sean,

I'll give you remainder of the tables for the variables,

weightkgs, warrantycode, product line, serialized, list price, prod class all come from the gbpricelist table where as the discount comes from the exhibit discount.

I didn't originally write the database myself although I've played a big part trying to correct these sorts of things in it ( the spaces with [ ]). Never the less i'm still very new to it and it's good to see yours and how i can further improve upon my own queries and designs. I'll also include aliases in my future posts 😀

The reason for using the distinct was that we were getting duplicate keys on the mfr_part_nums but i didn't actually know that effected the group by, we thought that was just simply for sorting the data into the table.

i hope you wouldn't mind helping me finish the remainder of the query with the rest of the information you needed.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

gedhinch (4/2/2014)
Thanks for your help Sean,
I'll give you remainder of the tables for the variables,
weightkgs, warrantycode, product line, serialized, list price, prod class all come from the gbpricelist table where as the discount comes from the exhibit discount.
I didn't originally write the database myself although I've played a big part trying to correct these sorts of things in it ( the spaces with [ ]). Never the less i'm still very new to it and it's good to see yours and how i can further improve upon my own queries and designs. I'll also include aliases in my future posts 😀
The reason for using the distinct was that we were getting duplicate keys on the mfr_part_nums but i didn't actually know that effected the group by, we thought that was just simply for sorting the data into the table.
i hope you wouldn't mind helping me finish the remainder of the query with the rest of the information you needed.

Don't tell Sean which columns belong to which tables, fix and repost the code yourself. We are volunteers giving of our own time and effort to try and help people who are having problems like yourself. You need to provide us with as much information as possible so that we can try and help.

In this case, there really isn't much we can do since we can't see what you see, nor are we going to spend our time trying to recreate your environment on our own to try and help you. To help us help you please read the first article I reference below in my signature block regarding asking for help. It will walk you through what you need to post and how to p0ost it to get the best possible responses to your question and get tested code in return.

gedhinch Old Hand Points: 302 More actions · Answer 6

I've read the article and it now is a lot clearer on how to post on this website, i'll have ago at re-writing the code myself and see what results i get before posting back. I hope i didn't get anybodies back up by that and I'll take steps to correct it, thanks again.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

1) The older style joins are not just worse syntactically. They have been deprecated and at some point in the future will NOT WORK any longer. Some of them already fail, such as *= and =*

2) to your original "bad data" question, can you please provide us with sample tables and data that show the effect you are speaking of? I can't see anything in your query that would lead to that so the most likely case is your query is filtering them out for some reason but that takes actual data for us to determine why.

3) Try your query with another WHERE clause added in that specifically SHOULD return rows with the 0.00 you are expecting as well as rows where 0.00 is NOT in play. Now you might be able to determine how they are getting filtered out. Also consider removing the group by and just returning all rows.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

TheSQLGuru (4/2/2014)
1) The older style joins are not just worse syntactically. They have been deprecated and at some point in the future will NOT WORK any longer. Some of them already fail, such as *= and =*

I knew that *= and *= styles have been deprecated but the other one I had no idea. I assume that what must actually be deprecated then is the cross join using two tables names?

from table1, table2

I have not been able to find anything about that anywhere in the documentation. Any chance you have a link to that info?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

http://msdn.microsoft.com/en-us/library/dd172122(v=vs.100).aspx

I don't see it listed here though, just the outer joins: http://technet.microsoft.com/en-us/library/bb510662.aspx

or here (although there are some rather surprising things supposedly being REMOVED in SQL 2014 and future editions!): http://technet.microsoft.com/en-us/library/ms143729.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

TheSQLGuru (4/2/2014)
http://msdn.microsoft.com/en-us/library/dd172122(v=vs.100).aspx
I don't see it listed here though, just the outer joins: http://technet.microsoft.com/en-us/library/bb510662.aspx
or here (although there are some rather surprising things supposedly being REMOVED in SQL 2014 and future editions!): http://technet.microsoft.com/en-us/library/ms143729.aspx

Let's make the links clickable:

http://msdn.microsoft.com/en-us/library/dd172122(v=vs.100).aspx

http://technet.microsoft.com/en-us/library/bb510662.aspx

http://technet.microsoft.com/en-us/library/ms143729.aspx

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 11

TheSQLGuru (4/2/2014)
http://msdn.microsoft.com/en-us/library/dd172122(v=vs.100).aspx
I don't see it listed here though, just the outer joins: http://technet.microsoft.com/en-us/library/bb510662.aspx
or here (although there are some rather surprising things supposedly being REMOVED in SQL 2014 and future editions!): http://technet.microsoft.com/en-us/library/ms143729.aspx

I think it might be impossible for them to get rid of all the old style joins because it would make correlated sub-queries impossible if they did. It would also take out a lot of APPLY functionality, which is really just another form of correlated sub-query in many cases.

I do wish that MS would get off the deprecation kick and concentrate more on fixing things like the awful mess that DBCC SHRINKFILE makes and a thousand other shortcomings.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 12

It occurs to me that there may not be an Exhibit_Discount record for products where the discount is zero. If that's the case, you'll need a left outer join to the Exhibit_Discount table instead of an inner join and you'll need to select IsNull(Exhibit_Discount.Discount, 0) as the discount value.

gedhinch Old Hand Points: 302 More actions · Answer 13

Hi again,

I'm going to the get the create statements and some sample data today for you guys to if you'd like to test it and further help me with my problem.

Thanks for your help so far, the comments have been really useful especially regarding the use of joins in our query, the simple reason why we didn't use them is that we didn't really understand them but that has become some what clearer now.

gedhinch Old Hand Points: 302 More actions · Answer 14

I've spent quite a bit of time making these queries for you to re-create our current database yourselves. You'll be able to see the entire makeup of it and i presume there is already a few fundamental flaws which are stopping us getting the results we want, anyway for the tables.

First is the Exhibit discount

Use sales_builder

go

----------Creating table

Create table Exhibit.discount

(

Exhibit Nvarchar(255) Null ,

Disti Nvarchar(255) Null,

Date_in nvarchar(255) Null,

Date_out nvarchar(255) null,

Discount Float Null,

)

----------Data to insert into Exhibit Discount

Insert Into Exhibit_Discount

(Exhibit, Disti,[Date_in], [Date_out],Discount)

select'1D069D',' DIST/DAR_org',' #2011-05-01#',' #2049-12-31#','0' UNION ALL

select'RD206D',' DIST/DAR_org',' #2013-05-01#',' #2049-12-31#','0' UNION ALL

select'1D110D',' DIST/DAR_org',' #2011-05-01#',' #2049-12-31#','0' UNION ALL

select'RD502D',' DIST/DAR_org',' #2012-02-09#',' #2049-12-31#','0.15' UNION ALL

select'RD133D',' DIST/DAR_org',' #2011-10-15#',' #2049-12-31#','0.3' UNION ALL

select'RD250D',' DIST/DAR_org',' #2011-05-01#',' #2049-12-31#','0.17' UNION ALL

select'4D043D',' DIST/DAR_org',' #2011-05-01#',' #2049-12-31#','0' UNION ALL

select'RD206D',' DIST/DAR_org',' #2013-05-01#',' #2049-12-31#','0' UNION ALL

select'1D047D',' DIST/DAR_org',' #2011-05-01#',' #2049-12-31#','0' UNION ALL

select'RD200D',' DIST/DAR_org',' #2011-05-01#',' #2049-12-31#','0' UNION ALL

select'RD250D',' DIST/DAR_org',' #2011-05-01#',' #2049-12-31#','0.2' UNION ALL

select'1D017D',' DIST/DAR_org',' #2011-05-01#',' #2049-12-31#','0.1' UNION ALL

select'4D062D',' DIST/DAR_org',' #2013-12-06#',' #2049-12-31#','0.38' UNION ALL

select'RD109D',' DIST/DAR_org',' #2012-11-01#',' #2049-12-31#','0.26' UNION ALL

select'6D013D',' DIST/DAR_org',' #2013-10-28#',' #2014-02-04#','0' UNION ALL

2nd Exhibit Discount (the notes at the bottom are important)

use sales_builder

go

Create table Exhibit_unpivoted

(

Mfr_Part_number Nvarchar (max) not null,

exhibit Nvarchar (max) not null

);

----Note

--Use Sales_Builder

--GO

--DECLARE @colsUnpivot AS NVARCHAR(MAX),

-- @query AS NVARCHAR(MAX)

--select @colsUnpivot

-- = stuff((select ','+quotename(C.column_name)

-- from information_schema.columns as C

-- where C.table_name = 'ProductExhibit' and

-- C.column_name like 'Exhibit%'

-- for xml path('')), 1, 1, '')

--set @query

-- = 'select Mfr_Part_Number,

-- --indicatorname

-- indicatorvalue

-- from ProductExhibit

-- unpivot

-- (indicatorvalue

-- for indicatorname in ('+ @colsunpivot +')

-- ) u'

-- insert into Exhibit_Unpivoted (Mfr_Part_Number, Exhibit)

--exec sp_executesql @query;

after all the importing has been completed we use this statement to populate our exhibit_unpivoted table.

After this we then run the final query as shown in the first post.

Next the GBpriceList

USE Sales_Builder

go

-------Creating the table

create table gbpricelist

(

Mfr_Part_Num Nvarchar (255) Null,

WarrantyCode Nvarchar (255) Null,

Productline Nvarchar (255) Null,

Serialized Nvarchar (255) Null,

Prod_Class Nvarchar(255) Null,

WeightKGS Float Null,

Listprice float Null

)

--------Data to insert into gbpricelist

Insert into gbpricelist

(Mfr_Part_Num, WarrantyCode,ProductLine,Serialized,Prod_Class,WeightKGS,ListPrice)

select'0MCCKJONES','3C','87','No','SW''0','0', UNION ALL

select'103541-01','50','LI','No','HW''0.205931','10', UNION ALL

select'103541-02','50','LI','No','HW''0.141521','10', UNION ALL

select'103541-03','50','LI','No','HW''0.205931','11', UNION ALL

select'103541-46','NA','LI','No','HW''0.411408','10', UNION ALL

select'103541-48','50','LI','No','HW''2.88031','10', UNION ALL

select'103541-51','50','LI','No','HW''2.88031','10', UNION ALL

select'103541-AR','50','LI','No','HW''0.141521','10', UNION ALL

select'103541-BT','50','LI','No','HW''0.205931','10', UNION ALL

select'110818-60','NA','R8','No','ES''0.205931','263', UNION ALL

Next the long Description

use sales_builder

go

create table longDescr

(

Mfr_Part_Num nvarchar(255) Null,

Long_Description Nvarchar (255) null,

);

insert into LongDescr

([Mfr_part_num], [Long_ Description])

select'VA206AV',' Compaq Z200 CMT Rfrbd Workstation Base Unit', UNION ALL

select'VA213AA',' Remarketed t5540 CE 1GHZ 128F/512R VS Thin Client', UNION ALL

select'VA568A','China Regulatory CCC Compliance Mark[\10]Supported on Z400.', UNION ALL

select'VA571A','China Regulatory CCC Compliance Mark', UNION ALL

select'VA572A',' China Regulatory CCC Compliance Mark[\10]Supported on Z800.', UNION ALL

select'VE053A',' DVI to VGA Adapter', UNION ALL

select'VE053AA',' Rfrbd DVI to VGA Adapter', UNION ALL

select'VEI149X','SANPoint FoundtnSteHA,UX,v3.5,LTU[\10]VERITAS SANPoint Foundation Suite HA,-[\10]UX,v3.5,License', UNION ALL

select'VEI149XA#001','SANPoint FoundtnSteHA,UX,v3.5 LTU T1A[\10]VERITAS SANPoint Foundation Suite HA,-[\10]UX,v3.5,License Tier 1A', UNION ALL

select'VEI149XA#002','SANPoint FoundtnSteHA,UX,v3.5 LTU T1B[\10]VERITAS SANPoint Foundation Suite HA,-[\10]UX,v3.5,License Tier 1B', UNION ALL

Next the product Exhibit

Use Sales_Builder

go

Create table ProductExhibit

(

Mfr_part_Number nvarchar(255) NULL,

Exhibit1 nvarchar(255) NULL,

Exhibit2 nvarchar(255) NULL,

Exhibit3 nvarchar(255) NULL,

Exhibit4 nvarchar(255) NULL,

Exhibit5 nvarchar(255) NULL,

Exhibit6 nvarchar(255) NULL,

Exhibit7 nvarchar(255) NULL,

Exhibit8 nvarchar(255) NULL,

Exhibit9 nvarchar(255) NULL,

Exhibit10 nvarchar(255) NULL,

)

Insert into Productexhibit

([Mfr_Part_Number],exhibit1, exhibit2,exhibit3.exhibit4,exhibit5,exhibit6,exhibit7,exhibit8,exhibit9,exhibit10)

select'HA106A4#9J','4D004A2','4D004D2','4D004J2','4D004W2','4D047W2''4DD18R1','4DD36R2','4E036E2','4ED36E2','4I036I2', UNION ALL

select'HA106A4#9J','4D004A3','4D004D3','4D004J3','4D004W3','4D047W2''4DD18R1','4DD36R4','4E036E4','4ED36E4','4I036I4', UNION ALL

select'HA106A4#9J','4D004A2','4D004D2','4D004J2','4D004W2','4D047W2''4DD18R1','4DD36R2','4E036E2','4ED36E2','4I036I2', UNION ALL

select'HA106A4#9J','4D004A3','4D004D3','4D004J3','4D004W3','4D047W2''4DD18R1','4DD36R4','4E036E4','4ED36E4','4I036I4', UNION ALL

select'HA106A4#9K','6A003A2','6A003D2','6A003J2','6A003W2','6D002A2''6D002D2','6D002J2','6D002W2','6I002I2','6T003O2', UNION ALL

and finally the ShortDescr

--Creating the table

CREATE TABLE ShortDescr

(

Mfr_ Part_Number nvarchar(255) NULL,

_ Short_Description nvarchar(255) NULL

)

--data to insert into the table

Insert into ShortDescr

([Mfr_ Part_ Number],[Short_ Description])

select'0MCCKJONE',' Limited Term 1 mo test product LTU', UNION ALL

select'100613-00','CORD PWR AC LINE UK/SINGA', UNION ALL

select'100614-00','CORD PWR AC LINE EUR 12', UNION ALL

select'100661-00','CORD PWR 12 ACLINE AUS/N.ZEAL', UNION ALL

select'103541-00','PWR CORD AC LINE US', UNION ALL

select'103541-01','PWR CORD AC LINE AUST', UNION ALL

select'103541-02','PWR CORD AC LINE EURO', UNION ALL

select'103541-03','PWR CORD AC LINE UK', UNION ALL

select'103541-46','PWR CORD AC LINE ITL', UNION ALL

select'103541-48','PWR CORD AC LINE DEN', UNION ALL

The final stage then is just simply running this statement ( the one from the first post) in order to obtain and combine the results together from which we then can see which discounts we have against which product for example 0.12 =12% . This is why it's so important we can view which products we currently have 0.00 discount against.

SELECT

gb.[Mfr_Part_Num],

ld.[Long_ description],

sd.[Short_ Description],

--Exhibit_Unpivoted.Exhibit,

WeightKGS,

WarrantyCode,

ProductLine,

Serialized,

ListPrice,

Prod_Class,

d.Discount

FROM Exhibit_Unpivoted u

inner join Exhibit_Discount d on u.Exhibit = d.Exhibit

inner join GBPriceList pl on pl.Mfr_Part_Num = u.Mfr_Part_Number

inner join LongDescr ld on ld.[Mfr_ Part_ Num] = pl.Mfr_Part_Num

inner join ShortDescr sd on sd.[Mfr_ Part_ Number] = pl.Mfr_Part_Num

Group By pl.[Mfr_Part_Num],

ld.[Long_ description],

sd.[Short_ Description],

WeightKGS,

WarrantyCode,

ProductLine,

Serialized,

ListPrice,

Prod_Class,

Discount

I tried using this code but the mfr_part_number didn't work i am not entirely sure how the formatting of this code works as this isn't something i've personally done before.I spent a lot of time gathering this information as it wasn't readily available to me, the way the database was set up was just by my colleague importing data and not actually writing create statements which I've now done. I've tried my best with this and i hope this is good enough for some meaningful feedback on how to improve our database.

Thank you all for your help so far! 😀