Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query syntax wrong somewhere???

Author  Topic 

Really Obfuscated
Starting Member

6 Posts

Posted - 2015-03-25 : 07:32:14
Here is my query...


SELECT O.Product
, O.Grade
, O.ProdWkYr
, O.CustNo
, O.OrdLength
, O.PcsOrdered - isnull(R.AppPieces, 0) as PcsOrdered
, R.AppPieces
, O.SpecialReq
, O.PatternCut
, (O.PatternCut + O.FinishCut) - (O.PcsOrdered - isnull(R.AppPieces, 0)) As PcsDiff
, O.FinishCut
, O.SpecialOrder
, O.CharpyTest
, isnull((

case when R.AppPieces > 0 then
case when R.CharpyTest = 'N' then
case when O.OrdLength = R.AppLength then
O.PcsOrdered - R.AppPieces
else null end
else null end
else null end

),0) AS DS

From NYS2ProductOrder O Left Join (NYS2ProductReApp R Inner Join cmnFullSpecs F on R.Grade = F.GradeCode) on O.Product = R.Product and
O.Grade = F.GradeName And O.ProdWkYr = R.ProdWkYr And O.CustNo = R.CustNo and O.OrdLength = R.AppLength

Where O.Product = 'W18X130' And O.Grade = 'A992' AND O.ProdWkYr = 915 And (O.PcsOrdered - isnull(R.AppPieces, 0)) > 0

ORDER BY o.SpecialReq, O.OrdLength, O.CustNo DESC



Within these tables,
R.AppPieces = 2
R.CharpyTest = "N"
Lengths match...etc.

The column "DS" should come up with 2, but I am getting 0 with everything I've tried.
Any notions???

Thanks,

Obfuscated beyond repair.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-25 : 07:54:08
Not sure this will fix it, but it's simpler and equivalent:
isnull(case 
when R.AppPieces > 0 and R.CharpyTest = 'N' and O.OrdLength = R.AppLength
then O.PcsOrdered - R.AppPieces
else null end,0) AS DS
Go to Top of Page

Really Obfuscated
Starting Member

6 Posts

Posted - 2015-03-25 : 08:00:47
No, still zero. I put mine like that so I could "comment" out a line and test line by line to see where I might find the error. But you're correct about being equivalent. Thanks

Obfuscated beyond repair.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-25 : 09:48:14
If either O.PcsOrdered or R.AppPieces is null you'll get null for the entire expression, unless you wrap each of them with an ISNULL(,0).
Go to Top of Page

Really Obfuscated
Starting Member

6 Posts

Posted - 2015-03-26 : 05:59:37
Still zero... ??? !!! Brains are scrambled eggs.
Usually, a fresh set of eyes can spot your mistake pretty quickly, guess I'll try another angle at it.
Thanks,

Obfuscated beyond repair.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-27 : 09:11:17
Kinda hard to troubleshoot with full table schema (DDL) and some test data. Can you post those? Just enough test data to demonstrate the the problem. Also include the expected results.

Other than that, I'm assuming your LEFT JOIN or WHERE criteria are not actually getting the rows you expect. What happens if you remove "And (O.PcsOrdered - isnull(R.AppPieces, 0)) > 0" from the WHERE clause?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-27 : 15:59:20
R.CharpyTest = "N"

Trailing space, or some "invisible" character in the column which is preventing a match perhaps?

SELECT DATALENGTH(R.CharpyTest), ']' + R.CharpyTest + '['
FROM MyTable
WHERE R.CharpyTest LIKE "%N%" AND R.CharpyTest <> "N"

to check for that perhaps?
Go to Top of Page
   

- Advertisement -