First attempt at using a TSQL IF

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

General SQL Server Forums

New to SQL Server Programming

First attempt at using a TSQL IF

Author

Topic

AdenJones
Starting Member

17 Posts

Posted - 2009-05-30 : 01:11:04

Not sure where the problem is here but it is saying that there is an error at line 7 ')'. But I can't see any syntax errors

SELECT MemberID 
FROM tblReflectionNote LEFT OUTER JOIN tblMember ON tblMember.MemberID = tblReflectionNote.MemberID LEFT OUTER JOIN tblTimeUnit ON tblMember.OutreachTimeUnitID = tblTimeUnit.TimeUnitID
WHERE tblReflectionNote.Outreach = 1
IF (tblTimeUnit.TimeUnitIs = 1)
AND RecordDate > DATEADD(day, -TimeUnitValue, GETDATE())
IF (tblTimeUnit.TimeUnitIs = 2)
AND RecordDate > DATEADD(month, -TimeUnitValue, GETDATE())

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-30 : 01:54:30

IF is for code path selection, you need to use CASE in a query. I'm not 100% sure what you're trying to do but this is probably right.

SELECT MemberID 
FROM tblReflectionNote 
LEFT OUTER JOIN tblMember ON tblMember.MemberID = tblReflectionNote.MemberID 
LEFT OUTER JOIN tblTimeUnit ON tblMember.OutreachTimeUnitID = tblTimeUnit.TimeUnitID
WHERE tblReflectionNote.Outreach = 1
AND RecordDate > CASE tblTimeUnit.TimeUnitIs WHEN 1 THEN DATEADD(day, -TimeUnitValue, GETDATE())
WHEN 2 THEN DATEADD(month, -TimeUnitValue, GETDATE()) END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 02:18:59

i think it should be


SELECT MemberID 
FROM tblReflectionNote 
LEFT OUTER JOIN tblMember ON tblMember.MemberID = tblReflectionNote.MemberID 
LEFT OUTER JOIN tblTimeUnit ON tblMember.OutreachTimeUnitID = tblTimeUnit.TimeUnitID
AND RecordDate > CASE tblTimeUnit.TimeUnitIs WHEN 1 THEN DATEADD(day, (-1) * TimeUnitValue, GETDATE())
WHEN 2 THEN DATEADD(month, (-1) * TimeUnitValue, GETDATE()) END
WHERE tblReflectionNote.Outreach = 1

AdenJones
Starting Member

17 Posts

Posted - 2009-05-30 : 02:55:18

Thanks heaps for that, that was exactly what I was looking for. The first answer was the correct one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 03:13:01

quote:
Originally posted by AdenJones

Thanks heaps for that, that was exactly what I was looking for. The first answer was the correct one.

Are you sure? then why take left join with tblTimeUnit? you can just use INNER JOIN.
what you're doing is left joining with tblTimeUnit which means you need records from blReflectionNote regardless of if match exists in tblTimeUnit or not and then filtering by tblTimeUnit.TimeUnitIs field which will nullify effect of left join and returns only records with match in tblTimeUnit so that you can very well use inner join itself.

AdenJones
Starting Member

17 Posts

Posted - 2009-05-30 : 05:01:47

Yes you're right visakh inner joins would be more efficient here

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 13:48:19

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources