February 17, 2014 at 11:32 pm
I inherited the following query from a previous application. I'm having a hard time understanding the "Case" in the "Select" and "Where" clause also.
SELECT J1.AC_CODE, J1.PERIOD, J1.JRNAL_NO, J1.DESCRIPTN, - J1.AMOUNT ,
J1.ANAL_T3,
CASE 1
WHEN 1 THEN 'A'
ELSE J1.ACCNT_CODE
END ,
J1.JRNAL_LINE
FROM dbo.JSource J1
WHERE 1=1
AND 1=1
AND NOT ('A' LIKE '%Z%'
AND J1.JRNAL_SRCE IN ('B/F',
'CLRDN')
AND J1.JRNAL_NO = 0)
AND CASE 1
WHEN 1 THEN 'A'
ELSE J1.AC_CODE
END ='A'
AND J1.AC_CODE='156320'
AND J1.PERIOD BETWEEN 2014001 AND 2014012
AND J1.ANAL_T3='ANAL001'
ORDER BY 1,2,3,4,5,6,7,8
I'm not sure If I understand the following clauses correctly:
[h3]1st Clause:[/h3]
CASE 1
WHEN 1 THEN 'A'
ELSE J1.AC_CODE
END
I understood as: If column 1 is true, then choose literal A ortherwise choose J1.AC_CODE.
[h3]2nd clause:[/h3]
WHERE 1=1
AND 1=1
AND NOT ('A' LIKE '%Z%'
AND J1.JRNAL_SRCE IN ('B/F',
'CLRDN')
AND J1.JRNAL_NO = 0)
AND CASE 1
WHEN 1 THEN 'A'
ELSE J1.AC_CODE
END ='A'
I'm totally lost with this "Where" clause.
Can you help explain this query and write a better version for this whole query?
February 17, 2014 at 11:51 pm
[h3]1st Clause:[/h3]
CASE 1
WHEN 1 THEN 'A'
ELSE J1.AC_CODE
END
This code will always return 'A' , query can be write above syntax like this
CASE when 1= 1 then 'A' else J1.AC_CODE END
[h3]2nd clause:[/h3]
WHERE 1=1
AND 1=1
AND NOT ('A' LIKE '%Z%'
AND J1.JRNAL_SRCE IN ('B/F',
'CLRDN')
AND J1.JRNAL_NO = 0)
AND CASE 1
WHEN 1 THEN 'A'
ELSE J1.AC_CODE
END ='A'
its always helps when you bring the where clause in the select clause to understand its working ,
'A' is will always contain the value 'A', so the NOT section will not put any harm on the result set because the like query will not show anything at all.
and the 2nd AND is as same above case which is present in the select.
these kind of queries usually used by ORM, are you using NHIBERNATE ? because then you define filters on column it tends to make query like that, so whenever, the filter related column have any value 1=1 usually become 1=0 / 0=1 .
February 18, 2014 at 12:37 am
Thank you for replying.
I'm trying to simplify the query so it's more easier to read. This is my simplified version so far:
SELECT J1.AC_CODE,
J1.PERIOD,
J1.JRNAL_NO,
J1.DESCRIPTN,
- J1.AMOUNT AS AMOUNT ,
J1.ANAL_T3,
J1.ACCNT_CODE,
J1.JRNAL_LINE
FROM dbo.JSource J1
WHERE NOT ( J1.JRNAL_SRCE IN ('B/F','CLRDN') AND J1.JRNAL_NO = 0)
AND J1.AC_CODE='156320'
AND J1.PERIOD BETWEEN 2014001 AND 2014012
AND J1.ANAL_T3='ANAL001'
ORDER BY 1,2,3,4,5,6,7,8
I removed WHERE 1=1 AND 1=1 because all the parameters will be provided so WHERE 1=1 will be ignored by the optimizer (see explanation here)
I captured the queries with SQL Server Profiler (template: TSQL). The application is an accounting system. I'm not sure which ORM it's using because I don't have control over it.
February 18, 2014 at 12:57 am
WHERE 1=1
AND 1=1
AND NOT ('A' LIKE '%Z%'
AND J1.JRNAL_SRCE IN ('B/F',
'CLRDN')
AND J1.JRNAL_NO = 0)
AND CASE 1
WHEN 1 THEN 'A'
ELSE J1.AC_CODE
END ='A'
you have missed column 'J1.JRNAL_NO' which SHOULD BE inside NOT Clause like this
SELECT J1.AC_CODE,
J1.PERIOD,
J1.JRNAL_NO,
J1.DESCRIPTN,
- J1.AMOUNT AS AMOUNT ,
J1.ANAL_T3,
J1.ACCNT_CODE,
J1.JRNAL_LINE
FROM dbo.JSource J1
WHERE NOT (J1.JRNAL_SRCE IN ('B/F',
'CLRDN')
AND J1.JRNAL_NO = 0)
AND J1.AC_CODE='156320'
AND J1.PERIOD BETWEEN 2014001 AND 2014012
AND J1.ANAL_T3='ANAL001'
ORDER BY 1,2,3,4,5,6,7,8
February 19, 2014 at 8:37 am
twin.devil (2/18/2014)
WHERE 1=1
AND 1=1
AND NOT ('A' LIKE '%Z%'
AND J1.JRNAL_SRCE IN ('B/F', 'CLRDN')
AND J1.JRNAL_NO = 0)
AND CASE 1
WHEN 1 THEN 'A'
ELSE J1.AC_CODE
END ='A'
you have missed column 'J1.JRNAL_NO' which SHOULD BE inside NOT Clause like this
SELECT J1.AC_CODE,
J1.PERIOD,
J1.JRNAL_NO,
J1.DESCRIPTN,
- J1.AMOUNT AS AMOUNT ,
J1.ANAL_T3,
J1.ACCNT_CODE,
J1.JRNAL_LINE
FROM dbo.JSource J1
WHERE NOT (J1.JRNAL_SRCE IN ('B/F',
'CLRDN')
AND J1.JRNAL_NO = 0)
AND J1.AC_CODE='156320'
AND J1.PERIOD BETWEEN 2014001 AND 2014012
AND J1.ANAL_T3='ANAL001'
ORDER BY 1,2,3,4,5,6,7,8
You are missing that 'A' LIKE '%Z%' will always be false so the clause
('A' LIKE '%Z%'
AND J1.JRNAL_SRCE IN ('B/F',
'CLRDN')
AND J1.JRNAL_NO = 0)
will be false.
Assuming neither J1.JRNAL_SRCE nor J1.JRNAL_NO is NULL.
the whole WHERE clause simplifies to
WHERE
J1.AC_CODE='156320'
AND J1.PERIOD BETWEEN 2014001 AND 2014012
AND J1.ANAL_T3='ANAL001'
February 20, 2014 at 2:07 am
Yes, your explanation is correct. But I keep this clause:
NOT ( J1.JRNAL_SRCE IN ('B/F','CLRDN') AND J1.JRNAL_NO = 0)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply