I'm trying to write some SQL in my WebFocus program and I have a question. Is it possible to reference a file in a WHERE statement? I'm trying to do
WHERE fieldname IN (filename)
so I'd like to use the file (where the values would be located) instead of actually listing the values for the IN statement in the actual code. Can I do this and how?
Thanks!!!

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

WHERE fieldname IN FILE filename is WebFOCUS syntax. To be able to do this in SQL pass-through, your file must have alphanumeric values in single-quotes or numeric values and each value must be separated by a comma. If the file is generated by another program, concatenate the single-quotes and the comma to the alphanumeric values,

'ALBERTA',
'ONTARIO',

or the comma to the numeric values,

100,
8261,

Then in your SQL pass-through code, you can simply -INCLUDE filename:

SQL
SELECT
NAME, ADDRESS, PHONENUMBER
WHERE NAME IN (
-INCLUDE filename
' ')
WHERE NAME <> ' '
...

The last two lines of my code look a little odd, but the reason I have them there is if the file you're using in the -INCLUDE is generated from another program, then it will most likely have a comma at the end of each value, including the last one, which would cause a syntax error, hence I add a dummy value as the last value in the IN statement; the next line is there to eliminate the possiblility of actually retrieving this dummy value if it exists in the db. Sounds crazy, but it's a little better than coding the program that generates the file of values to ensure that the last value doesn't have the comma(!!!).

I hope this helps.

Queen,

We dynamically build WHERE statements in almost all of our focexecs. (We use SQL pass-through almost exclusively). One technique is to use -READ statements to build a list of & vars, then have DM code to build the WHERE statement.

...
...
HOLD AS XPARM FORMAT ALPHA
END

-RUN

-SET &TRECS = &LINES;
-RUN
-SET &TOTRECS = &LINES;
-ADDSECTIONS

-SET &CNTR = 0;
-LOOPITAO
-SET &CNTR = &CNTR+1;
-SET &LINE1 = '-IF &' || 'CNTR GT &' || 'TRECS GOTO STOPLOOPAO;';
&LINE1.EVAL
-READ XPARM &AOVALUE.A11.
-SET &FLDACOR.&CNTR = &AOVALUE ;
-SET &FLDACOR0 = &CNTR;
-GOTO LOOPITAO

-STOPLOOPAO  

