Use if else sql statement with two selects

Question

Currently I have the following SQL query

DECLARE @UgpEntry VARCHAR(50)

SET @t1.UgpEntry = (SELECT @UgpEntry = t1.UgpEntry from OITM t1)

IF (@UgpEntry = -1)

SELECT
t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , 
           (select max(p.Price)
        from ITM1 p 
        where p.ItemCode = t1.ItemCode 
          and p.PriceList = 1) as [price_1]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='00004' and t1.UgpEntry = -1
 
ELSE

SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , (select max(p.Price)
        from ITM9 p 
        where p.ItemCode = t1.ItemCode 
          and p.UomEntry = 1) as [price_1],

           (select max(p.Price)
        from ITM1 p 
        where p.ItemCode = t1.ItemCode 
          and p.PriceList = 1) as [preciocaja]
     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='v6p' and t1.UgpEntry > -1

What I'm trying to do:

If this column t1.UgpEntry from OITM table (already exists and have data) is equal to -1 then run the first query.
Else run the second query.

What am I doing wrong? i need to run one or the other select, depending on the IF statement

example: this record have a column named UgpEntry = -1

this record have UgpEntry = 5 and have another columns

i think i have this var wrong IF (@UgpEntry = -1) as it should be the column from OITM but not sure how to use that column as IF statement

Can you print the value of @UgpEntry to verify the value is indeed -1 at the time the IF statement is reached? You set the @UgpEntry with something like this: SELECT @UgpEntry = UgpEntry from yourTable — DeMaki
– DeMaki, Commented Apr 21, 2021 at 11:47
should i do that in the first line? DECLARE at UgpEntry SELECT @UgpEntry = UgpEntry from yourTable ? i just edited my main post — alexistkd
– alexistkd, Commented Apr 21, 2021 at 11:50
You do not have a WHERE clause in your SELECT statement to get UgpEntry from OITM. So if you have more than one row in that table you have no ide what the value of UgpEntry will be that gets returned. — JMabee
– JMabee, Commented Apr 21, 2021 at 11:59
@JMabee so on both select #1 and #2 and added where t.ugpentry = etc. post edited — alexistkd
– alexistkd, Commented Apr 21, 2021 at 12:02

DeMaki · Accepted Answer · 2021-04-21 12:02:40Z

1

The layout of your IF statement is correct. Just make sure the query to set @UgpEntry returns a single result.

DECLARE @UgpEntry VARCHAR(50)

SELECT @UgpEntry = UgpEntry FROM OITM WHERE [enter your condition]

IF (@UgpEntry = -1)
    [remaining logic from your original post]

answered Apr 21, 2021 at 12:02

DeMaki

5211 gold badge6 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alexistkd Over a year ago

perfect, that was my issue. thank you so much

Collectives™ on Stack Overflow

Use if else sql statement with two selects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related