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
July 08, 2009, 05:09 AM
atturhari
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
July 08, 2009, 05:13 AM
atturhari
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
July 08, 2009, 06:11 AM
P.P
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
July 08, 2009, 07:51 AM
atturhari
Increase the field size and check.
WF 7.7.02 on Windows 7 Teradata HTML,PDF,EXCEL,AHTML
July 08, 2009, 08:53 AM
P.P
I tried that too. No Luck
Webfocus 7.6.x Windows 7 HTML, PDF, Excel
July 08, 2009, 09:27 AM
Danny-SRL
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
July 08, 2009, 09:38 AM
P.P
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
July 08, 2009, 09:52 AM
Tony A
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
July 08, 2009, 01:11 PM
P.P
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
July 08, 2009, 01:39 PM
Francis Mariani
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
July 08, 2009, 02:11 PM
Glenda
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
July 08, 2009, 02:29 PM
P.P
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
July 08, 2009, 02:49 PM
Glenda
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
July 08, 2009, 04:22 PM
Darin Lee
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 )
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
July 08, 2009, 04:52 PM
Francis Mariani
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