Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WHERE caluse as a table column values

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] WHERE caluse as a table column values
 Login/Join
 
Gold member
posted
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,


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
<JG>
posted
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
 
Report This Post
Platinum Member
posted Hide Post
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


--------------------------------------------------------------------------------
prod: WF/AS 8.2.05; OmniGen;
In FOCUS since 1991
 
Posts: 104 | Location: United Kingdom | Registered: February 07, 2008Report This Post
Virtuoso
posted Hide Post
How about:
TABLE FILE PARMVAL
PRINT FLD1VAL
IF TRANSID EQ &TRANSID
ON TABLE HOLD
END
TABLE FILE XX
PRINT *
IF FLD1 EQ (HOLD)
END


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
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.


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Platinum Member
posted Hide Post
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


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report This Post
<JG>
posted
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
 
Report This Post
<JG>
posted
Johney the syntax is slightly different for WHERE

IF FLD1 EQ (HOLD)

becomes

WHERE FLD1 IN FILE HOLD
 
Report This Post
Platinum Member
posted Hide Post
That edit worked JG. Thank you.


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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...


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
Thanks JG .


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Gold member
posted Hide Post
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


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
<JG>
posted
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 '
 
Report This Post
Virtuoso
posted Hide Post
Johney,

Add before your TABLE FILE
FILEDEF SELFILE DISK SELFILE.FEX

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


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
<JG>
posted
Daniel, there is no need for a FILEDEF as it's generated automatically because it's a HOLD file
 
Report This Post
Virtuoso
posted Hide Post
JG,

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


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
<JG>
posted
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.
 
Report This Post
Virtuoso
posted Hide Post
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...


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
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


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
Gold member
posted Hide Post
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.


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
<JG>
posted
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
 
Report This Post
Gold member
posted Hide Post
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.


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WHERE caluse as a table column values

Copyright © 1996-2020 Information Builders