The above code builds the list of & vars.

 
...
...
WHERE (<your field name> IN
  ( &FLDACOR1
-ADDSECTIONS
-IF &FLDACOR0 EQ 1 GOTO STOPLOOP;
-SET &CNTR=1;
-LOOPSQL
-SET &CNTR=&CNTR+1;
-SET &LINE1='-IF &' || 'CNTR GT &' || 'FLDACOR0 GOTO STOPLOOP;';
&LINE1.EVAL
-SET &LINE2= ',' || '&' | 'FLDACOR' | &CNTR.EVAL ;
&LINE2.EVAL
-GOTO LOOPSQL
-STOPLOOP
)
 

The above set of code dynamically builds the WHERE statement.

Hope this helps,

Kevin

The Dialog Manager -READ looping method is unnecessarily complicates the code, requiring two loops through the data. If the data contained the value separator required by SQL syntax (the comma) then all you would have to do is -INCLUDE the data file.

Francis,

Agreed, depending on environment...

Do you FILEDEF the location for the HOLD file? Is it in the APPPATH?

Kevin

If the HOLD file was created in the same WF session as the code that -INCLUDEs it, it does not require a FILEDEF, nor does it need to be in the APPPATH. If it was previously created, then it does require a FILEDEF or has to be in the APPPATH.

Cheers.

Further, if the values are actually being derived from another DB2 table, you could also use a sub-select SQL to get it all done in one fell swoop...

SQL DB2
SELECT
FLD1,FLD2
FROM DB1.TBL1
WHERE FLD3 IN (
SELECT FLD3 FROM DB1.TBL2
WHERE FLD9 BETWEEN 'XXX' AND 'YYY'
AND yada....yada...yada..
)
;
TABLE
ON TABLE HOLD AS HX1
END
-*

Sandeep Mamidenna

Francis, Does that SQL syntax work? I get an error:
(FOC1400) SQLCODE IS 907 (HEX: 0000038B)
: ORA-00907: missing right parenthesis
: Error context area: (-INCLUDE CRNS ' ')
L (FOC1405) SQL PREPARE ERROR.

FILEDEF CRNS DISK D:\SHARES\STUDENT\CRNS.TXT
-RUN
ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
SQL SQLORA PREPARE SQLOUT FOR
SELECT * FROM SSBSECT
WHERE ssbsect_term_code = '200901'
and SSBSECT_CRN IN (-INCLUDE CRNS ' ')
;
END
TABLE FILE SQLOUT
PRINT *
END
-EXIT

Rick,

The -INCLUDE is the WwebFOCUS part of the SQL statement. The code should look like this:

FILEDEF CRNS DISK D:\SHARES\STUDENT\CRNS.TXT
-RUN
ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
SQL SQLORA PREPARE SQLOUT FOR
SELECT * FROM SSBSECT
WHERE ssbsect_term_code = '200901'
and SSBSECT_CRN IN (
-INCLUDE CRNS
'');
END
TABLE FILE SQLOUT
PRINT *
END
-EXIT

As long as the -INCLUDEd file contains rows that would be valid SQL, e.g

'A',
'BIG',
'SSS',

Francis, It seems to want a fex:
ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key CRNS.fex.

FILEDEF CRNS DISK D:\SHARES\STUDENT\CRNS.TXT
-RUN
? FILEDEF CRNS
-*
ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
SQL SQLORA PREPARE SQLOUT FOR
SELECT * FROM SSBSECT
WHERE SSBSECT_TERM_CODE = '200901'
AND SSBSECT_CRN IN (
-INCLUDE CRNS
'');
END
TABLE FILE SQLOUT
PRINT *
END
-EXIT

Data file:
'11404',
'11470'

This works. needed -mrnoedit
Although I still get the error message about not being able to open a .fex, but it runs just fine.
(FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND: CRNS

It doesn't seem to matter it the data has single quotes or not.

I guess the other thing I did was take out the blank in the IN phrase. A user will be creating the data and that will just be a rule.
Thanks.

FILEDEF CRNS DISK D:\SHARES\STUDENT\CRNS.TXT
-RUN
? FILEDEF CRNS
-*
ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
SQL SQLORA PREPARE SQLOUT FOR
SELECT * FROM SSBSECT
WHERE SSBSECT_TERM_CODE = '200901'
AND SSBSECT_CRN IN (
-mrnoedit -INCLUDE CRNS
);
END
TABLE FILE SQLOUT
PRINT *
END
-EXIT

data
'11404',
'11470',
'11472',
'11473',
'11476'

Does D:\SHARES\STUDENT\CRNS.TXT exist? Is the D drive available to WebFOCUS?


A working example:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

TABLE FILE CAR
PRINT
COMPUTE AA/A13 = '''' || COUNTRY || ''',';
WHERE COUNTRY NE 'ENGLAND'
ON TABLE HOLD AS H001
END
-RUN

SQL
SELECT COUNTRY, CAR, MODEL, SALES FROM CAR
WHERE COUNTRY IN (
-INCLUDE H001
'');
END
-RUN

Thanks. even with yours I had to add the
-mrnoedit, and still get the error message. (FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND: H001
But it runs. Hmmm

When I run the code in MRE, it works correctly with the -MRNOEDIT:

-SET &ECHO='ON';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

TABLE FILE CAR
PRINT
COMPUTE AA/A13 = '''' || COUNTRY || ''',';
WHERE COUNTRY NE 'ENGLAND'
ON TABLE HOLD AS H001
END
-RUN

SQL
SELECT COUNTRY, CAR, MODEL, SALES FROM CAR
WHERE COUNTRY IN (
-MRNOEDIT -INCLUDE H001
'');
END
-RUN

In your case, the reason why the report runs though it cannot find the included file is that WebFOCUS just continues. Right-click the report result to view the WebFOCUS code that's executed. I'm not sure why you get the error (FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND: H001

Try adding a FILEDEF:

-SET &ECHO='ON';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

FILEDEF H001 DISK h001.fex
-RUN

TABLE FILE CAR
PRINT
COMPUTE AA/A13 = '''' || COUNTRY || ''',';
WHERE COUNTRY NE 'ENGLAND'
ON TABLE HOLD AS H001
END
-RUN

SQL
SELECT COUNTRY, CAR, MODEL, SALES FROM CAR
WHERE COUNTRY IN (
-MRNOEDIT -INCLUDE H001
'');
END
-RUN

Though this won't help with your original problem.

This message has been edited. Last edited by: Francis Mariani,

So you don't get the error message? It is not in the html output, or if you run from Dev Studio with message viewer on?

I'm on 768 and you are on 765.

The only issue I see is I think if it were to run in Report Caster it may not like the error message or it would always send out a notification. I don't anticipate this being a scheduled job.

So unless you can think of a way to tweak it more I'm happy.
Thanks much. This will be way cool.

Here are 4 steps showing the different behavior depending on whether a file extension is used or '-mrnoedit'. I couldn't set up the CAR file do show this for some reason.
Thanks Francis for your help.

-SET &ECHO=ON;
-*
-* 1. This works. No error messages
-*
-*FILEDEF CRNS DISK D:\SHARES\STUDENT\CRNS.TXT
-*-RUN
-*? FILEDEF CRNS
-*
-*ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
-*SQL SQLORA PREPARE SQLOUT FOR
-*SELECT * FROM SSBSECT
-*WHERE SSBSECT_TERM_CODE = '200901'
-*AND SSBSECT_CRN IN (
-* includes extension
-*-INCLUDE CRNS.TXT
-*'');
-*END
-*TABLE FILE SQLOUT
-*PRINT *
-*END
-*
-* 2. This does not work. Get:
-* ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key CRNS.fex. 
-*
-*FILEDEF CRNS DISK D:\SHARES\STUDENT\CRNS.TXT
-*-RUN
-*? FILEDEF CRNS
-*
-*ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
-*SQL SQLORA PREPARE SQLOUT FOR
-*SELECT * FROM SSBSECT
-*WHERE SSBSECT_TERM_CODE = '200901'
-*AND SSBSECT_CRN IN (
-*Missing .ext
-*-INCLUDE CRNS
-*'');
-*END
-*TABLE FILE SQLOUT
-*PRINT *
-*END
-*
-* 3. This works. But get FOC227 error message
-*
-*FILEDEF CRNS DISK D:\SHARES\STUDENT\CRNS.TXT
-*-RUN
-*? FILEDEF CRNS
-*
-*ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
-*SQL SQLORA PREPARE SQLOUT FOR
-*SELECT * FROM SSBSECT
-*WHERE SSBSECT_TERM_CODE = '200901'
-*AND SSBSECT_CRN IN (
-* No extension
-*-mrnoedit -INCLUDE CRNS
-*'');
-*END
-*TABLE FILE SQLOUT
-*PRINT *
-*END
-*
-* 4. This does not work. Get can not find fex error message, FOC227
-*
-*FILEDEF CRNS DISK D:\SHARES\STUDENT\CRNS.TXT
-*-RUN
-*? FILEDEF CRNS
-*
-*ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
-*SQL SQLORA PREPARE SQLOUT FOR
-*SELECT * FROM SSBSECT
-*WHERE SSBSECT_TERM_CODE = '200901'
-*AND SSBSECT_CRN IN (
-* inculdes extension
-*-mrnoedit -INCLUDE CRNS.TXT
-*'');
-*END
-*TABLE FILE SQLOUT
-*PRINT *
-*END