Skip to Content
author's profile photo Former Member
Former Member

SQL: Using IF in a SQL-Query

Hi there,

I need an automated query for chosing the correct commission for creating an invoice.

If the commission is set within the item sold (Commsion > 0) (OITM.CommisPcnt) SBO should use that one - otherwise it should work with the one of the OSLP-table (OSLP.Commission).

My query looks like that but always sets the commission to 0.00% (There is no error message):

if (select oitm.CommisPcnt from oitm where $[$38.1.0] = oitm.ItemCode) > 0

begin

select oitm.CommisPcnt from oitm inv1 where inv1.SlpCode = oslp.SlpCode

end

else

begin

select oslp.Commission from oslp oinv where oinv. = oitm.ItemCode

END

$[$38.1.0] = ItemCode

Thank you all in advance!!!

Kai

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on May 19, 2004 at 05:46 AM

    Hi Kai

    Try it with something like that:

    -


    IF (SELECT OITM.CommisPcnt FROM OITM WHERE OITM.ItemCode = $[$38.1.0]) > 0

    SELECT OITM.CommisPcnt

    FROM OITM

    WHERE OITM.ItemCode = $[$38.1.0]

    ELSE

    SELECT OSLP.Commission

    FROM OSLP

    WHERE OSLP.SlpCode = [[[OINV.SlpCode]]]

    -


    I don't like SBO's SQL-Syntax, so I don't know whether $[$38.1.0] is correct and I don't know how to insert the correct value of the invoice's SlpCode (I marked it in the query with [[[OINV.SlpCode]]]); I think you have to replace [[[OINV.SlpCode]]] with some $[$...].

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks a lot!!!

      Modifying the variables a bit and it works fine now!!!

      IF (SELECT OITM.CommisPcnt FROM OITM WHERE OITM.ItemCode = $[$38.1.0]) > 0

      SELECT OITM.CommisPcnt

      FROM OITM

      WHERE OITM.ItemCode = $[$38.1.0]

      ELSE

      SELECT OSLP.Commission

      FROM OSLP

      WHERE OSLP.SlpCode = $[$20.0.0]

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.