[CLOSED] SQL WHERE clause via SQL passthrough

In FOCUS you can build the following statement

WHERE field IN FILE TEMP

where TEMP has a list of values to use against the field. This works great

IN SQL the WHERE statement can use a list also

WHERE field IN (val1,val2,val3)

Here is my question..
Is there a technique within FOCUS to pass a file that contains a list of values to SQL Pass through. It would look like this

WHERE field IN (TEMP)

and TEMP would contain val1, val2, val3

Thanks in advance

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

Posts: 19 | Location: Omaha, NE | Registered: July 23, 2003

posted

Rich,

I don't think waht you require is possible. If you look at the SQL generated by a FOCUS TABLE request that includes a WHERE IN FILE statement, you will se that it is translated as WHERE XXX IN ('qqq', 'www', 'eee'). As well, there does not appear to be any SQL syntax where you can have your selection values in a file. So I don't think FOCUS can generate the SQL WHERE clause like you want.

Regards,

Pietro.

posted

Rich,

You can use some DM commands to generate the appropriate SQL PASSTHROUGH query based on the values read from a file. This is a simple example:

1. The text file filter.txt (resides in the '$IBI_HOME/apps/tests' directory):
ENGLAND,$
FRANCE,$
JAPAN,$
2. The master file filter.mas:
FILE=FILTER,SUFFIX=COM
SEGNAME=FILTER, SEGTYPE=S01
FIELDNAME=COUNTRY, COUNTRY,A10,A10,$
3. The focexec procedure:
-*APP PATH tests
APP FI FILTER tests/filter.txt
-*
TABLE FILE FILTER
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-IF &RECORDS LE 0 THEN GOTO :FINISH;
-READ HOLD &NAME.A10.
SQL
SELECT COUNTRY FROM CAR
WHERE COUNTRY IN
(
'&NAME'
-REPEAT :LOOP FOR &CNT FROM 2 TO &RECORDS
-READ HOLD &NAME.A10.
,'&NAME'
-:LOOP
);
END
-:FINISH

Hope this helps
Grzegorz

Tewy

Platinum Member

posted

Hide Post

What you want to do is possible in Focus. You need to filedef to the file you have your values stored in (I've only ever used TXT files or FTM file created by Focus).

The syntax for your report is

IF fieldname IS (filename)

Note the IF statement has to go at the bottom of any WHERE statements you may be using. Don't put a semi-colon on the end of your IF statement. Your file can only have up to 1000 values in it.

In a similar vain you can do

IF fieldname IS-NOT (filename) etc

Regards

Tewy

Posts: 123 | Location: UK | Registered: October 09, 2003

Ignored post by Tewy posted

Show Post

n00b

Member

posted

Hide Post

Have there been any changes/enhancements to this methodology in the last 9 years? I need to do the same thing (pass values from a FOCUS file to a SQL where clause.

WebFOCUS 7.7.03
AIX 5.2
HTML, AHTML, PDF, EXCEL, XML

Posts: 2 | Location: Fort Worth, TX | Registered: April 29, 2009

Ignored post by n00b posted

Show Post

dojo

Silver Member

posted

Hide Post

Another method that has worked efficiently for me in a few applications is to create a SQL table with two columns; one, the userid of the user making the request AND two, the values from the file. Load the values, then you can do an inner join to the values and include the userID as part of the WHERE clause. Depending on your specific DBMS you can make the userID column a NUPI and do a DELETE FROM when you are finished and there would be no contention to clear out the temporary list of values for the next run. Hope that makes sense.

Linux/zVM/WF7.6.11

Posts: 29 | Location: Washington DC | Registered: June 06, 2008

Ignored post by dojo posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

[CLOSED] SQL WHERE clause via SQL passthrough

Focal Point | Privacy Statement | Terms of Service