Hi,

I would like to use the values stored in an Oracle table against the WHERE statement in a TABLE FILE request.

The table (stored the WHERE values) named PARAMVAL has 2 fields as transid and FLD1VAL.
This table is getting filled from a dot net screen and once read the webfocus has to delete the entries using a oracle package.

Suppose, it contains 3 records for transid EQ 1, as below.

1 ENGLAND
1 INDIA
1 AMERICA

-* passing from dot net
-DEFAULT &TRANSID ='1'

I can issue a TABLE FILE PARAMVAL and do a DM READ to get the FLD1VAL if it was only one record.Also , I can not create a join between the XX and PARAMVAL because as per the design I can not provide a common key field to link.The transid field will be passed from dot net and once I read the value , I need to delete all the records pertaining the particular transid.

TABLE FILE XX
PRINT *
WHERE FLD1 EQ '&FLD1'
END

Also , I know how it can be made using SQL pass through,

ENGINE SQLORA SET DEFAULT_CONNECTION ORATEST
SQL SQLORA PREPARE SQLOUT FOR
select * from xx where FLD1 in (select fld1val from PARAMVAL where transid ='&transid');

But as per the requirement,I am not supposed to use SQL passthrough.

Any suggestions on this?

Thanks and Regards,
Johney Joseph.

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

Generate the IN values and include them instead of using -READ

  
TABLE FILE CAR
SUM 
   COMPUTE INCOL/A20=''''||COUNTRY||''''||','; 
BY SEATS   NOPRINT
BY COUNTRY NOPRINT
WHERE SEATS EQ 2
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SELFILE FORMAT ALPHA
END
-RUN
TABLE FILE CAR
PRINT COUNTRY CAR MODEL
WHERE COUNTRY IN(
-INCLUDE SELFILE
'DUMMY SEL')
END

So what JG is suggesting is:
-DEFAULT &FLD1 ='1'
TABLE FILE XX
SUM COMPUTE INCOL/A20=''''||FLD1VAL||''''||',';
BY FLD1VAL NOPRINT
WHERE FLD1 EQ '&FLD1'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SELFILE FORMAT ALPHA
END
-RUN
TABLE FILE filename
verb fieldnames
WHERE test_fieldname IN(
-INCLUDE SELFILE
'DUMMY SEL')
END

How about:
TABLE FILE PARMVAL
PRINT FLD1VAL
IF TRANSID EQ &TRANSID
ON TABLE HOLD
END
TABLE FILE XX
PRINT *
IF FLD1 EQ (HOLD)
END

Thanks Daniel / Clinton,

Daniels code worked perfectly . Only one doubt, Can we not use WHERE instead of IF clause?. When I tried , it gave me error as

