Very Computer

Hi,
  I'm having serious trouble with the syntax of if
statements inside a select statement. I'm trying to
compare two columns, but to check if first it's an exact
match, if not then check for 6 charachters match, if not
check for 4, if not check for 2.  I'm looking for
something like:

If combo.unspsc_small = left(combo.unspsc_large, 8)
elseif
combo.unspsc_small = left(combo.unspsc_large, 6)
elseif
combo.unspsc_small = left(combo.unspsc_large, 4)
else
combo.unspsc_small = left(combo.unspsc_large, 2)

I can't get it to work at all. If you can help me then
please do.

Avi,
Use CASE statements. Look in BOL (books online) for help with the syntax.

Prasad

Quote:> Hi,
>   I'm having serious trouble with the syntax of if
> statements inside a select statement. I'm trying to
> compare two columns, but to check if first it's an exact
> match, if not then check for 6 charachters match, if not
> check for 4, if not check for 2.  I'm looking for
> something like:

> If combo.unspsc_small = left(combo.unspsc_large, 8)
> elseif
> combo.unspsc_small = left(combo.unspsc_large, 6)
> elseif
> combo.unspsc_small = left(combo.unspsc_large, 4)
> else
> combo.unspsc_small = left(combo.unspsc_large, 2)

> I can't get it to work at all. If you can help me then
> please do.

You can't use IF statements in selects, you must use CASE expressions. With
what little information you've given I can't tell what you really want to
happen so I can't give you an example :(

Zach

Quote:> Hi,
>   I'm having serious trouble with the syntax of if
> statements inside a select statement. I'm trying to
> compare two columns, but to check if first it's an exact
> match, if not then check for 6 charachters match, if not
> check for 4, if not check for 2.  I'm looking for
> something like:

> If combo.unspsc_small = left(combo.unspsc_large, 8)
> elseif
> combo.unspsc_small = left(combo.unspsc_large, 6)
> elseif
> combo.unspsc_small = left(combo.unspsc_large, 4)
> else
> combo.unspsc_small = left(combo.unspsc_large, 2)

> I can't get it to work at all. If you can help me then
> please do.

Avi,

Your example translates to something like:

SELECT CASE WHEN combo.unspsc_small = combo.unspsc_large THEN
<something>
                          WHEN combo.unspsc_small =
SUBSTRING(combo.unspsc_large, 1, 6) THEN <something>
                          WHEN combo.unspsc_small =
SUBSTRING(combo.unspsc_large, 1, 4) THEN <something>
                          WHEN combo.unspsc_small =
SUBSTRING(combo.unspsc_large, 1, 2) THEN <something>
                            [optional ELSE <something>]
             END
FROM combo

HTH,

Paul

avi,

not quite sure what you're after...

case combo.unspsc_small
    when left(combo.unspsc_large, 8) then 'match 8'
    when left(combo.unspsc_large, 6) then 'match 6'
    when left(combo.unspsc_large, 4) then 'match 4'
    when left(combo.unspsc_large, 2) then 'match 2'
end

perhaps, you might want to post ddl and sample data so we can help better.

-oj

Quote:> Hi,
>   I'm having serious trouble with the syntax of if
> statements inside a select statement. I'm trying to
> compare two columns, but to check if first it's an exact
> match, if not then check for 6 charachters match, if not
> check for 4, if not check for 2.  I'm looking for
> something like:

> If combo.unspsc_small = left(combo.unspsc_large, 8)
> elseif
> combo.unspsc_small = left(combo.unspsc_large, 6)
> elseif
> combo.unspsc_small = left(combo.unspsc_large, 4)
> else
> combo.unspsc_small = left(combo.unspsc_large, 2)

> I can't get it to work at all. If you can help me then
> please do.

If combo.unspsc_small = left(combo.unspsc_large, 8)
Begin
    ********commands**********
End
else
if combo.unspsc_small = left(combo.unspsc_large, 6)
Begin
    ********commands**********
End
else
if combo.unspsc_small = left(combo.unspsc_large, 4)
Begin
    ********commands**********
End
else
if combo.unspsc_small = left(combo.unspsc_large, 2)
Begin
    ********commands**********
End
else
Begin
    ********commands**********
End

Quote:> Hi,
>   I'm having serious trouble with the syntax of if
> statements inside a select statement. I'm trying to
> compare two columns, but to check if first it's an exact
> match, if not then check for 6 charachters match, if not
> check for 4, if not check for 2.  I'm looking for
> something like:

> If combo.unspsc_small = left(combo.unspsc_large, 8)
> elseif
> combo.unspsc_small = left(combo.unspsc_large, 6)
> elseif
> combo.unspsc_small = left(combo.unspsc_large, 4)
> else
> combo.unspsc_small = left(combo.unspsc_large, 2)

> I can't get it to work at all. If you can help me then
> please do.

Hi,
   Thanx for the reply. What i'm actually trying to do is compare 2 columns in an access database (it sucks i know but i have to use access). Since one column is all 8 digit numbers and the other ranges from 2 to 8 digits, I need to find the best match. So i start with a perfect match of 8, if that doesnt work i look for a match of 6, then 4, and lastly 2. If the case is true, I just want the query to pull it out from the database.  I hope you can help me out with this description.

Thanx again,
Avi

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Just curious but if you're comparing two columns in an Access database, why
are you posting in a SQL newsgroup? Although we've got some really smart
people here, you'd probably find more accurate answers in the Access forums.

Zach

Hi,
   Thanx for the reply. What i'm actually trying to do is compare 2 columns
in an access database (it sucks i know but i have to use access). Since one
column is all 8 digit numbers and the other ranges from 2 to 8 digits, I
need to find the best match. So i start with a perfect match of 8, if that
doesnt work i look for a match of 6, then 4, and lastly 2. If the case is
true, I just want the query to pull it out from the database.  I hope you
can help me out with this description.

Thanx again,
Avi

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!