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] Create and Populate and Array
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Create and Populate and Array
 Login/Join
 
Platinum Member
posted
I have a list of 200 employee numbers that I need to use as the where criteria for my report. Where I get in trouble is the left outer join in the final output, since these employee numbers do not already exist in a separate table.

On the final output, I want these employee numbers to appear as the first column, regardless of whether they are in the database table I am looking at with this criteria.

Employee #     Name
123            Bob
456            
789            Dan


I see in other threads that there is a feature to create and populate an array from a table, but is there a way to create and populate an array with custom values, and then use that array as the left side of a left outer join?

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


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I am a little confused as to thwt you mean by an array, as an array is usually something in memory. A JOIN only joins actual tables. And since you are wanting all of the values in your list to appear regardless of whether or not they appear in your other table, the list of employees MUST be on the LEFT of the join.
Your best bet would be to create a data and master file containing the list of your desired employee numbers and then join that to your other table with a left outer join.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I just don't have the WebFOCUS vocabulary to explain what I am trying to do.

I don't have the ability to create a new master file, so I was hoping that I could create a table that is local to the WebFOCUS query, and populate it with the employee numbers.

-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* Create List of Com Employees 
-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

FILEDEF cmstmas DISK 070COM.mas
-RUN
-WRITE cmstmas FILE=keyvalue, SUFFIX=FOC
-WRITE cmstmas SEGNAME=ROOT_SEG, SEGTYPE=S1, $
-WRITE cmstmas FIELD=070EMP, ALIAS=EMPID, FORMAT=A9, $
-RUN

CREATE FILE 070COM
-RUN
MODIFY FILE 070COM
DATA
12345
56789
END
-RUN

-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* Get EMPLOYEE_HR_INFO data 
-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

TABLE FILE EMPLOYEE_HR_INFO
PRINT
EMPLOYEE_HR_INFO.LAST_NAME AS 'LAST_NAME'
EMPLOYEE_HR_INFO.FIRST_NAME AS 'FIRST_NAME'

BY EMPLOYEE_HR_INFO.EMPID AS 'EMPID'

ON TABLE HOLD AS EHIBASE
END

-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* Join Final Data Back Together
-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
JOIN CLEAR *

JOIN
LEFT_OUTER
070COM.070EMP
IN 070COMTO ALL EHIBASE.EMPID
IN EHIBASE
AS J1
END

TABLE FILE 070COM
PRINT
070COM.070EMP
EHIBASE.LAST_NAME
EHIBASE.FIRST_NAME

ON TABLE PCHOLD FORMAT EXL2K
END


I know how to do this in MS Access, but not WebFOCUS.

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


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
CW, your vocabulary is just fine...
What you're doing looks almost reasonable... but you may be making it too hard .
You don't have to make a focus file inorder to do a join...you can join 2 flat files just fine, as long as the key field is the BY field in creating the 2 flat extracts.
YOur first file is a focus file, but without an indexed field, and there is no guarantee that the entries are in sequence...so lets just go with a flat file.
How are you generating this list of numbers?
Come back with that answer...
and we'll write a nice little macgyver-free chunk of code to get you that first list of empids...




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Susannah-

Thank you for your help. I have a bad habit of making things more difficult then they need to be, so any help you could provide would be appreciated.

My list if empids is a static list, but it does not reside in a table anwhere. It is literally a handwritten list on a piece of paper. So where I am stuck is getting those empids, within the query, into a format that can serve as the left side table of a left outer join.


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
If you cannot create the Master and the data-file, you could do something like this: Create a fex that you include into whichever program that you need it in. This fex will set up the file of employee numbers.

-*-- Create the master for Employee Numbers --------------------------
APP FI DATAMAST DISK 070COM.MAS
-RUN

-WRITE DATAMAST FILE=070COM, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=070EMP, ALIAS=EMPID, USAGE=A09, ACTUAL=A09, $

-*-- Create the data file for Employee Numbers -----------------------
FILEDEF 070COM DISK 070COM.FTM
-RUN

-WRITE 070COM 010012993
-WRITE 070COM 023200393
-WRITE 070COM 033884271
-WRITE 070COM 042390189
-WRITE 070COM 053826177


Make sure that the employees are sorted in the right order.

Next, create a hold file of the report data you require, reading whatever tables you need, sorting the data by employee.

Then join the Employee Numbers file to the hold file and run the final report.

Here's a working example using the CAR file:

-*-- Create the master for Countries ---------------------------------
APP FI DATAMAST DISK COUNTRY1.MAS
-RUN

-WRITE DATAMAST FILE=COUNTRY1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=COUNTRY1, ALIAS=EMPID, USAGE=A10, ACTUAL=A10, $

-*-- Create the data file for Countries ------------------------------
FILEDEF COUNTRY1 DISK COUNTRY1.FTM
-RUN

-WRITE COUNTRY1 CANADA
-WRITE COUNTRY1 ENGLAND
-WRITE COUNTRY1 FRANCE
-WRITE COUNTRY1 ITALY
-WRITE COUNTRY1 JAPAN
-WRITE COUNTRY1 S KOREA
-WRITE COUNTRY1 SWEDEN
-WRITE COUNTRY1 W GERMANY

