$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: true, vro: true, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
avatar image
Former Member

IF statement syntax in SQL script view

I need to include a "IF" condition in the "SELECT" section of my SQL script view.

I tried the following syntax's but I get the error 'Incorrect SQL syntax near 'IF'

1.  IF(Revenue <> '0' AND Quantity <> '0', Revenue/Quantity, '0') AS Gross Price

2.  IF(Revenue != '0' AND Quantity != '0', Revenue/Quantity, '0') AS Gross Price

3.  IF(Revenue <> '0' AND Quantity <> '0' THEN Revenue/Quantity ELSE  '0' END) AS Gross Price

4.  IF(Revenue != '0' AND Quantity != '0' THEN Revenue/Quantity ELSE  '0' END) AS Gross Price

My final SQL would read like follows:

SELECT field1, field2, IF(......) AS field3

FROM table1

Can anybody please help with the correct IF statement syntax to be used in the SQL script based view?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Aug 20, 2014 at 08:33 AM

    Lakshmi,

    You can not use IF along with SELECT statement. Note that, you can achieve most of boolean logics with CASE statement syntax as noted by @Chandra Sekhar . In select, you are applying it over a column and your logic will be executed as many as times the count of result set rows. Hence , righting an imperative logic is not well appreciated. Still, if you want to do the same, create a calculation view and use intermediate calculated columns to achieve what you are expecting .

    Sreehari

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you Sreehari, Chandra and Henry for the responses.

      The SQL view is built on top of a graphical view, so I will write a calculated column in that and try to use it in my SQL view.

  • Aug 19, 2014 at 12:11 PM

    Hi Lakshmi,

    below is the syntax for IF statement.

    IF <bool_expr1> THEN

    <then_stmts1>

    [

    {

    ELSEIF <bool_expr2>

    THEN <then_stmts2>

    }...]

    [ELSE <else_stmts3>]

    END IF

    eg :

    BEGIN

    DECLARE found INT := 1;

    SELECT count(*) INTO found FROM books WHERE isbn = :v_isbn;

    IF :found = 0 THEN

    INSERT INTO books VALUES (:v_isbn, 'In-Memory Data Management', 1, 1, '2011', 42.75, 'EUR');

    ELSE

    UPDATE books SET price = 42.75 WHERE isbn =:v_isbn;

    END IF;

    END;

    Sreehari

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Lakshmi

      im pretty sure you can't use IF inside a SELECT statement as you just said.

      The IF statement is to control the flow of logic I.e. To decide which statements to execute in the script.

      I Think if you check the syntax for SELECT in the SQL Reference guide you'll find this to be true.

      you should be able to get rid of the IF and turn your expression into a 'conditional expression' that uses a logical test and Boolean results in the arithmetic to return the result you need.

      Something like

      (expression=value*value + expression<>value*X) AS colname

      there is no doubt a more elegant way of doing that most likely using CASE but I'm off to meeting and don't have time to check it out at the moment

      hope this helps