Hello

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.STATE  

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

quote:

BI_Developer

Try using EXTERNAL SQL. You may have to tweak the syntax to what webfocus would like though.

Give it a try...

Thanks

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

Thank you Martin. A, B and Z is the same table. I will try your code.

quote:

Originally posted by MartinY:
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'.

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.

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 sometable  

command. Copy paste the TABLE request to where you need it.