IF STATEMENTS IN SELECT STATEMENTS

IF STATEMENTS IN SELECT STATEMENTS

Post by Avi » Thu, 15 Nov 2001 04:06:18



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 STATEMENTS IN SELECT STATEMENTS

Post by Prasad Koukuntl » Thu, 15 Nov 2001 04:20:45


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.


 
 
 

IF STATEMENTS IN SELECT STATEMENTS

Post by Zachary Well » Thu, 15 Nov 2001 04:40:34


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.

 
 
 

IF STATEMENTS IN SELECT STATEMENTS

Post by Paul » Thu, 15 Nov 2001 04:32:31


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


> 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 STATEMENTS IN SELECT STATEMENTS

Post by oj » Thu, 15 Nov 2001 04:39:34


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 STATEMENTS IN SELECT STATEMENTS

Post by John Shelle » Thu, 15 Nov 2001 04:36:54


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.

 
 
 

IF STATEMENTS IN SELECT STATEMENTS

Post by Avi Shenka » Thu, 15 Nov 2001 06:51:15


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!

 
 
 

IF STATEMENTS IN SELECT STATEMENTS

Post by Zachary Well » Thu, 15 Nov 2001 07:01:24


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!

 
 
 

1. Customer Statement SQL Select statement???

Aaaumph!  I'm trying to build a customer statement report in Crystal
Reports.  I've been working on this sql statement (stored procedure,
SQLserver 7) for so danged long.

Can anyone give me a similar example of a select statement to build a
customer statement?  It doesn't have to match my scenario if it
provides a record for each invoice and each payment applied to a given
job/customer.

{{I have a JOBS table, INVOICES table, JOB_SERVICES table, PAYMENTS
table and an INVOICE_PAYMENTS table.}}

For every job, there could be multiple services assigned.  An invoice
can be applied to one or more of those job services (via an invoice#
field in Job_Services).  Payments can be applied and distributed to
the invoices.

Basically, can anyone refer me to a sample that consists of a database
containing customer invoices and customer payments.  And a SELECT
statement that produces a line item for each invoice and payments
pertaining to a given job (or customer)?

I'm getting so confused and frustrated knowing that I must be so dang
close.  An example would help to the point where could apply the
concept to my scenario.  Any suggestions??

Thanks!!!

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----

2. Contractors NYC

3. Using IF statements in Select statements

4. C++/OO/CORBA/COM/Java/RDBMS in a Fortune 100 company

5. USING A FIELD FROM MAIN SELECT STATEMENT IN SUB SELECT STATEMENT

6. How to define MS SQL 7.0 based system scalability?

7. Help me convert a SELECT statement to an UPDATE statement

8. ReportSmith - question on defining records for printing

9. insert statement using values from a select statement

10. Running SELECT statement within EXECUTE statement

11. Sleeping SELECT statement is blocking another statement

12. convert select statement ro delete statement

13. Conditional Statements in the INSERT-SELECT statement!