[CLOSED] Can SQL be run from Mainframe FOCUS 7.1.1?

WebMeister,

You can write SQL in MF 7.1.1. However, what are your underlying data? SQL tables? If not, it won't help you. If so, and you are using SQL passthru, then you are on your own.

Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

Expert

posted

With FOCUS or WebFOCUS, in some cases, SET ALL=ON or SET ALL=PASS allows you to join two tables to "include missing segment instances in a report".

You can use SQL to read non-DBMS files: FOCUS databases or flat files can be accessed with SQL passthru.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Guru

posted

Danny and Francis,

Thank you for your replies. It also dawned on me that a possible JOIN LEFT_OUTER..... might work, which is what I'm trying as we speak, except I just got a SYNTAX ERROR OR MISSING ELEMENT IN THE JOIN COMMAND.

What I had was

JOIN LEFT_OUTER STU_ID IN A0&&BNVC TO MULTIPLE STU_ID IN RBFILE AS A

So I'm working on that right now.

Again, thanks for replying.

Mainframe FOCUS 7.0
VM/CMS and MVS/TSO

Posts: 250 | Registered: January 14, 2008

Ignored post by webmeister posted

Expert

posted

LEFT_OUTER is only available in WebFOCUS 7.1 and later. I'm not sure if this is available in Mainframe FOCUS 7.1.1...

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Guru

posted

Well, darn! Is there any equivalent for Mainframe FOCUS 7.1.1 ?

Mainframe FOCUS 7.0
VM/CMS and MVS/TSO

Posts: 250 | Registered: January 14, 2008

Ignored post by webmeister posted

GinnyJakes

Expert

posted

If you are doing SQL Passthru, you would pass the join in the SQL and you would specify the left outer syntax appropriate for the data base you are trying to access. You wouldn't use a FOCUS join as you showed.

But, if you were to use FOCUS syntax, SET ALL=ON turns on left outer processing.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Danny-SRL

Virtuoso

posted

WebMeister,

So you are using FOCUS code and not SQL code. Then instead of

  
JOIN LEFT_OUTER STU_ID IN A0&&BNVC TO MULTIPLE STU_ID IN RBFILE AS A

use

SET ALL=ON  
JOIN STU_ID IN A0&&BNVC TO ALL STU_ID IN RBFILE AS A

Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

Guru

posted

Hi, Danny,

Will that give me what I'm looking for? I want to list all STU_ID from my main file and all associated data from my secondary file, and in the case of the primary file not having any associated data in the secondary file, I still want to list the STU_ID data from the primary file. So...would your suggestion give me what I'm interested in?

I just need to list all records from my primary file, whether or not htey have matching records from my secondary file.

In any case, I appreciate your suggestions.

Mainframe FOCUS 7.0
VM/CMS and MVS/TSO

Posts: 250 | Registered: January 14, 2008

Ignored post by webmeister posted

GinnyJakes

Expert

posted

As I mentioned above, the SET ALL=ON command causes a left outer join to be performed. Might not be too efficient. But you will get the desired results.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Danny-SRL

Virtuoso

posted

WebMeister,

It depends upon what other screening conditions you have in your TABLE request.

If your conditions are only on the primary file then you will get all the records of the primary file that match the conditions with the associated records from the secondary.

If you also have conditions on the secondary then use SET ALL=PASS.

Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

Expert

posted

So how many times are we going to suggest "SET ALL=ON or SET ALL=PASS"?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Guru

posted

Oh, about 4 or 5 times; I'm still working with the SET ALL commands andnot getting the results I want I've tried SET ALL=PASS and didn't get all the records I was expected, so am working now wirh SET PASS=ON. Also, do the files being made have to be in FOCUS format for the SET PASS command to work? I might be asking that question a few times also.

Mainframe FOCUS 7.0
VM/CMS and MVS/TSO

Posts: 250 | Registered: January 14, 2008

Ignored post by webmeister posted

Tony A

Expert

posted

wm,

If you can't get the SET ALL working for you then you could always rely upon the good old MATCH FILE processing using AFTER MATCH HOLD OLD.

One of the problems with JOIN and trying to get LEFT OUTER functionality is that if you specify selection on your lower order table, then that effectively does away with the ALL parm. Using MATCH will enable you to place selection criteria within each part of the request without that effect.

T

This message has been edited. Last edited by: Tony A,

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Guru

posted

Thanks, Tony,

I'll play around with Match and see if that does my trick.

I appreciate your reply.

Mainframe FOCUS 7.0
VM/CMS and MVS/TSO

Posts: 250 | Registered: January 14, 2008

Ignored post by webmeister posted

Expert

posted

FYI, the SET PASS=ON regards passwords on FOCUS databases, nothing to do with JOINs.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

PBrightwell

Master

posted

Webmeister,

What is the source of your data? If these are flat files the SET ALL=ON or PASS isn't going to work, I would use Tony's suggestion and do a MATCH. If they are IMS, IDMS, VSAM, or other hierarchial database are the fields you are joining on indexed? If it is DB2 can you create a view that does the join for you?

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes

Posts: 755 | Location: TX | Registered: September 25, 2007

Ignored post by PBrightwell posted

David Briars

Master

posted

Hi webmeister,

If I understand your situation correctly: You are reporting from FOCUS databases, on FOCUS 7.1.1 for z/OS. You and/or your staff, feel a little more comfortable with SQL then FOCUS. You need to join two data sources, using a LEFT OUTER JOIN.

If this is the case, you might want to consider using the SQL Translator.

For example, the following code:

  
DYNAM ALLOC DD VIDEOTRK DS TLA.FOCUS.VIDEOTRK SHR REUS
DYNAM ALLOC DD MOVIES   DS TLA.FOCUS.MOVIES   SHR REUS
-RUN
SQL
SELECT CUSTID,
       TRANSDATE,
       V.MOVIECODE,
       TITLE
FROM VIDEOTRK V
 LEFT OUTER JOIN MOVIES M
  ON V.MOVIECODE = M.MOVIECODE;
END

Yields:

  
CUSTID  TRANSDATE   MOVIECODE  TITLE
------  ---------   ---------  -----
0925    1991/06/27  001MCA     JAWS
0925    1991/06/27  692PAR     TOP GUN
0944    1991/06/21  505MGM     PHILADELPHIA STORY, THE
0944    1991/06/20  040ORI     BABETTE'S FEAST
1118    1991/06/26  710VES     FAMILY, THE
1118    1991/06/26  803WAR     DEATH IN VENICE
1133    1991/06/21  .          .
1133    1991/06/19  243MGM     MARTY
1133    1991/06/19  259MGM     MALTESE FALCON, THE
1237    1991/06/25  .          .

VIDEOTRK and MOVIES are two test database that comes with certain versions of FOCUS. (Anybody know what is this thing called 'video'?)

You, and your staff, can set the SQL Translator echo to on. The benefit would be that you would see the FOCUS TABLE command, generated by the SQL query. In this way, you could learn even more about the TABLE command.

Regards,
Dave

Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster

Posts: 822 | Registered: April 23, 2003

Ignored post by David Briars posted