Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] NE / OR not working correctly in SQL PREPARE?
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] NE / OR not working correctly in SQL PREPARE?
 Login/Join
 
Platinum Member
posted
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
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Don't you want AND with NE?


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
 
Posts: 103 | Registered: April 27, 2011Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 578 | Registered: October 01, 2014Reply With QuoteReport This Post
Master
posted Hide Post
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.
 
Posts: 938 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 578 | Registered: October 01, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] NE / OR not working correctly in SQL PREPARE?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.