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  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL to WebFOCUS
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] SQL to WebFOCUS
 Login/Join
 
Platinum Member
posted
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,


WF 8.2.01 APP STUDIO
PDF,HTML,EXL2K,Active
 
Posts: 133 | Registered: July 21, 2011Reply With QuoteReport This Post
Gold member
posted Hide Post
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


WebFOCUS 8
Windows, All Outputs
 
Posts: 60 | Registered: May 26, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 2259 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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'.


WF 8.2.01 APP STUDIO
PDF,HTML,EXL2K,Active
 
Posts: 133 | Registered: July 21, 2011Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 2 | Location:  | Registered: October 25, 2019Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 391 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL to WebFOCUS

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