Query syntax wrong somewhere??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

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.

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).

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.

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?

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources