If statement in SQL Select - Syntax

[sculptorlv]

Hello, got problem with syntax. Please help.

I'm trying to get additional column in my table with conditional value 0 or 1 (True or False) - I do it for the first time.

SQL SELECT

... 

SQL SELECT

  Tabl_contract.Code AS "Contract_Number",

  cle."Customer No_" AS "Customer_Number2",

  cle."Document No_" AS "Debitor_document",

..

IF ((cle."Document No_" > 0), 1, 0) AS "Debitor_Sign"

FROM "EUR-VENDEN".dbo."EUR VENDEN$Cust_ Ledger Entry" AS cle

...

And I got error in IF statement..

IF ((cle."Document No_" > 0), 1, 0) AS "Debitor_Sign"

[henrikalmen]

It seems that you are trying to mix SQL with QlikView scripting language.

You should probably use Preceding Load and do something like this:

MyTable:

LOAD

     *,

     IF ([Debitor_document] > 0), 1, 0) AS Debitor_Sign

;

SQL SELECT

  Tabl_contract.Code AS Contract_Number,

  cle.[Customer No_] AS Customer_Number2,

  cle.[Document No_] AS Debitor_document

FROM "EUR-VENDEN".dbo."EUR VENDEN$Cust_ Ledger Entry" As cle ...

join ... as Tabl_contract;

EDIT: I just realised I entered the wrong field name in the preceding load. I have changed that now.

[maxgro]

you can use a case in sql server

SQL SELECT

  Tabl_contract.Code AS "Contract_Number",

  cle."Customer No_" AS "Customer_Number2",

  cle."Document No_" AS "Debitor_document",

..

   case   "Debitor_Sign"=

          when cle."Document No_" > 0 then 1

          else 0

    end

.....

FROM "EUR-VENDEN".dbo."EUR VENDEN$Cust_ Ledger Entry" AS cle

https://msdn.microsoft.com/it-it/library/ms181765(v=sql.120).aspx

[sculptorlv]

I tried to do this with CASE, didn't work

[henrikalmen]

Have you tried my suggested preceding load solution?

[henrikalmen]

I have updated that solution now...

BTW, using case I think you should write your statement like this instead:

CASE

    WHEN [Document No_] > 0 THEN 1

ELSE

    0

END AS Debitor_Sign

[sculptorlv]

No I didn't. Got problems with that, still trying to understand this function by manuals.

[sculptorlv]

This would not work.

Actually I have doubled rows this the same Document No_ value. And there are two only options for Applied column.

1. Both Applied volumes = 0 for each of two Document No_ value

2. First Applied =1, second Applied = 0 ..

Thus, I need only ONE row of Document No_ with the value Applied 1 (if one of the value =1) or 0 (if both Applied values = 0)

[henrikalmen]

I'm not quite sure what you are trying to do. Can you post some sample data? What the tables look like, some data in them, and what you would want the result to look like.