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>,

Don't you want AND with NE?

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.

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

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)

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

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;