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,

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.

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,

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

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.

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

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,

susannah, thanks for that clarification - it's important.

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

Try changing the APP FI command to a FILEDEF command:

FILEDEF DATAMAST DISK COUNTRY1.MAS

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

Have you tried what I suggested:

APP FI DATAMAST DISK 070COM.MAS

should be:

FILEDEF DATAMAST DISK 070COM.MAS

Yes. It gives me the same error message.

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

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.

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.

i have UNIX and don't use EDAPUT, just -WRITE.
hmmm? what am i missing?