Here in the client they have an EDA/iWay connection to an AS400 DB2 database. I could use some SQL syntax to access this database, but not all syntax are supported.

According to another consultant, WebFOCUS is doing translation instead of passing the SQL directly to the database through the EDA/iWay connection. One of the limitation is that I can't use SQL sub-query. For example, If I want to select the price value of the last day in the table:

SELECT T1.SECURITY_ID, T1.PRICE, T1.TRADE_DATE
FROM PRICE_TABLE T1
INNER JOIN
(SELECT SECURITY_ID, MAX(TRADE_DATE) AS MAX_DATE
FROM PRICE_TABLE
GROUP BY SECURITY_ID) T2
ON T1.SECURITY_ID = T2.SECURITY_ID AND T1.TRADE_DATE = T2.MAX_DATE

There is this error:

(FOC1400) SQLCODE IS -205 (HEX: FFFFFF33) XOPEN: 42S22
(FOC1410) SQL COLUMN NOT FOUND. (FOR TABLE: CHECK ALIAS NAMES)
L (FOC1405) SQL PREPARE ERROR.
(FOC1400) SQLCODE IS 1400 (HEX: 00000578)
: SQLCODE IS -205 (HEX: FFFFFF33) XOPEN: 42S22
L (FOC1405) SQL PREPARE ERROR.

Is there any document on this kind of SCALAR SQL? How to circumvent this sub-query issue?

Thanks!

This message has been edited. Last edited by: <Kathryn Henning>,

What is the code that is wrapped around your SQL ?

It should start with SQL DB2 to use SQL pass through.

Does your SQL need to run on the remote EDA server?

We do that like this:

-SET &EDASERVER = 'AS400';  $ Your remote EDA server
-SET &EDACONN = 'SQL400';  $ The database connector on the EDA server
-SET &EDAAPP = 'SQLREPORTS'; $ An app on the EDA server to store the results

SQL EDA SET SERVER &EDASERVER
-RUN

-REMOTE BEGIN
APP HOLD &EDAAPP

ENGINE DB2 SET DEFAULT_CONNECTION &EDACONN
SQL DB2 PREPARE SQLOUT FOR
...Put your SQL query here...
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS YOURREMOTEHOLDFILE FORMAT FOCUS
END
-RUN

-REMOTE END

CREATE SYNONYM yourapp/YOURLOCALHOLDFILE
 FOR YOURREMOTEHOLDFILE
 DBMS EDA AT &EDASERVER
 DROP
END
-RUN

Make sure you do all your sorting in the SQL query; doing that in the SQLOUT TABLE request can cause errors and agent crashes and probably isn't going to perform as well anyway.

quote:

Originally posted by Waz:
What is the code that is wrapped around your SQL ?

It should start with SQL DB2 to use SQL pass through.

This is not like real DB2 SQL passthrough. They use

SQL EDA

Some DB2 function and SQL syntax is not supported, like "FETCH FIRST XXX ROWS".

Then you're probably better off running the code against the remote EDA server like I showed.

That's using the -REMOTE BEGIN/END syntax.

This works fine, but you have to re-write your sql to make it so:

SQL
SELECT 
 T2.*, T4.*
FROM 
((SELECT 
   T1.* 
  FROM 
   whatever T1 
 ) T2
  INNER JOIN
 (SELECT
   SECURITY_ID, 
   MAX(somedate) AS SOMEDATE 
  FROM whatever T3 
  GROUP BY SECURITY_ID 
 ) T4
 ON 
  T2.someid = T4.SECURITY_ID AND 
  T2.somedate = T4.SOMEDATE 
)
END

quote:

Originally posted by Wep5622:
Then you're probably better off running the code against the remote EDA server like I showed.

That's using the -REMOTE BEGIN/END syntax.

Still playing with this syntax, thanks for the advise