I'm trying to use SQL for some reports and am running into a strange hiccup. I am getting a save prompt in my browser rather than my HTML output as usual.

If I run this code I get HTML output in my browser like normal, like I want:

SQL
SELECT BODYTYPE, AVG(MPG), SUM(SALES)
FROM CAR
WHERE RETAIL_COST > 5000 AND BODYTYPE <> 'SEDAN'
GROUP BY BODYTYPE;
TABLE
HEADING
"AVERAGE MPG AND TOTAL SALES PER BODYTYPE"
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END


But if I try something very similar against a SQL table in our database (code below), I get a save prompt in my browser instead of my output. I even put the synonym in the same application folder on my reporting server to see if some setting there was causing the difference. Can't figure out why this is happening.

SQL
SELECT SITEKEY
FROM STYLE_DIMSITES;
TABLE
HEADING
"TEST OUTPUT"
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

Note: I made a test synonym for one of our tables, that's why it has the funny name STYLE_DIMSITES

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

Phil,
AS far as I know you can't use PCHOLD directly after your SQL code like you did (except for Focus files). You need to hold it first and then display it in the format you want from that holdfile.

Your code could look like this

SQL SQLMSS
-*change the engine if you using another DB!
SELECT SITEKEY
FROM REAL_TABLE_NAME;
TABLE
ON TABLE HOLD AS HOLD1 FORMAT XFOCUS
END
-*
TABLE FILE HOLD1
PRINT
   SITEKEY
HEADING
"TEST OUTPUT"
ON TABLE PCHOLD FORMAT HTML 
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE

Hope this works for you.

Thanks Dirk. This explains why it was working with the car file.
I was hoping I could mash the SQL output directly onto my WebFOCUS styling. Will go the holdfile route.

worked like a charm. thanks again for the clarification. I'm going to switch several reports over to SQL so that I can work more closely with my data architect on the query structure. this was the last step I needed to get working for that plan.

Hi Phil,

I'm glad my small contribution helped you solving your problem.
This is what this forum is all about. Helping each other!

You do not have to do a hold file. The following was done using dev studio. It does this using a prepare. Less IO in the end.

ENGINE SQLMSS SET DEFAULT_CONNECTION POLY
SQL SQLMSS PREPARE SQLOUT FOR
SELECT [UserID]
      ,[Sid]
      ,[UserType]
      ,[AuthType]
      ,[UserName]
  FROM [ReportServer].[dbo].[Users]
END
TABLE FILE SQLOUT
PRINT 
     UserID
     Sid
     UserType
     AuthType
     UserName
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
  

quote:

You do not have to do a hold file

dhagen,
As far as I know by using the PREPARE statement you also create a holdfile (sqlout)behind the screens, only you don't have to code it explicit.
I have tested this and the IO in both cases is the same!