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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Help with SQL
 Login/Join
 
<mikeciav>
posted
I am a brand new to web focus and i am trying to develop reports that require SQL UNION joins. I can not seem to get the reports to run when i manually type in the SQL command. I get a "Error parsing report request"
>SQL<
SQL SQLMSS
error?
help i am so stuck i dont know what to do.
Thanks


This is the code I have in the "SHOW SOURCE VIEW" window.

SQL SQLMSS
SELECT * FROM NOR_CASE_DATA;
TABLE
ON TABLE HOLD AS CASE_DATA
END

TABLE FILE CASE_DATA
PRINT CASENUM

END
 
Report This Post
Expert
posted Hide Post
Try the following syntax:
SET SQLENGINE=SQLMSS
RUN
SQL
SELECT MAX(MODEL)
FROM WF_REPOS.DBO.CARSQL;
TABLE
ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
RUN

This message has been edited. Last edited by: <Mabel>,
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<mikeciav>
posted
what is carSQL is that my database name ?
 
Report This Post
<mikeciav>
posted
Here is what i have now:

SET SQLENGINE = SQLMSS
SQL
SELECT CASENUM
FROM SW_NOD.DBO.NOR_CASE_DATA;
TABLEON
TABLE HOLD AS HOLD01
FORMAT ALPHA
END
and all i get is error parsing report request?
what does all this mean?
thanks
 
Report This Post
<mikeciav>
posted
after cleaning up what you sent i posted this code in and i get the same kinda of error:
ERROR :
>SET<
SET SQLENGINE = SQLMSS

This is the code:
thanks

SET SQLENGINE = SQLMSS
SQL
SELECT CASENUM
FROM SW_NOD.DBO.NOR_CASE_DATA;
TABLE ON
TABLE HOLD AS HOLD01
FORMAT ALPHA
TABLE FILE HOLD01
PRINT
CASENUM
 
Report This Post
Expert
posted Hide Post
SW_NOD.DBO.NOR_CASE_DATA is schema.user.table
Check the WebFOCUS syntax, particularly the required END statements
SET SQLENGINE = SQLMSS
SQL
SELECT CASENUM
FROM SW_NOD.DBO.NOR_CASE_DATA;
TABLE <br />ON TABLE HOLD AS HOLD01 FORMAT ALPHA <br />END<br />TABLE FILE HOLD01
PRINT <br />CASENUM 
END

This message has been edited. Last edited by: <Mabel>,
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<mikeciav>
posted
I have know idea what is going on. It doesnt seem to reconize and thingbut TABLE in the first line.
this is the exact syntax i have from the first line to the last: and i get the same error
>SET<
SET SQLENGINE = SQLMSS
it doesnt even seem like it is looking at the whole syntax.


SET SQLENGINE = SQLMSS
SQL
SELECT CASENUM
FROM SW_NOD.DBO.NOR_CASE_DATA;
TABLE
ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
TABLE FILE HOLD01
PRINT
CASENUM
END
 
Report This Post
Silver Member
posted Hide Post
After your SQL statement, change your TABLE statement to "TABLE FILE SQLOUT" and add the "Print" statement too. SQL statement data go into the SQLOUT table automatically.

SET SQLENGINE = SQLMSS
SQL
SELECT CASENUM
FROM SW_NOD.DBO.NOR_CASE_DATA;
TABLE FILE SQLOUT
PRINT CASENUM
ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
TABLE FILE HOLD01
PRINT
CASENUM
END

Hope this helps.

Raelene
 
Posts: 31 | Location: Oklahoma City, OK | Registered: September 11, 2003Report This Post
<mikeciav>
posted
Guys i appreciate all of your help. BUT for some reason i get the error above on the first line, every time i run this. Where should i be pasting this code into, i have it pasted into the "Show Source View" from the report painter?
Is that right?
Also is there some setting I need to put on in order for web focus to know i calling a SQL statement.
I am extremly new to web focus and and just trying to get this to work.

If this does not work can i put my SQL statement in a Stored procedure and try that?
if so How do i do that?

Once again thank you so much
 
Report This Post
Guru
posted Hide Post
What is your environment? Where is the database? ie do you have a hub-sub situation going on here?
If you do, you will have to direct the code to the proper remote server.

REMOTE DEST=subservername
-REMOTE BEGIN
sql stuff
and table file SQLOUT
-REMOTE END
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
<mikeciav>
posted
I am running web focus developer, locally on my laptop.
 
Report This Post
<mikeciav>
posted
I am completly stuck. I have tried every thing and can not find any documentation to stear me of what could be wrong.
Thanks
 
Report This Post
Expert
posted Hide Post
Before worrying about SQL, have you attempted to test your connectivity via WebFOCUS to your database?

Have you created the WebFOCUS meta-data for NOR_CASE_DATA? This is done via the WebFOCUS console.