-*-- Extract Sales information ---------------------------------------

TABLE FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS H001
END
-RUN

-*-- Print Sales information for all Countries------------------------

JOIN CLEAR *
JOIN COUNTRY IN COUNTRY1 TO COUNTRY IN H001 AS J1
-RUN

TABLE FILE COUNTRY1
SUM
SALES
BY COUNTRY1.COUNTRY
END
-RUN


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, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
frankie is so elegant! yep, just what he says.

Note carefully that in frankie's fex ,his BY field is carefully prefixed to be the host's value of COUNTRY... that's important.

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




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
susannah, thanks for that clarification - it's important.


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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
when I copy francis' code into a query, I get the error message:

 0 ERROR AT OR NEAR LINE     34  IN PROCEDURE writefiletest
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: COUNTRY1
 TABLE FILE COUNTRY1


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Try changing the APP FI command to a FILEDEF command:

FILEDEF DATAMAST DISK COUNTRY1.MAS


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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
So now my new code is:

-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* Create List of Comsat SSN's 
-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

APP FI DATAMAST DISK 070COM.MAS
-RUN

-WRITE DATAMAST FILE=070COM, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=070COM, ALIAS=EMPID, USAGE=A10, ACTUAL=A10, $

-*-- Create the data file for Employee Numbers -----------------------
FILEDEF 070COM DISK 070COM.FTM
-RUN

-WRITE 070COM 999999999


-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* Get EMPLOYEE_HR_INFO data 
-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

TABLE FILE EMPLOYEE_HR_INFO
PRINT
LAST_NAME

BY EMPLOYEE_HR_INFO.EMP_SSN

ON TABLE HOLD AS EHIBASE
END
-RUN

-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-* Join Final Data Back Together
-*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
JOIN CLEAR *

JOIN EMP_SSN IN 070COM TO EMP_SSN IN EHIBASE AS J1
-RUN


TABLE FILE 070COM
PRINT
EMP_SSN
LAST_NAME
EHIBASE.FIRST_NAME
END
-RUN


Which gives me the error message:

 0 NUMBER OF RECORDS IN TABLE=   215461  LINES= 215461
 0 ERROR AT OR NEAR LINE     38  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: 070COM
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: 070COM
 BYPASSING TO END OF COMMAND


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Have you tried what I suggested:

APP FI DATAMAST DISK 070COM.MAS

should be:
FILEDEF DATAMAST DISK 070COM.MAS


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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Yes. It gives me the same error message.


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Also,

EHIBASE.FIRST_NAME should be 070COM.EMP_SSN[CODE]
A more complete example with the CAR file:
 [CODE]-SET &ECHO=ON;

-*-- Create the master for Countries ---------------------------------
-*APP FI DATAMAST DISK COUNTRY1.MAS
FILEDEF DATAMAST DISK COUNTRY1.MAS
-RUN

-*--
-WRITE DATAMAST FILE=COUNTRY1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10, $

-*-- Create the data file for all Countries required in the report ---
-*-- These Countries may or may not be in the database being reported from
FILEDEF COUNTRY1 DISK COUNTRY1.FTM
-RUN

-WRITE COUNTRY1 ENGLAND
-WRITE COUNTRY1 CANADA
-WRITE COUNTRY1 S KOREA
-WRITE COUNTRY1 SWEDEN

-*-- Extract and merge all Countries ---------------------------------
-*-- Part 1 - extract Countries that exist in the database
TABLE FILE CAR
SUM
COUNTRY
BY COUNTRY
ON TABLE HOLD AS COUNTRY2 FORMAT ALPHA

-*-- Part 2 - merge Countries that may or may not exist in the database
MORE
FILE COUNTRY1
END
-RUN

-*-- Extract Sales information ---------------------------------------
TABLE FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

-*-- Join table of all Countries to table of extracted Sales information ---
JOIN CLEAR *
JOIN COUNTRY IN COUNTRY2 TO COUNTRY IN H001 AS J1
-RUN

-*-- Print Sales information for all Countries -----------------------
TABLE FILE COUNTRY2
SUM
SALES
BY COUNTRY2.COUNTRY
END
-RUN


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, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
CWM,

Since you are the one with the list of employees, you could put them in an EXCEL file, create an ODBC connection to that EXCEL file and then JOIN it to your existing table.

The subsequent TABLE issued to the EXCEL would retrieve all the employees from the EXCEL and the matching records, if the JOIN is 1-to-1. If your JOIN is 1-to-many, then use SET ALL=ON or SET ALL=PASS depending on your other WHERE statements.


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

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
What's your environment? If it's UNIX or LINUX you will need to use EDAPUT for creating the master file on the fly. Waz gave a great example of EDAPUT in FOCALPoint.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
i have UNIX and don't use EDAPUT, just -WRITE.
hmmm? what am i missing?




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Create and Populate and Array

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