Focal Point
[CLOSED] NE / OR not working correctly in SQL PREPARE?

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/4497075576

April 13, 2015, 02:34 PM
Martin vK
[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 8203M, 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 8203M, 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;



- Jack Gross
WF through 8.1.05