September 16, 2009 at 12:43 pm
Hi, I am trying to work with this code.
I want the value of stick_quantity, "blank" or null to change to 0.
I tried adding :
CASE
WHEN dbo.SAP_InvoiceLine_M.stick_quantity = ''
THEN '0'
END
but I am not sure where to add it in the code.
Here is the code:
SELECT
dbo.SAP_SoldToPartner.HC_CustomerAccountNumber,
dbo.SAP_SoldToPartner.HC_Name1,
dbo.Invoice_date_month.year_key,
sum(dbo.SAP_InvoiceLine_M.stick_quantity)
FROM
dbo.SAP_InvoiceLine_M,
dbo.SAP_ShipToPartner,
dbo.SAP_SoldToPartner,
dbo.Invoice_date_month
WHERE
( dbo.SAP_SoldToPartner.SoldTo_wid=dbo.SAP_ShipToPartner.SoldTo_wid )
AND ( dbo.SAP_InvoiceLine_M.ShipTo_wid=dbo.SAP_ShipToPartner.ShipTo_wid )
AND ( dbo.SAP_InvoiceLine_M.Month_Key=dbo.Invoice_date_month.month_key )
AND (
( dbo.Invoice_date_month.year_key = datepart(YYYY,getdate())-1 )
)
GROUP BY
dbo.SAP_SoldToPartner.HC_CustomerAccountNumber,
dbo.SAP_SoldToPartner.HC_Name1,
dbo.Invoice_date_month.year_key
September 16, 2009 at 1:30 pm
You would add that code inside the SUM, for example:
SUM(CASE WHEN...) AS ...
But, I would recommend using COALESCE and NULLIF instead of the CASE WHEN construct - so you would end up with:
SUM(COALESCE(NULLIF(dbo.SAP_InvoiceLine_M.stick_quantity, ''), 0)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2009 at 8:19 am
I get the following error when I tried what you suggested.
stick_quantity is data type decimal in the database.
Exception: DBD, [Microsoft OLE DB Provider for SQL Server] : Error converting data type varchar to numeric.State: 22018
Here is what I tried to do.
sum(CASE WHEN dbo.SAP_InvoiceLine_M.stick_quantity = '' THEN 0 END)
That didn't work, so I tried this and got the same error.
SUM(COALESCE(NULLIF(dbo.SAP_InvoiceLine_M.stick_quantity, ''), 0)
Thanks,
-R
September 17, 2009 at 9:03 am
I have been working on something very similar. I replaced sum with count and got closer to my result. The only problem I am having is that the ID does not return zeroes in the other columns. When I used SUM it multiplied the count by ID which gave me the incorrect results. Maybe mines will get you closer.
SELECT tlkp_Defects.Defect_Title,
Count(case When tbl_Assembly_Holds.Disposition_ID = 1 then NULL else 0 end) FixedOnLine,
Count(case When tbl_Assembly_Holds.Disposition_ID = 2 then NULL else 0 end) Repair,
Count(case When tbl_Assembly_Holds.Disposition_ID = 3 then NULL else 0 end) Scrap
FROM
tbl_Assembly_Holds
join tlkp_Disposition on tbl_Assembly_Holds.Disposition_ID = tlkp_Disposition.Disposition_ID
join tbl_Assembly_Hold_Defects on tbl_Assembly_Hold_Defects.TagNumber = tbl_Assembly_Holds.TagNumber
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
WHERE tbl_Assembly_Holds.Record_date Between '8/1/2009' And '9/11/2009' AND
tlkp_Defects.Defect_Title IN ()
GROUP BY
tlkp_Defects.Defect_Title
September 17, 2009 at 9:19 am
This code generated the zeroes but multiplied defect_ID by Disposition_ID
SELECT tlkp_Disposition.Disposition_Description, count(*) AS Disposition_Count, tlkp_Defects.Defect_Title,
SUM(case tlkp_Disposition.Disposition_ID when 1 then tlkp_Defects.Defect_ID else 0 end) FixedOnLine,
SUM(case tlkp_Disposition.Disposition_ID when 2 then tlkp_Defects.Defect_ID else 0 end) Repair,
SUM(case tlkp_Disposition.Disposition_ID when 3 then tlkp_Defects.Defect_ID else 0 end) Scrap
FROM
tbl_Assembly_Holds
join tlkp_Disposition on tbl_Assembly_Holds.Disposition_ID = tlkp_Disposition.Disposition_ID
join tbl_Assembly_Hold_Defects on tbl_Assembly_Hold_Defects.TagNumber = tbl_Assembly_Holds.TagNumber
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
WHERE
tbl_Assembly_Holds.Record_Date BETWEEN '#FORM.dateFrom#' AND '#FORM.dateTo#'
GROUP BY
tlkp_Defects.Defect_Title, tlkp_Disposition.Disposition_Description
September 17, 2009 at 9:38 am
R,
Have you tried using ISNULL? Since stick_quantity is decimal SQL Server doesn't store "blanks" but zeros and NULL is stored as NULL. So your sum column will look something like this:
SUM(ISNULL(dbo.SAP_InvoiceLine_M.stick_quantity,0))
Let me know if it's not working out for you.
-Supriya
September 17, 2009 at 1:03 pm
This worked for me. I got the results I wanted.
SELECT tlkp_Defects.Defect_Title,
SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 1 then 1 else 0 end) FixedOnLine,
SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 2 then 1 else 0 end) Repair,
SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 3 then 1 else 0 end) Scrap
FROM
tbl_Assembly_Holds
join tlkp_Disposition on tbl_Assembly_Holds.Disposition_ID = tlkp_Disposition.Disposition_ID
join tbl_Assembly_Hold_Defects on tbl_Assembly_Hold_Defects.TagNumber = tbl_Assembly_Holds.TagNumber
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
WHERE
tbl_Assembly_Holds.Record_date Between '8/1/2009' And '9/11/2009'
GROUP BY
tlkp_Defects.Defect_Title
September 17, 2009 at 1:28 pm
Supriya,
Thanks for your help. The code worked and the report ran without any errors. However, it didn't accomplish baht I wanted it to. I have a report with 12 columns, and I have applied sorts to two of those columns. Firstly, I am sorting the "Yes/No" column. All the values that are "Yes" show up first, and then I want the highest stick quantity to be displayed first. This works fine for the "Yes" part, however when it says "No" there are some columns that do not have a stick_quantity value. They're not 0 or NULL, just blank. And the report displays those first, and then the highest stick quantity all the way down to 0. What I am trying to do is change the blank entries to 0 so that they automatically go to the end of the report.
Thanks,
-R
September 17, 2009 at 2:35 pm
Can you do SELECT DISTINCT(stick_quantity) FROM dbo.SAP_InvoiceLine_M ?Do you have rows with blank stick_quantity?
September 18, 2009 at 7:15 am
SELECT
dbo.SAP_SoldToPartner.HC_CustomerAccountNumber,
dbo.SAP_SoldToPartner.HC_Name1,
dbo.Invoice_date_month.year_key,
sum(dbo.SAP_InvoiceLine_M.stick_quantity)
FROM
dbo.SAP_InvoiceLine_M,
dbo.SAP_ShipToPartner,
dbo.SAP_SoldToPartner,
dbo.Invoice_date_month
WHERE
( dbo.SAP_SoldToPartner.SoldTo_wid=dbo.SAP_ShipToPartner.SoldTo_wid )
AND ( dbo.SAP_InvoiceLine_M.ShipTo_wid=dbo.SAP_ShipToPartner.ShipTo_wid )
AND ( dbo.SAP_InvoiceLine_M.Month_Key=dbo.Invoice_date_month.month_key )
AND (
( dbo.Invoice_date_month.year_key = datepart(YYYY,getdate())-1 )
)
GROUP BY
dbo.SAP_SoldToPartner.HC_CustomerAccountNumber,
dbo.SAP_SoldToPartner.HC_Name1,
dbo.Invoice_date_month.year_key
I do have blank stick_quantiy. There is also 0 stick_quantity in the database. However for some reason, blanks are given higher value than an actual number. And I can't use DISTINCT because I want all the customer names to show up. How can I make these blanks equal to 0?
-R
September 18, 2009 at 12:02 pm
I meant just run this single query: SELECT DISTINCT(stick_quantity) FROM dbo.SAP_InvoiceLine_M.
What is the output? Also, what is the actual datatype for stick_quantity?
Actually, I created a small test case with two columns one is int and other is decimal. This is what i have:declare @a1 table (c1 int, c2 decimal (5,2))
insert into @a1 (c1,c2)
select 1,''
When I try adding blank for c2 i get this error: "Error converting data type varchar to numeric."
So it seems SQL Server won't even let you insert blanks for a decimal field. So i am just confused how come you have blanks in that field.
Also, you said you have 12 columns in the report. Your query shows only 4. Where are the other 8 columns coming from?
-Supriya
September 18, 2009 at 12:12 pm
I am still trying to grasp the idea, but from what I understand, the data is being pulled from different tables in the same database.
I am using Business Objects to create my report.
Their are joins that connect specific columns from one table to another. Some of the columns are just calculations I have made using the data being pulled from the databases and displayed in the columns.
Someone tried to explain to me that when their are JOINs, even though the data is in decimal in one table, it may be in another format in another table (confusing), the JOIN allows it to be blank. I'm still trying to figure out that means.
-R
September 18, 2009 at 2:12 pm
huh????
Guess I have a lot to learn !!! 🙂
Anyway the only way I can think of for checking blanks is by converting the field to character, check for blanks using NULLIF and then use ISNULL to set to 0 and convert back to numeric when doing SUM (got it?? :hehe: )
Have a nice wknd!!
-Supriya
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply