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

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>,

what is carSQL is that my database name ?

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

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

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>,

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

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

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

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

I am running web focus developer, locally on my laptop.

I am completly stuck. I have tried every thing and can not find any documentation to stear me of what could be wrong.
Thanks

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.

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

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?

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

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

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

thank you very much that is very helpfull i will try that now