Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
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.
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
I tried to do this with CASE, didn't work
Have you tried my suggested preceding load solution?
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
No I didn't. Got problems with that, still trying to understand this function by manuals.
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)
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.