I am trying to run a query via SQL passthru and the SQL code contains a union join. The data does not get parsed in WebFocus but when I run it manually in my SQL browser it works as expected. I think it is to do with the brackets in the select statement but the brackets are necessary for the query to be valid. Is there any other code that I need to place for this to work?

  
SET EMPTYREPORT=ON
ENGINE SQLMYSQL SET DEFAULT_CONNECTION t2
ENGINE SQLMYSQL SET VARCHAR OFF
SQL SQLMYSQL PREPARE SQLOUT FOR

(SELECT LS.NAME,
         CASE
             WHEN (LA.Status='C') THEN 'U'
             WHEN (LA.Status='A') THEN 'A'
             ELSE 'N/A'
         END AS "Account_Status",
         Count("Account_Status") AS 'Counts'
  FROM account LA LEFT JOIN scheme LS ON (LA.scheme_id = LS.scheme_id)
  WHERE LS.scheme_id IN (1,2,3)
  GROUP BY "Account_Status",LS.scheme_id
  ORDER BY LS.scheme_id,"Account_Status")
UNION 
(SELECT LS.NAME,
         CASE
             WHEN (LA.Status='C') THEN 'U'
             ELSE 'N/A'
         END AS "Account_Status",
         Count("Account_Status") AS 'Counts'
  FROM account LA LEFT JOIN scheme LS ON (LA.scheme_id = LS.scheme_id)
  WHERE LS.loyalty_scheme_id IN (4,5,6)  
  GROUP BY "Account_Status",LS.scheme_id  
  ORDER BY LS.scheme_id,"Account_Status")
 
END
 
-RUN

TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD FORMAT DFIX DELIMITER , HEADER YES ENCLOSURE "
END

When I run this I get the following error

 
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 00000
 :  (-1) [00000] JDBFOC>> describecols():  error: getMetaData call on stmt
 : returned null ResultSetMetaData object!
 L    (FOC1405) SQL PREPARE ERROR.
 0 ERROR AT OR NEAR LINE     35  IN PROCEDURE _sql_custom
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
 BYPASSING TO END OF COMMAND 

This message has been edited. Last edited by: touch,

I very rarely use SQL Passthoughs but I believe your SQL statement has to be terminated with a semi-colon before the END statement.

Not sure this is 100% - but should get you closer. Good luck and hope this helps!
/*
Some SQL requirements:
1. no double quotes (unless you've set that to be the default)
2. you must do a 'group by' on all selected (non-aggregate) fields when using an aggregate ('count' in this case)
3. in a Union, the 'Order by' can only be in the last Select statement
4. In your last Select (above), why group on a field (loyalty_scheme_id) that hasn't been selected? (I added it to the select)
*/

SELECT ls.acct_name 'Account Name' ,
CASE la.acct_status
WHEN 'C' THEN 'U'
WHEN 'A' THEN 'A'
ELSE 'N/A'
END AS Account_Status ,
ls.loyalty_scheme_id 'Scheme ID',
COUNT(la.acct_status) AS Counts
FROM loyalty_scheme ls
LEFT JOIN loyalty_account la ON ls.loyalty_scheme_id = la.loyalty_scheme_id
WHERE ls.loyalty_scheme_id IN ( 1, 2, 3 )
GROUP BY ls.acct_name ,
la.acct_status,
ls.loyalty_scheme_id
UNION
SELECT ls.acct_name 'Account Name',
CASE la.acct_status
WHEN 'C' THEN 'U'
ELSE 'N/A'
END AS Account_Status ,
ls.loyalty_scheme_id 'Scheme ID',
COUNT(la.acct_status) AS Counts
FROM loyalty_account la
LEFT JOIN loyalty_scheme ls ON ls.loyalty_scheme_id = la.loyalty_scheme_id
WHERE ls.loyalty_scheme_id IN ( 4, 5, 6 )
GROUP BY ls.acct_name ,
la.acct_status,
ls.loyalty_scheme_id
ORDER BY ls.acct_name

Forgot to mention that you should 'test drive' your SQL in SSMS first...

Try putting your CASE statements all on one line - I've found that the "END" keyword on a CASE statement will break my SQL passthrough if its on a separate line from the CASE.

CASE WHEN (LA.Status='C') THEN 'U' WHEN (LA.Status='A') THEN 'A' ELSE 'N/A' END AS "Account_Status",

- I use passthrough with Oracle SQL, and have used UNIONs successfully. In fact, I can't say that I've found a valid SELECT statement that I can't run as passthrough yet (and I've run some exotic ones!) . . .

Rob

I have tried playing around with it and it seems to be working!

Thanks a lot to JALdbaDEV and MathematicalRob

Hopefully, this will make it easier for our team to build more complex reports