(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: HOLD

Clinton, your code gave me an error like

(FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: COUNTRY

I tried to print the SELFILE and it is printing as expected only.Could you please help me in correcting it.

Both of you gave me very good ideas, Thanks a lot for that.

Thanks and Regards,
Johney Joseph.

JG - I tried the Hold and -INCLUDE method you prescribed above but had the following error:

ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key SELFILE.fex.

Does this technique depend on the environment configuration ? Our iWay server is on the mainframe and the client on a Windows server.

Thank you for posting.
Best regards,
Sandeep Mamidenna

quote:

ERROR_MR_FEX_NOT_FOUND

BlueZone You're obviously in an MRE environment

Problem is the MRE is trying to include the code at parse time and not at run time

Try wrapping it with

-MRNOEDIT BEGIN

-MRNOEDIT END

Johney the syntax is slightly different for WHERE

IF FLD1 EQ (HOLD)

becomes

WHERE FLD1 IN FILE HOLD

That edit worked JG. Thank you.

Johney,

You can see by my use of IF that I have been using FOCUS for a long time, when WHERE did not exist. Acquired reflexes are hard to change...

Thanks JG .

quote:

TABLE FILE CAR
SUM
COMPUTE INCOL/A20=''''||COUNTRY||''''||',';
BY SEATS NOPRINT
BY COUNTRY NOPRINT
WHERE SEATS EQ 2
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SELFILE FORMAT ALPHA
END
-RUN
TABLE FILE CAR
PRINT COUNTRY CAR MODEL
WHERE COUNTRY IN(
-INCLUDE SELFILE
'DUMMY SEL')
END

This code gives me the error,

0 NUMBER OF RECORDS IN TABLE= 5 LINES= 2
0 ERROR AT OR NEAR LINE 13 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: COUNTRY
(FOC009) INCOMPLETE REQUEST STATEMENT

When I tried it with EQ with OR as below

COMPUTE INCOL/A20=''''||COUNTRY||''''||'OR';
......
......

PRINT COUNTRY CAR MODEL
WHERE COUNTRY EQ
-INCLUDE SELFILE
END

It gave me the error as

0 ERROR AT OR NEAR LINE 1 IN PROCEDURE SELFILE FOCEXEC *
(FOC255) COMPUTATIONAL ELEMENT IS TOO LONG: 'ENGLAND'OR
(FOC009) INCOMPLETE REQUEST STATEMENT

Any help to correct would be welcomed.

Thanks and Regards,
Johney Joseph

Johney, The code you posted back runs fine on 7.6.7 and should not have any problem in 7.1.1

Run the code with

 -SET &ECHO=ALL; 

and post the output between code tags so that we can see what is going on.

The reason for the second error is because you need spaces around the OR like ' OR '

Johney,

Add before your TABLE FILE
FILEDEF SELFILE DISK SELFILE.FEX

It is not advisable to INCLUDE something which is not a FEX

Daniel, there is no need for a FILEDEF as it's generated automatically because it's a HOLD file

JG,

The FILEDEF is used so that the generated HOLD file has a FEX extension

Doesn't need to be a .fex extension because the absolute path to the file is the working directory.

An INCLUDE must be named .fex if it resides in a location in the APP PATH
however if the file is in the working edatemp directory or it's full path is
specified it can be any extension.

The documentation on this is not very clear and contradictory.

JG,

You are right. Nonetheless, I have always found it is good programming practice.

BTW, I ran Johney's "quoted" code and it works fine, with and without the FILEDEF. So, either he is running something more than what is quoted or, maybe, in 7.1 he needs the FILEDEF...

quote:

FILEDEF SELFILE DISK SELFILE.FEX

Here is the output after ECHO is ON,

  
FILEDEF SELFILE DISK SELFILE.FEX
 TABLE FILE CAR
 SUM
 COMPUTE INCOL/A20=''''||COUNTRY||''''||',';
 BY SEATS   NOPRINT
 BY COUNTRY NOPRINT
 WHERE SEATS EQ 2
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS SELFILE FORMAT ALPHA
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        5  LINES=      2
 TABLE FILE CAR
 PRINT COUNTRY CAR MODEL
 WHERE COUNTRY IN(
 -INCLUDE SELFILE
 'ENGLAND',
 'ITALY',
 'DUMMY'
 )
 END
 0 ERROR AT OR NEAR LINE     16  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: COUNTRY
 (FOC009) INCOMPLETE REQUEST STATEMENT

Thanks and Regards,
Johney Joseph

quote:

The reason for the second error is because you need spaces around the OR like ' OR '

Thanks a lot GJ, The ' OR ' trick helped.

Johney this include issue is very strange as your code works perfectly on my systems

This line
WHERE COUNTRY IN(

try
WHERE COUNTRY IN (
and make sure there are no tab characters only spaces

I remember having several problems with tabs in 7.1

quote:

This line
WHERE COUNTRY IN(
try
WHERE COUNTRY IN (
and make sure there are no tab characters only spaces
I remember having several problems with tabs in 7.1

Thanks again GJ , your assumption was right. After the 'IN' I placed a space and it is working perfectly alright.

Thanks and Regards,
Johney Joseph.