[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Incorrect syntax near the keyword 'As'

childofthe1980s

3/18/2007 7:17:00 PM

Hello:

I'm trying to complete an If, Then statement within a script that I put
together, and I keep getting an "Incorrect syntax near the keyword 'As'"
error toward the end of the statement.

The whole statement says that if you are taking the payroll codes from the
benefit table, then provide the payroll code's description such "health
insurance" or "dental insurance".

The very beginning of my clause, of course, says "SELECT ... CASE
UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
ELSE 0 END As [Payroll Description]

There must be something wrong with the ELSE portion. I tried to fix it, by
saying ELSE '' END As [Payroll Description]. But, I got the same error.

What am I doing wrong, and how can I fix this?

Thanks!

childofthe1980s
11 Answers

Sylvain Lafontaine

3/18/2007 7:23:00 PM

0

It's because 0 is not a character string. Replace it with '0'.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
> Hello:
>
> I'm trying to complete an If, Then statement within a script that I put
> together, and I keep getting an "Incorrect syntax near the keyword 'As'"
> error toward the end of the statement.
>
> The whole statement says that if you are taking the payroll codes from the
> benefit table, then provide the payroll code's description such "health
> insurance" or "dental insurance".
>
> The very beginning of my clause, of course, says "SELECT ... CASE
> UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
> WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
> WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
> WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
> WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
> ELSE 0 END As [Payroll Description]
>
> There must be something wrong with the ELSE portion. I tried to fix it,
> by
> saying ELSE '' END As [Payroll Description]. But, I got the same error.
>
> What am I doing wrong, and how can I fix this?
>
> Thanks!
>
> childofthe1980s


Tom Moreau

3/18/2007 7:24:00 PM

0

I'm assuming that the datatype of the UPR40800.DSCRIPTN column is varchar or
something similar, yet the ELSE is 0 - i.e. an int. Perhaps you should
change it to '0' or 'Not found' or something else that's more appropriate.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
Hello:

I'm trying to complete an If, Then statement within a script that I put
together, and I keep getting an "Incorrect syntax near the keyword 'As'"
error toward the end of the statement.

The whole statement says that if you are taking the payroll codes from the
benefit table, then provide the payroll code's description such "health
insurance" or "dental insurance".

The very beginning of my clause, of course, says "SELECT ... CASE
UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
ELSE 0 END As [Payroll Description]

There must be something wrong with the ELSE portion. I tried to fix it, by
saying ELSE '' END As [Payroll Description]. But, I got the same error.

What am I doing wrong, and how can I fix this?

Thanks!

childofthe1980s

Sylvain Lafontaine

3/18/2007 7:38:00 PM

0

I should have added that I took the assumption that the DSCRIPTN field is of
char type.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23LiAVLZaHHA.1296@TK2MSFTNGP02.phx.gbl...
> It's because 0 is not a character string. Replace it with '0'.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
> message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
>> Hello:
>>
>> I'm trying to complete an If, Then statement within a script that I put
>> together, and I keep getting an "Incorrect syntax near the keyword 'As'"
>> error toward the end of the statement.
>>
>> The whole statement says that if you are taking the payroll codes from
>> the
>> benefit table, then provide the payroll code's description such "health
>> insurance" or "dental insurance".
>>
>> The very beginning of my clause, of course, says "SELECT ... CASE
>> UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
>> WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
>> WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
>> WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
>> WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
>> ELSE 0 END As [Payroll Description]
>>
>> There must be something wrong with the ELSE portion. I tried to fix it,
>> by
>> saying ELSE '' END As [Payroll Description]. But, I got the same error.
>>
>> What am I doing wrong, and how can I fix this?
>>
>> Thanks!
>>
>> childofthe1980s
>
>


childofthe1980s

3/19/2007 1:52:00 AM

0

Hi Sylvain:

Now, I'm getting that "error converting data type varchar to numeric" error.
Help!!!

childofthe1980s

"Sylvain Lafontaine" wrote:

> I should have added that I took the assumption that the DSCRIPTN field is of
> char type.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:%23LiAVLZaHHA.1296@TK2MSFTNGP02.phx.gbl...
> > It's because 0 is not a character string. Replace it with '0'.
> >
> > --
> > Sylvain Lafontaine, ing.
> > MVP - Technologies Virtual-PC
> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >
> >
> > "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
> > message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
> >> Hello:
> >>
> >> I'm trying to complete an If, Then statement within a script that I put
> >> together, and I keep getting an "Incorrect syntax near the keyword 'As'"
> >> error toward the end of the statement.
> >>
> >> The whole statement says that if you are taking the payroll codes from
> >> the
> >> benefit table, then provide the payroll code's description such "health
> >> insurance" or "dental insurance".
> >>
> >> The very beginning of my clause, of course, says "SELECT ... CASE
> >> UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
> >> WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
> >> WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
> >> WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
> >> WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
> >> ELSE 0 END As [Payroll Description]
> >>
> >> There must be something wrong with the ELSE portion. I tried to fix it,
> >> by
> >> saying ELSE '' END As [Payroll Description]. But, I got the same error.
> >>
> >> What am I doing wrong, and how can I fix this?
> >>
> >> Thanks!
> >>
> >> childofthe1980s
> >
> >
>
>
>

Sylvain Lafontaine

3/19/2007 2:06:00 AM

0

What's the exact type of the field UPR40800.DSCRIPTN ?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
message news:0A1834D5-8E5C-4804-9F49-0FC425841935@microsoft.com...
> Hi Sylvain:
>
> Now, I'm getting that "error converting data type varchar to numeric"
> error.
> Help!!!
>
> childofthe1980s
>
> "Sylvain Lafontaine" wrote:
>
>> I should have added that I took the assumption that the DSCRIPTN field is
>> of
>> char type.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>> wrote in message news:%23LiAVLZaHHA.1296@TK2MSFTNGP02.phx.gbl...
>> > It's because 0 is not a character string. Replace it with '0'.
>> >
>> > --
>> > Sylvain Lafontaine, ing.
>> > MVP - Technologies Virtual-PC
>> > E-mail: sylvain aei ca (fill the blanks, no spam please)
>> >
>> >
>> > "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
>> > message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
>> >> Hello:
>> >>
>> >> I'm trying to complete an If, Then statement within a script that I
>> >> put
>> >> together, and I keep getting an "Incorrect syntax near the keyword
>> >> 'As'"
>> >> error toward the end of the statement.
>> >>
>> >> The whole statement says that if you are taking the payroll codes from
>> >> the
>> >> benefit table, then provide the payroll code's description such
>> >> "health
>> >> insurance" or "dental insurance".
>> >>
>> >> The very beginning of my clause, of course, says "SELECT ... CASE
>> >> UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
>> >> WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
>> >> WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
>> >> WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
>> >> WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
>> >> ELSE 0 END As [Payroll Description]
>> >>
>> >> There must be something wrong with the ELSE portion. I tried to fix
>> >> it,
>> >> by
>> >> saying ELSE '' END As [Payroll Description]. But, I got the same
>> >> error.
>> >>
>> >> What am I doing wrong, and how can I fix this?
>> >>
>> >> Thanks!
>> >>
>> >> childofthe1980s
>> >
>> >
>>
>>
>>


Sylvain Lafontaine

3/19/2007 2:16:00 AM

0

Also, another possibility would be that you need a comma after the [Payroll
Description] or that the error is in the following FROM statement.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%2378%23oscaHHA.4396@TK2MSFTNGP06.phx.gbl...
> What's the exact type of the field UPR40800.DSCRIPTN ?
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
> message news:0A1834D5-8E5C-4804-9F49-0FC425841935@microsoft.com...
>> Hi Sylvain:
>>
>> Now, I'm getting that "error converting data type varchar to numeric"
>> error.
>> Help!!!
>>
>> childofthe1980s
>>
>> "Sylvain Lafontaine" wrote:
>>
>>> I should have added that I took the assumption that the DSCRIPTN field
>>> is of
>>> char type.
>>>
>>> --
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>>
>>>
>>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>>> wrote in message news:%23LiAVLZaHHA.1296@TK2MSFTNGP02.phx.gbl...
>>> > It's because 0 is not a character string. Replace it with '0'.
>>> >
>>> > --
>>> > Sylvain Lafontaine, ing.
>>> > MVP - Technologies Virtual-PC
>>> > E-mail: sylvain aei ca (fill the blanks, no spam please)
>>> >
>>> >
>>> > "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
>>> > message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
>>> >> Hello:
>>> >>
>>> >> I'm trying to complete an If, Then statement within a script that I
>>> >> put
>>> >> together, and I keep getting an "Incorrect syntax near the keyword
>>> >> 'As'"
>>> >> error toward the end of the statement.
>>> >>
>>> >> The whole statement says that if you are taking the payroll codes
>>> >> from
>>> >> the
>>> >> benefit table, then provide the payroll code's description such
>>> >> "health
>>> >> insurance" or "dental insurance".
>>> >>
>>> >> The very beginning of my clause, of course, says "SELECT ... CASE
>>> >> UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
>>> >> WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
>>> >> WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
>>> >> WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
>>> >> WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
>>> >> ELSE 0 END As [Payroll Description]
>>> >>
>>> >> There must be something wrong with the ELSE portion. I tried to fix
>>> >> it,
>>> >> by
>>> >> saying ELSE '' END As [Payroll Description]. But, I got the same
>>> >> error.
>>> >>
>>> >> What am I doing wrong, and how can I fix this?
>>> >>
>>> >> Thanks!
>>> >>
>>> >> childofthe1980s
>>> >
>>> >
>>>
>>>
>>>
>
>


childofthe1980s

3/19/2007 2:41:00 AM

0

string (perhaps alphanumeric)

"Sylvain Lafontaine" wrote:

> What's the exact type of the field UPR40800.DSCRIPTN ?
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
> message news:0A1834D5-8E5C-4804-9F49-0FC425841935@microsoft.com...
> > Hi Sylvain:
> >
> > Now, I'm getting that "error converting data type varchar to numeric"
> > error.
> > Help!!!
> >
> > childofthe1980s
> >
> > "Sylvain Lafontaine" wrote:
> >
> >> I should have added that I took the assumption that the DSCRIPTN field is
> >> of
> >> char type.
> >>
> >> --
> >> Sylvain Lafontaine, ing.
> >> MVP - Technologies Virtual-PC
> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
> >>
> >>
> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> >> wrote in message news:%23LiAVLZaHHA.1296@TK2MSFTNGP02.phx.gbl...
> >> > It's because 0 is not a character string. Replace it with '0'.
> >> >
> >> > --
> >> > Sylvain Lafontaine, ing.
> >> > MVP - Technologies Virtual-PC
> >> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >> >
> >> >
> >> > "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
> >> > message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
> >> >> Hello:
> >> >>
> >> >> I'm trying to complete an If, Then statement within a script that I
> >> >> put
> >> >> together, and I keep getting an "Incorrect syntax near the keyword
> >> >> 'As'"
> >> >> error toward the end of the statement.
> >> >>
> >> >> The whole statement says that if you are taking the payroll codes from
> >> >> the
> >> >> benefit table, then provide the payroll code's description such
> >> >> "health
> >> >> insurance" or "dental insurance".
> >> >>
> >> >> The very beginning of my clause, of course, says "SELECT ... CASE
> >> >> UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
> >> >> WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
> >> >> WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
> >> >> WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
> >> >> WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
> >> >> ELSE 0 END As [Payroll Description]
> >> >>
> >> >> There must be something wrong with the ELSE portion. I tried to fix
> >> >> it,
> >> >> by
> >> >> saying ELSE '' END As [Payroll Description]. But, I got the same
> >> >> error.
> >> >>
> >> >> What am I doing wrong, and how can I fix this?
> >> >>
> >> >> Thanks!
> >> >>
> >> >> childofthe1980s
> >> >
> >> >
> >>
> >>
> >>
>
>
>

childofthe1980s

3/19/2007 2:42:00 AM

0

no, it's neither of those.

I don't understand why this has to be so hard.

childofthe1980s

"Sylvain Lafontaine" wrote:

> Also, another possibility would be that you need a comma after the [Payroll
> Description] or that the error is in the following FROM statement.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:%2378%23oscaHHA.4396@TK2MSFTNGP06.phx.gbl...
> > What's the exact type of the field UPR40800.DSCRIPTN ?
> >
> > --
> > Sylvain Lafontaine, ing.
> > MVP - Technologies Virtual-PC
> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >
> >
> > "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
> > message news:0A1834D5-8E5C-4804-9F49-0FC425841935@microsoft.com...
> >> Hi Sylvain:
> >>
> >> Now, I'm getting that "error converting data type varchar to numeric"
> >> error.
> >> Help!!!
> >>
> >> childofthe1980s
> >>
> >> "Sylvain Lafontaine" wrote:
> >>
> >>> I should have added that I took the assumption that the DSCRIPTN field
> >>> is of
> >>> char type.
> >>>
> >>> --
> >>> Sylvain Lafontaine, ing.
> >>> MVP - Technologies Virtual-PC
> >>> E-mail: sylvain aei ca (fill the blanks, no spam please)
> >>>
> >>>
> >>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> >>> wrote in message news:%23LiAVLZaHHA.1296@TK2MSFTNGP02.phx.gbl...
> >>> > It's because 0 is not a character string. Replace it with '0'.
> >>> >
> >>> > --
> >>> > Sylvain Lafontaine, ing.
> >>> > MVP - Technologies Virtual-PC
> >>> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >>> >
> >>> >
> >>> > "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
> >>> > message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
> >>> >> Hello:
> >>> >>
> >>> >> I'm trying to complete an If, Then statement within a script that I
> >>> >> put
> >>> >> together, and I keep getting an "Incorrect syntax near the keyword
> >>> >> 'As'"
> >>> >> error toward the end of the statement.
> >>> >>
> >>> >> The whole statement says that if you are taking the payroll codes
> >>> >> from
> >>> >> the
> >>> >> benefit table, then provide the payroll code's description such
> >>> >> "health
> >>> >> insurance" or "dental insurance".
> >>> >>
> >>> >> The very beginning of my clause, of course, says "SELECT ... CASE
> >>> >> UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
> >>> >> WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
> >>> >> WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
> >>> >> WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
> >>> >> WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
> >>> >> ELSE 0 END As [Payroll Description]
> >>> >>
> >>> >> There must be something wrong with the ELSE portion. I tried to fix
> >>> >> it,
> >>> >> by
> >>> >> saying ELSE '' END As [Payroll Description]. But, I got the same
> >>> >> error.
> >>> >>
> >>> >> What am I doing wrong, and how can I fix this?
> >>> >>
> >>> >> Thanks!
> >>> >>
> >>> >> childofthe1980s
> >>> >
> >>> >
> >>>
> >>>
> >>>
> >
> >
>
>
>

Sylvain Lafontaine

3/19/2007 3:24:00 AM

0

Well, then if you are getting the message "error converting data type
varchar to numeric" error, then maybe your Case statement is OK and that you
have now a second error somewhere else.

However, as you didn't have posted your whole Select query nor the structure
of your tables that are relevant here, we cannot help you anymore at this
moment.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
message news:DE9803E8-6033-4D67-9FF3-B5653F65009F@microsoft.com...
> no, it's neither of those.
>
> I don't understand why this has to be so hard.
>
> childofthe1980s
>
> "Sylvain Lafontaine" wrote:
>
>> Also, another possibility would be that you need a comma after the
>> [Payroll
>> Description] or that the error is in the following FROM statement.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>> wrote in message news:%2378%23oscaHHA.4396@TK2MSFTNGP06.phx.gbl...
>> > What's the exact type of the field UPR40800.DSCRIPTN ?
>> >
>> > --
>> > Sylvain Lafontaine, ing.
>> > MVP - Technologies Virtual-PC
>> > E-mail: sylvain aei ca (fill the blanks, no spam please)
>> >
>> >
>> > "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
>> > message news:0A1834D5-8E5C-4804-9F49-0FC425841935@microsoft.com...
>> >> Hi Sylvain:
>> >>
>> >> Now, I'm getting that "error converting data type varchar to numeric"
>> >> error.
>> >> Help!!!
>> >>
>> >> childofthe1980s
>> >>
>> >> "Sylvain Lafontaine" wrote:
>> >>
>> >>> I should have added that I took the assumption that the DSCRIPTN
>> >>> field
>> >>> is of
>> >>> char type.
>> >>>
>> >>> --
>> >>> Sylvain Lafontaine, ing.
>> >>> MVP - Technologies Virtual-PC
>> >>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>> >>>
>> >>>
>> >>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
>> >>> please)>
>> >>> wrote in message news:%23LiAVLZaHHA.1296@TK2MSFTNGP02.phx.gbl...
>> >>> > It's because 0 is not a character string. Replace it with '0'.
>> >>> >
>> >>> > --
>> >>> > Sylvain Lafontaine, ing.
>> >>> > MVP - Technologies Virtual-PC
>> >>> > E-mail: sylvain aei ca (fill the blanks, no spam please)
>> >>> >
>> >>> >
>> >>> > "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote
>> >>> > in
>> >>> > message news:E4617DFD-17D4-4723-9C60-4FD60954DF91@microsoft.com...
>> >>> >> Hello:
>> >>> >>
>> >>> >> I'm trying to complete an If, Then statement within a script that
>> >>> >> I
>> >>> >> put
>> >>> >> together, and I keep getting an "Incorrect syntax near the keyword
>> >>> >> 'As'"
>> >>> >> error toward the end of the statement.
>> >>> >>
>> >>> >> The whole statement says that if you are taking the payroll codes
>> >>> >> from
>> >>> >> the
>> >>> >> benefit table, then provide the payroll code's description such
>> >>> >> "health
>> >>> >> insurance" or "dental insurance".
>> >>> >>
>> >>> >> The very beginning of my clause, of course, says "SELECT ... CASE
>> >>> >> UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
>> >>> >> WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
>> >>> >> WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
>> >>> >> WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
>> >>> >> WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
>> >>> >> ELSE 0 END As [Payroll Description]
>> >>> >>
>> >>> >> There must be something wrong with the ELSE portion. I tried to
>> >>> >> fix
>> >>> >> it,
>> >>> >> by
>> >>> >> saying ELSE '' END As [Payroll Description]. But, I got the same
>> >>> >> error.
>> >>> >>
>> >>> >> What am I doing wrong, and how can I fix this?
>> >>> >>
>> >>> >> Thanks!
>> >>> >>
>> >>> >> childofthe1980s
>> >>> >
>> >>> >
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>


--CELKO--

3/19/2007 2:55:00 PM

0

Having blanks inside a column name is a bad idea, since the name will
not port to other languages, is very easy to mis-type and messes up
the data dictionary. You also used the proprietary square brackets
instead of the proper double quotes.

You can make your code a lot easier to read without the extra WHEN
clauses. A CASE expression has one and only one data type. You need
to be sure that the THEN clauses are all the same data type. That can
be done with implicit promotion or use a CAST() function.

CASE
WHEN UPR30300.payrol_cd
IN ('0SC2HF', 'SC2LE', 'SC2LS', 'SC2LC')
THEN UPR40800.dscriptn
ELSE '{{Unknown}}' AS payroll_description