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     [CLOSED] Hold File in Where Condition

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Hold File in Where Condition
 Login/Join
 
P.P
Member
posted
Hi,

I have a SubQuery in SQL which I am trying to do in Webfocus.

I want to explain with a small example. I have a SQL as follows

Select Empno,
Ename,
Sal
From Employee
Where Manager NOT IN(Select Manager from Employee)

I am trying to convert this SQL Query into Webfocus. So I did

TABLE FILE EMPLOYEE
PRINT MANAGER
ON TABLE HOLD AS HLD_MANAGER FORMAT ALPHA
END

TABLE FILE EMPLOYEE
PRINT
EMPNO
ENAME
SAL
WHERE MANAGER OMITS (HLD_MANAGER)
END

When I execute this I am getting the following error

(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH:
An IF or WHERE test compares an alphanumeric field against a value which is longer than the
field format.

Can any please suggest me what needs to be done if I want to use a HOLD file in Where Condition. I am not able to understand the error properly.

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


Webfocus 7.6.x
Windows 7
HTML, PDF, Excel
 
Posts: 16 | Location: Toronto, ON, Canada | Registered: June 24, 2009Report This Post
atturhari
Platinum Member
posted Hide Post
To use hold file in where condition,

WHERE MANAGER IN FILE HLD_MANAGER
WHERE NOT MANAGER IN FILE HLD_MANAGER (negation)


WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML
 
Posts: 165 | Registered: September 29, 2008Report This Post
atturhari
Platinum Member
posted Hide Post
if you still get any error then, follow this example to hold data in a hold file,

DEFINE FILE CAR
COUNTRY_X/A28 = '''' | COUNTRY | '''';
END

TABLE FILE CAR
PRINT
COUNTRY_X
ON TABLE HOLD AS TEMP_WHE FORMAT ALPHA
END


WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML
 
Posts: 165 | Registered: September 29, 2008Report This Post
P.P
Member
posted Hide Post
Hi Hari,

Thanks a lot.

I tried with NOT IN FILE also. I am getting the same error.

I tried the solution given by you using DEFINE. It also giving me the same error.

Actually, the field Manager is of Size A17. Is it something to do with field format?


Webfocus 7.6.x
Windows 7
HTML, PDF, Excel
 
Posts: 16 | Location: Toronto, ON, Canada | Registered: June 24, 2009Report This Post
atturhari
Platinum Member
posted Hide Post
Increase the field size and check.


WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML
 
Posts: 165 | Registered: September 29, 2008Report This Post
P.P
Member
posted Hide Post
I tried that too. No Luck Frowner


Webfocus 7.6.x
Windows 7
HTML, PDF, Excel
 
Posts: 16 | Location: Toronto, ON, Canada | Registered: June 24, 2009Report This Post
Danny-SRL
Virtuoso
posted Hide Post
P.P,

You are mixing up 2 syntaxes.

quote:
TABLE FILE EMPLOYEE
PRINT MANAGER
ON TABLE HOLD AS HLD_MANAGER FORMAT ALPHA
END

TABLE FILE EMPLOYEE
PRINT
EMPNO
ENAME
SAL
WHERE MANAGER OMITS (HLD_MANAGER)
END


Here is one solution that will work. I use the Focus EMPLOYEE file:
  
-* File PP1.fex
TABLE FILE EMPLOYEE
BY CURR_JOBCODE
WHERE CURR_SAL GT 20000
ON TABLE HOLD 
END
TABLE FILE EMPLOYEE
PRINT
LAST_NAME
CURR_SAL
BY EID
BY CURR_JOBCODE
IF CURR_JOBCODE NE (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
P.P
Member
posted Hide Post
Hi Danny,

Thanks a lot for the reply.

I tried with IF too. It is giving me the following error

0 ERROR AT OR NEAR LINE 105 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC017) THE NUMBER OF TEST CONDITIONS EXCEEDS THE MAXIMUM
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

My hold file is holding 296351 rows.

Do we have any limit on this?


Webfocus 7.6.x
Windows 7
HTML, PDF, Excel
 
Posts: 16 | Location: Toronto, ON, Canada | Registered: June 24, 2009Report This Post
Tony A
Expert
posted Hide Post
Yes, and your file breaks that limit and then some.

In your situation I would suggest resorting to MATCH or LEFT_OUTER JOIN both of which will provide you with a suitable method.

MATCH FILE EMPLOYEE
   BY DEPARTMENT
RUN
FILE EMPLOYEE
PRINT EMP_ID
      FIRST_NAME
      LAST_NAME
      CURR_SAL
   BY DEPARTMENT
AFTER MATCH HOLD AS OUTFILE NEW-NOT-OLD
END

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
P.P
Member
posted Hide Post
Hi Tony,

Thanks for the reply.

Is there any other way to achieve the Subquery scenario instead of creating Subquery in a Hold file and Using a Match/Join.

As I told, My Hold is holding 296351 rows and when I execute the hold file, it is taking 4mins. to complete the execution ,which is very long time.

Is there any other approach for the same?


Webfocus 7.6.x
Windows 7
HTML, PDF, Excel
 
Posts: 16 | Location: Toronto, ON, Canada | Registered: June 24, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
Why not just use SQL "passthru" if your SQL works well? You can always create a HOLD file and then design the report using the HOLD file.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Glenda
Guru
posted Hide Post
quote:
Select Empno,
Ename,
Sal
From Employee
Where Manager NOT IN(Select Manager from Employee)



Your code suggests that you want to print Empno, Ename, Sal from the Employee file where the Manager is "blank". Is this what you are trying to do?

If so then:

TABLE FILE Employee
PRINT
      ...
WHERE Manager EQ ''
END


If you are in fact using a completely different file in your subquery then try:

SQL
SELECT
       FILE1.Empno
     , FILE1.Ename
     , FILE1.Sal 
     , FILE2.Manager
FROM FILE1 LEFT JOIN FILE2 ON FILE1.Manager=FILE2.Manager
WHERE FILE2.Manager IS NULL;
END


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
P.P
Member
posted Hide Post
Hi,

I cannot use SQL Passthru. It is my requirement.

I have to report it from Focus code only.

Actually, above is just an example I have used to explain the scenario.

Actually , the subquery is returning 296351 rows.


Webfocus 7.6.x
Windows 7
HTML, PDF, Excel
 
Posts: 16 | Location: Toronto, ON, Canada | Registered: June 24, 2009Report This Post
Glenda
Guru
posted Hide Post
What exactly are you trying to do?

1) Select records from filename where the "manager" field is empty?

2) Select records from filename1 where the "manager" field does not exist in filename2?


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Darin Lee
Virtuoso
posted Hide Post
Backing up several steps--

If your subquery is returning almost 296K_ rows, there is not a way to use that hold file in a WHERE statement. The file exceeds the limits of that technique. The only way you'll get what you need is by doing a join, in which case Glenda is heading you down the right path.

Second thought - whether you use SQL or FOCUS, there is SQL being generated and passed to the DMBS so whether it's

SQL SQLMSS
Select FIELD from XFILE;
TABLE FILE SQLOUT
PRINT FIELD
END

OR

TABLE FILE XFILE
PRINT FIELD
END

the same thing is happening on the DBMS side resulting in identical output on the FOCUS side. Code is code. (unless you're tyring to edit it with Report Painter Roll Eyes )


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Code is code, except that you cannot write WebFOCUS code that will result in a SQL subquery...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report 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     [CLOSED] Hold File in Where Condition

Copyright © 1996-2020 Information Builders