Is there a way to convert SQL to WebFOCUS? I cannot use SQL passthrough. I am trying to convert below code. Any help is appreciated?
SELECT A.STATE, CASE WHEN SUM(CASE WHEN A.CITY = 'EDI' OR A.CITY = 'JC' THEN 1 ELSE 0 END) = 0 THEN 'N' ELSE 'Y' END AS T1 FROM TAB.TABLE_NAME A WHERE EXISTS (SELECT '1' FROM TAB.TABLE_NAME B WHERE A.STATE = B.STATE AND CURRENT DATE BETWEEN B.FROM_DT AND COALESCE(B.TO_DT,'12/31/9999') AND COALESCE(DATE(B.TD),'12/31/9999') = ( SELECT MAX(COALESCE(DATE(Z.TR_DT),'12/31/9999')) FROM TAB.TABLE_NAME Z WHERE B.STATE = Z.STATE AND B.CITY = Z.CITY ) ) AND A.STATE = 'NJ' GROUP BY A.STATEThis message has been edited. Last edited by: BI_Developer,
WF 8.2.01 APP STUDIO
Try using EXTERNAL SQL. You may have to tweak the syntax to what webfocus would like though.
Give it a try...
Windows, All Outputs
I may be wrong, but since B.STATE must be equal to Z.STATE and B.STATE equal to A.STATE and A.STATE equal to 'NJ', then your code may be simplified as per below
SELECT A.STATE, CASE WHEN SUM(CASE WHEN A.CITY = 'EDI' OR A.CITY = 'JC' THEN 1 ELSE 0 END) = 0 THEN 'N' ELSE 'Y' END AS T1 FROM TAB.TABLE_NAME A WHERE A.STATE = 'NJ' GROUP BY A.STATE
But I suspect that you are requesting from three different tables even if you pseudo all with TABLE_NAME (e.g Sales table that have sale period) and that you don't really need to filter on 'NJ'.
Not a perfect option; it's just pseudo-code (not even tested and can even be simplified), but something similar to this may work if other option don't.
This is just to give ideas on how to explose an SQL query in multiple FOCUS requests.
When I don't know how to perform something, I like to do it step by step and then look to simplify it.
DEFINE FILE TABLE_NAME_Z DT_Z /YYMD MISSING ON = IF TR_DT EQ MISSING THEN '9999/12/31' ELSE TR_DT; END TABLE FILE TABLE_NAME_Z SUM MAX.DT_Z as 'MAX_DT_Z' BY STATE BY CITY ON TABLE HOLD AS TBL_Z FORMAT FOCUS INDEX STATE CITY END -RUN DEFINE FILE TABLE_NAME_B TO_DT_B /YYMD MISSING ON = IF TO_DT EQ MISSING THEN '9999/12/31' ELSE TO_DT; DT_B /YYMD MISSING ON = IF DT EQ MISSING THEN '9999/12/31' ELSE DT; END TABLE FILE TABLE_NAME_B BY STATE BY CITY BY DT_B WHERE CURRENT_DATE GE FROM_DT; WHERE CURRENT_DATE LE TO_DT_B; ON TABLE HOLD AS TBL_B FORMAT FOCUS END -RUN JOIN STATE AND CITY IN TBL_B TAG T1 TO STATE AND CITY IN TBL_Z TAG T2 AS J1 END TABLE FILE TBL_B BY T1.STATE WHERE DT_B EQ MAX_DT_Z; ON TABLE HOLD AS TBL_C FORMAT ALPHA END -RUN JOIN CLEAR * END DEFINE FILE TABLE_NAME_A COND /I3 = IF CITY EQ 'EDI' OR 'JC' THEN 1 ELSE 0; END TABLE FILE TABLE_NAME_A SUM COND NOPRINT COMPUTE T1 /A1 = IF COND EQ 0 THEN 'N' ELSE 'Y'; BY STATE WHERE STATE IN FILE TBL_C; -*WHERE STATE EQ 'NJ'; END -RUN
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
Thank you Martin. A, B and Z is the same table. I will try your code.
WF 8.2.01 APP STUDIO
There are multiple ways to covert this to WF. You would first have to create synonyms of the tables in WF. Then in a procedure, you can create individual hold files that contain the pertinent rows from the tables specified and then join and report from them. Secondly, after creating individual table synonyms in WF, a cluster synonym of the tables can be created which can also include filter logic needed in your query.
WF 82 Windows
Can you use SQL pass through once? If so you could let the server generate the FOCUS code for you and then use it. Go to the server console and create a new procedure. Disable APT so that FOCUS is generated instead of SQL, and the put in your SELECT statement like this:
SQL SET APT=OFF END SQL SELECT somecolumn FROM sometable WHERE somecondition ; END
Then click on the user icon and select Session Log. In the editor look for the
TABLE FILE sometablecommand. Copy paste the TABLE request to where you need it.
|Powered by Social Strata|