Have you written a simple WebFOCUS report to read NOR_CASE_DATA?

If you cannot create the meta-data, there is most likely a problem with connectivity or configuration.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<mikeciav>
posted
thank you frank that was one of my issues i was not talking to the correct dataadapter. Thank you very much.
Now i can get to the original question, regarding UNION statement, in my SQL. I have created a SQL statement with a UNION join and i need to display the data. but i get a error when i run this statement
'******************************8
-* File FFF.fex
-* Default Mode: ResourceLayout
-* File D.fex
SET SQLENGINE = SQLMSS
SQL
SELECT CASENUM AS 'CASE NUMBER',
'BRANCH LAN' AS 'REQUEST TYPE',
BRCH_LAN_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE BRCH_LAN_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'CMSI' AS 'REQUEST TYPE',
CMSI_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE CMSI_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'MISC LAN' AS 'REQUEST TYPE',
MISC_LAN_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE MISC_LAN_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'MAINFRAME' AS 'REQUEST TYPE',
MF_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE MAINFRAME_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'VPNCMO' AS 'REQUEST TYPE',
VPNCMO_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE REMOTE_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'QA' AS 'REQUEST TYPE',
QA_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE QA_APPR = 'DENIED';
TABLE FILE SQLOUT
PRINT
'CASE NUMBER'
'REQUEST TYPE'
'DENIAL REASON'
ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
TABLE FILE HOLD01
PRINT
'CASE NUMBER'
'REQUEST TYPE'
'DENIAL REASON'
END

does any thing jump out at anyone why this would not work.
Thanks
 
Report This Post
Expert
posted Hide Post
If you have access to MS SQL Server ENterprise Manager, try running the raw SQL to see if the error is there.

What is the error you are getting?
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<mikeciav>
posted
Thanks for the reply this is the error i get:
The union works and runs in SQL server.


0 ERROR AT OR NEAR LINE 3 IN PROCEDURE SQLOUT FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: CASE NUMBER
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
0 ERROR AT OR NEAR LINE 48 IN PROCEDURE FFF-1 FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD01
BYPASSING TO END OF COMMAND
 
Report This Post
<mikeciav>
posted
Thanks every one for all your help i got it:
This is the final code, i just had to say print *. AWSOME
-**********************************************8
-* File FFF.fex
-* Default Mode: ResourceLayout
-* File D.fex
SET SQLENGINE = SQLMSS
SQL
SELECT CASENUM AS 'CASE NUMBER',
'BRANCH LAN' AS 'REQUEST TYPE',
BRCH_LAN_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE BRCH_LAN_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'CMSI' AS 'REQUEST TYPE',
CMSI_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE CMSI_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'MISC LAN' AS 'REQUEST TYPE',
MISC_LAN_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE MISC_LAN_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'MAINFRAME' AS 'REQUEST TYPE',
MF_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE MAINFRAME_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'VPNCMO' AS 'REQUEST TYPE',
VPNCMO_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE REMOTE_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'QA' AS 'REQUEST TYPE',
QA_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE QA_APPR = 'DENIED';
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
TABLE FILE HOLD01
PRINT
*
END
-* File FFF.fex
-* Default Mode: ResourceLayout
-* File D.fex
SET SQLENGINE = SQLMSS
SQL
SELECT CASENUM AS 'CASE NUMBER',
'BRANCH LAN' AS 'REQUEST TYPE',
BRCH_LAN_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE BRCH_LAN_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'CMSI' AS 'REQUEST TYPE',
CMSI_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE CMSI_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'MISC LAN' AS 'REQUEST TYPE',
MISC_LAN_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE MISC_LAN_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'MAINFRAME' AS 'REQUEST TYPE',
MF_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE MAINFRAME_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'VPNCMO' AS 'REQUEST TYPE',
VPNCMO_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE REMOTE_APPR = 'DENIED'
UNION
SELECT CASENUM AS 'CASE NUMBER',
'QA' AS 'REQUEST TYPE',
QA_REASON AS 'DENIAL REASON'
FROM dbo.NOR_CASE_DATA
WHERE QA_APPR = 'DENIED';
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
TABLE FILE HOLD01
PRINT
*
ON TABLE HOLD AS ITEM1 FORMAT HTMTABLE
END
-HTMLFORM FFF-1
 
Report This Post
Expert
posted Hide Post
All the AS statements in the SQL should have column names without blanks in them. e.g.:SELECT CASENUM AS 'CASE_NUMBER'.

FOCUS does not like blanks in column names.

Afterwards, you can print a report with an AS statement:

TABLE FILE HOLD01
PRINT CASE_NUMBER AS 'Case Number'
END
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<mikeciav>
posted
thank you very much that is very helpfull i will try that now
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders