Skip to Content
author's profile photo
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 comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • author's profile photo
    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 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]