[CLOSED] NE / OR not working correctly in SQL PREPARE?
Hi,
Triggered by a customer question on wrong results I found a strange behaviour in expressions using NE in combination with OR.
I have a flow with source transformations which produces following code:
LOAD MASTER dwhdb/kvk_vestiging
EX -lines 3 EDAPUT MASTER,dwhdb/kvk_vestiging,A,MEM,
DEFINE EENMANSZAAK/I6=IF RECHTSVORMCODE EQ '01' OR '02' THEN 1 ELSE 0; $
DEFINE BV_OF_NV/I6=IF RECHTSVORMCODE NE '01' OR '02' OR '70' OR '71' OR '72' OR '73' OR '74' OR '88' OR '89' AND VESTIGINGSNUMMER GT 0 THEN 1 ELSE 0; $
-RUN
SQL PREPARE SQLIN FROM
SELECT
SUM(T1.EENMANSZAAK ) ,
SUM(T1.BV_OF_NV )
FROM
dwhdb.kvk_vestiging T1
After turning on SQL traces I found out that BV_OF_NV is translated as follows: SUM((CASE WHEN ("T1"."RECHTSVORMCODE" <> '01' OR "T1"."RECHTSVORMCODE" <> '02' OR "T1"."RECHTSVORMCODE" <> '70' OR "T1"."RECHTSVORMCODE" <> '71' OR "T1"."RECHTSVORMCODE" <> '72' OR "T1"."RECHTSVORMCODE" <> '73' OR "T1"."RECHTSVORMCODE" <> '74' OR "T1"."RECHTSVORMCODE" <> '88' OR "T1"."RECHTSVORMCODE" <> '89') AND "T1"."VESTIGINGSNUMMER" > 0 THEN 1 ELSE 0 END)) AS "BV_OF_NV"
This does not seem correct to me. If I make a similar request in the Reporting Server, like this:
DEFINE FILE KVK_VESTIGING
EENMANSZAAK/I6=IF RECHTSVORMCODE EQ '01' OR '02' THEN 1 ELSE 0;
BV_OF_NV/I6=IF RECHTSVORMCODE NE '01' OR '02' OR '70' OR '71' OR '72' OR '73' OR '74' OR '88' OR '89' AND VESTIGINGSNUMMER GT 0 THEN 1 ELSE 0;
END
TABLE FILE KVK_VESTIGING
SUM
EENMANSZAAK
BV_OF_NV
END
If I put SQL traces on I get the following SQL translation: SUM((CASE WHEN ((T1."RECHTSVORMCODE" NOT IN('01', '02', '70', '71', '72', '73', '74', '88', '89')) AND (T1."VESTIGINGSNUMMER" > 0)) THEN 1 ELSE 0 END))
This is what I indeed also expected in the DM flow. Is there a reasonable explanation for the different behaviour, or is this a bug (in iDM)? I tested this in 7.7.06M version, and I used a DB2 source table.
Martin.This message has been edited. Last edited by: <Kathryn Henning>,
WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
April 14, 2015, 11:48 AM
Kevin W
Don't you want AND with NE?
WebFOCUS 7.7.05 (Someday 8) Windows 7, All Outputs In Focus since 1983.
April 14, 2015, 12:28 PM
Martin vK
Kevin,
Using NE with AND gives an error. Actually I do not mind if it is NE+OR or NE+AND as long as it works everywhere in the same way.
Now, if I have a DEFINE in WebFOCUS:
TEST/I1 = IF FIELDA NE 'A' OR 'B' THEN 1 ELSE 0;
this is translated correctly by the Reporting Server into SQL:
CASE WHEN FIELDA NOT IN ('A','B') THEN 1 ELSE 0 END
If I use this same definition however as a source transformation in Data Management Console the same translates into SQL as:
CASE WHEN FIELDA <> 'A' OR FIELDA <> 'B' THEN 1 ELSE 0 END
At least in 7706M with DB2 adapter. Has anyone else tested this with other versions or DBMS?
As a workaround I have modified the source transformation into
IF NOT FIELDA IN ('A','B') THEN 1 ELSE 0
, which works fine.
Martin.
WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
April 15, 2015, 09:03 AM
GavinL
About the error. I think you need to specify what the synonym is, not just the field.
Your scenario with the OR..
DEFINE FILE CAR
TEST/I1=IF CAR.BODY.SEATS NE 1 OR 2 THEN 1 ELSE 0;
END
TABLE FILE CAR
PRINT
CAR.ORIGIN.COUNTRY
CAR.COMP.CAR
CAR.BODY.TEST
CAR.BODY.SEATS
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
The results:
COUNTRY CAR TEST SEATS
ENGLAND JAGUAR 0 2
ENGLAND JAGUAR 1 5
ENGLAND JENSEN 1 4
ENGLAND TRIUMPH 0 2
JAPAN DATSUN 1 4
JAPAN TOYOTA 1 4
ITALY ALFA ROMEO 1 4
ITALY ALFA ROMEO 0 2
ITALY ALFA ROMEO 0 2
ITALY MASERATI 0 2
W GERMANY AUDI 1 5
W GERMANY BMW 1 5
W GERMANY BMW 1 4
W GERMANY BMW 1 5
W GERMANY BMW 1 5
W GERMANY BMW 1 5
W GERMANY BMW 1 5
FRANCE PEUGEOT 1 5
- FOCUS Man, just FOCUS! ----------------------------- Product: WebFOCUS Version: 8.1.04 Server: Windows 2008 Server
April 15, 2015, 10:54 AM
jgelona
I've been coding the FOCUS language for 30 years and I have NEVER used OR with NE. Maybe it is my math background, because mathematically, using OR with NE will never result in a TRUE condition. In the days before the WHERE clause I did:
TABLE FILE CAR
PRINT ....
IF SEATS NE 1
IF SEATS NE 2
Even in a DEFINE I would do:
TEST/I1=SEATS NE 1 AND SEATS NE 2;
With the advent of the WHERE clause I always do the following in DEFINE and in TABLE
WHERE NOT SEATS IN (1,2)
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
April 16, 2015, 09:06 AM
GavinL
I'm not disputing your logic as I agree, I'm only helping him with his error. With that being said, I was a bit surprised in the results.
To me, this should of returned a 1 for TEST, but maybe IBI is doing some weird logic in the background.
COUNTRY CAR TEST SEATS
ENGLAND JAGUAR 0 2
- FOCUS Man, just FOCUS! ----------------------------- Product: WebFOCUS Version: 8.1.04 Server: Windows 2008 Server
April 27, 2015, 01:53 PM
j.gross
quote:
LOAD MASTER dwhdb/kvk_vestiging EX -lines 3 EDAPUT MASTER,dwhdb/kvk_vestiging,A,MEM, DEFINE EENMANSZAAK/I6=IF RECHTSVORMCODE EQ '01' OR '02' THEN 1 ELSE 0; $ DEFINE BV_OF_NV/I6=IF RECHTSVORMCODE NE '01' OR '02' OR '70' OR '71' OR '72' OR '73' OR '74' OR '88' OR '89' AND VESTIGINGSNUMMER GT 0 THEN 1 ELSE 0; $
Took me a minute to realize you were appending the two defines to the in-memory copy of kvk_vestiging.mas.
It could be that the mix of AND and OR is confusing the parser. Before turning to a more radical revision, I would try it with the RHS expression in the second DEFINE fully parenthesized:
IF (RECHTSVORMCODE NE '01' OR '02' OR '70' OR '71' OR '72' OR '73' OR '74' OR '88' OR '89') AND (VESTIGINGSNUMMER GT 0) THEN 1 ELSE 0;