January 26, 2017 at 11:19 am
In t-sql 2012, each of the sqls listed separately below work fine. However I
would like to be able to have t-sql where the c1.value either='N' or c1.value either<>'N' is
more obvious. I would like for that to be a distinguishing part of the sql using an if else,
case, and or whatever you think would work.
referenced sql:
IF
(Select c2.personID, c2.enrollmentID
,attributeID=3371
,c2.value,c2.date,c2.customGUID,c2.districtID
from O.dbo.Cust C1
JOIN O.dbo.Cust c2
on c2.personID=c1.personID
and c1.date=c2.date
and C2.attributeID= 1452
where C1.attributeID = 997 and c1.value ='N') ELSE IF
(SELECT
c2.personID,c2.enrollmentID
,attributeID=3370
,c2.value,c2.date,c2.customGUID,c2.districtID
from O.dbo.Cust C1
JOIN O.dbo.Cust c2
on c2.personID=c1.personID
and c1.date=c2.date
and C2.attributeID= 1452
where C1.attributeID = 997 and c1.value <> 'N')
Thus would you modify the t-sql, I listed above to accomplish my goal?
January 26, 2017 at 11:34 am
should be able to do something like this:
SELECT C2.personID,C2.enrollmentID
,attributeID = CASE WHEN C1.value = 'N' THEN 3371 ELSE 3370 END
,C2.value, C2.[date], C2.customGUID, C2.districtID
FROM O.dbo.Cust C1
JOIN O.dbo.Cust c2
ON C2.personID = C1.personID
AND C1.date = C2.date
AND C2.attributeID = 1452
WHERE C1.attributeID = 997
January 26, 2017 at 11:38 am
dianerstein 8713 - Thursday, January 26, 2017 11:19 AMIn t-sql 2012, each of the sqls listed separately below work fine. However I
would like to be able to have t-sql where the c1.value either='N' or c1.value either<>'N' is
more obvious. I would like for that to be a distinguishing part of the sql using an if else,
case, and or whatever you think would work.
referenced sql:IF
(Select c2.personID, c2.enrollmentID
,attributeID=3371
,c2.value,c2.date,c2.customGUID,c2.districtID
from O.dbo.Cust C1
JOIN O.dbo.Cust c2
on c2.personID=c1.personID
and c1.date=c2.date
and C2.attributeID= 1452
where C1.attributeID = 997 and c1.value ='N') ELSE IF
(SELECT
c2.personID,c2.enrollmentID
,attributeID=3370
,c2.value,c2.date,c2.customGUID,c2.districtID
from O.dbo.Cust C1
JOIN O.dbo.Cust c2
on c2.personID=c1.personID
and c1.date=c2.date
and C2.attributeID= 1452
where C1.attributeID = 997 and c1.value <> 'N')Thus would you modify the t-sql, I listed above to accomplish my goal?
First, SQL stands for Structured Query Language. You don't have individual languages here, you have individual queries.
Second, IF requires a Boolean condition. You have supplied queries rather than Boolean conditions, so this code cannot possibly work.
I suspect what you want is the CASE expression.
SELECT
c2.personID
, c2.enrollmentID
, attributeID= CASE WHEN C1.value = 'N' THEN 3371 ELSE 3370 END
, c2.value
, c2.date
, c2.customGUID
, c2.districtID
FROM O.dbo.Cust C1
JOIN O.dbo.Cust c2
ON c2.personID=C1.personID
AND C1.date=c2.date
AND c2.attributeID= 1452
WHERE C1.attributeID = 997
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply