June 2, 2010 at 9:43 am
when I run this sql statement, I get the error
incorrect syntax error near when on line 5
what is wrong?
thanks
select p.idpeople,
case
p.cregtype
when 'ATT' then
when es.ideventcode = 'FULL' then
when p.cmemebertype in ('1', '2', '3', '4') then
'GOVERNMENT/MILITARY'
when p. cmemebertype in ('5', '6') then
'GOVERNMENT CONTRACTOR'
when p. cmemebertype in ('7', '8') then
'INDUSTRY'
when 'EXO' then
when p. cmemebertype in ('1', '2', '3', '4') then
'EXHIBITOR ONLY'
when p.cregtype = 'PRS' then
'PRESS'
when p.cregtype = 'SPK' then
'SPEAKER'
when p.cregtype = 'STF' then
'STAFF'
when p. cmemebertype in ('44', '45') then
'STAFF'
end
as footbadge
from tblpeople p inner join tblpeopleevents pe on p.idpeople = pe.idpeople
inner join tbleventsessions es on pe.ideventcode = es.ideventcode
June 2, 2010 at 9:52 am
--u need something in every then statement and your formatting was jacked up a bit.
SELECT p.idpeople,
CASE
WHEN p.cregtype = 'ATT' THEN ''
WHEN es.ideventcode = 'FULL' THEN ''
WHEN p.cmemebertype IN ( '1', '2', '3', '4' ) THEN 'GOVERNMENT/MILITARY'
WHEN p. cmemebertype IN ( '5', '6' ) THEN 'GOVERNMENT CONTRACTOR'
WHEN p. cmemebertype IN ( '7', '8' ) THEN 'INDUSTRY'
WHEN p.cregtype = 'EXO' THEN ''--missing the then vlause here
WHEN p. cmemebertype IN ( '1', '2', '3', '4' ) THEN 'EXHIBITOR ONLY'
WHEN p.cregtype = 'PRS' THEN 'PRESS'
WHEN p.cregtype = 'SPK' THEN 'SPEAKER'
WHEN p.cregtype = 'STF' THEN 'STAFF'
WHEN p. cmemebertype IN ( '44', '45' ) THEN 'STAFF'
ELSE ''
END AS footbadge
June 2, 2010 at 9:58 am
thank you so much. It worked.
June 2, 2010 at 10:58 am
not sure if it returns what u want. i added a bunch or empty strings in the areas when you had no "THEN" value. therefore if either of the following hold true you will return an emapty string.
p.cregtype = 'ATT'
or es.ideventcode = 'FULL'
or .cregtype = 'EXO'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply