I need a little assistance ...

Many of my reports rely on inline SQL (for control and performance) to grab data from a MS SQL source. I then use WebFOCUS' power to layout the resulting report.

Trying to take this approach up another notch (Sorry, Emeril!), I am exploring ways to use dynamic SQL to run reports on a more ad-hoc basis.

For example, the following SQL will take in a number of user-chosen parameters to determine the number of rows to return (specific Tranche,RunID; all Tranches, RunIDs, etc.) The code builds a proper SQL string and then executes it.
====================================

DECLARE @DealID varchar(255)
DECLARE @TrancheName varchar(255)
DECLARE @RunID varchar(255)
DECLARE @RunDate datetime

SET @DealID = 'MADISON1'
SET @TrancheName = 'ALL'
SET @RunID = 'ALL'
SET @RunDate = '11/16/2005'

DECLARE @SQL varchar(6000)

SET @SQL = '
SELECT
ssr.TrancheName,
ssr.Cusip,
CONVERT(varchar(10),ssr.RunDate,101) AS ''RunDate'',
CASE LTRIM(RTRIM(ssr.RunID)) WHEN ''CL1'' THEN ''Cleanup'' ELSE LTRIM(RTRIM(ssr.RunID)) END AS ''RunID'',
ssr.DeltaVal AS ''Break ADR'',
ssr.PrinWrtDownVal,
ssr.PercentagePrinWrtDown,
ssr.TotColLoss,
ssr.PercentTotColLoss
FROM IntexCDO.dbo.stdStressRun ssr WITH (NOLOCK)
WHERE DealID = ''' + @DealID + ''''
IF @TrancheName = 'ALL'
SET @SQL = @SQL + '
AND LTRIM(RTRIM(TrancheName)) IN (SELECT LTRIM(RTRIM(TrancheName)) FROM IntexCDO.dbo.stdStressRun WITH (NOLOCK)
WHERE LTRIM(RTRIM(DealID)) = ''' + @DealID + ''' GROUP BY TrancheName)'
ELSE
SET @SQL = @SQL + '
AND LTRIM(RTRIM(TrancheName)) = ''' + @TrancheName + ''''

IF @RunID = 'ALL'
SET @SQL = @SQL + '
AND LTRIM(RTRIM(RunID)) IN (''CL1'', ''CL3'')'
ELSE
SET @SQL = @SQL + '
AND RunID = ''' + @RunID + ''''

SET @SQL = @SQL + '
AND MONTH(RunDate) = (SELECT MONTH(MAX(ssr2.RunDate))
FROM IntexCDO.dbo.stdStressRun ssr2 WITH (NOLOCK)
WHERE LTRIM(RTRIM(DealID)) = ''' + @DealID + '''
AND RunDate <= ''' + convert[varchar[10),@RunDate,101) + ''')
AND DAY(RunDate) = (SELECT DAY(MAX(ssr2.RunDate))
FROM IntexCDO.dbo.stdStressRun ssr2 WITH (NOLOCK)
WHERE LTRIM(RTRIM(DealID)) = ''' + @DealID + '''
AND RunDate <= ''' + convert[varchar[10),@RunDate,101) + ''')
AND YEAR(RunDate) = (SELECT YEAR(MAX(ssr2.RunDate))
FROM IntexCDO.dbo.stdStressRun ssr2 WITH (NOLOCK)
WHERE LTRIM(RTRIM(DealID)) = ''' + @DealID + '''
AND RunDate <= ''' + convert[varchar[10),@RunDate,101) + ''')'

SET @SQL = @SQL + '
ORDER BY RunID, ssr.TrancheName'

PRINT @SQL
EXEC(@SQL)
====================================
The query processor interprets the code/variables and creates the following SQL which is then executed:

SELECT
ssr.TrancheName,
ssr.Cusip,
CONVERT(varchar(10),ssr.RunDate,101) AS 'RunDate',
CASE LTRIM(RTRIM(ssr.RunID)) WHEN 'CL1' THEN 'Cleanup' ELSE LTRIM(RTRIM(ssr.RunID)) END AS 'RunID',
ssr.DeltaVal AS 'Break ADR',
ssr.PrinWrtDownVal,
ssr.PercentagePrinWrtDown,
ssr.TotColLoss,
ssr.PercentTotColLoss
FROM IntexCDO.dbo.stdStressRun ssr WITH (NOLOCK)
WHERE DealID = 'MADISON1'
AND LTRIM(RTRIM(TrancheName)) IN (SELECT LTRIM(RTRIM(TrancheName)) FROM IntexCDO.dbo.stdStressRun WITH (NOLOCK)
WHERE LTRIM(RTRIM(DealID)) = 'MADISON1' GROUP BY TrancheName)
AND LTRIM(RTRIM(RunID)) IN ('CL1', 'CL3')
AND MONTH(RunDate) = (SELECT MONTH(MAX(ssr2.RunDate))
FROM IntexCDO.dbo.stdStressRun ssr2 WITH (NOLOCK)
WHERE LTRIM(RTRIM(DealID)) = 'MADISON1'
AND RunDate <= '11/16/2005')
AND DAY(RunDate) = (SELECT DAY(MAX(ssr2.RunDate))
FROM IntexCDO.dbo.stdStressRun ssr2 WITH (NOLOCK)
WHERE LTRIM(RTRIM(DealID)) = 'MADISON1'
AND RunDate <= '11/16/2005')
AND YEAR(RunDate) = (SELECT YEAR(MAX(ssr2.RunDate))
FROM IntexCDO.dbo.stdStressRun ssr2 WITH (NOLOCK)
WHERE LTRIM(RTRIM(DealID)) = 'MADISON1'
AND RunDate <= '11/16/2005')
ORDER BY RunID, ssr.TrancheName
====================================

My dilemma now is trying to transfer this approach into the WF environment. As I wrote earlier, I've had success substituting amper variables in place of SQL "@" variables and enclosing the string and date vars within single quotes.

I am pulling my hair out trying to get this to interpret correctly in WF. Any suggestions??


Thanks!!

I think your going to have to use Dialogue Manager for dynamic values, passing these into the stored procedure, and use the SQLOUT as the source for report formatting. Guess I'm not sure why you'ld want to use dynamic SQL vars as opposed to DM vars if you're working from WF Developer.

For the vast majority of cases there is neglible performance hits by having WF generate optimized SQL or passing hand-coded SQL. Of course, if you write "bad" FOCUS code and generate table space scans than that's more of an issue of writing efficient FOCUS code versus the actual product - IMHO.

Thanks, Rick.

I need to be dynamic here only because the contents of the WHERE clause could change based on whether an 'ALL' or a specific value is selected by the client for a few columns.

I've taken the approach you suggested to run simpler queries (using DM vars), but it's the code *itself* that needs to be dynamic this time, not the values.

I think I see what you need.

I think you need to set a DM var to the actual SQL WHERE clause and substitute the DM var in the pass-thru SQL
like your SET @SQL = statement.

Something like this.
------------------------------------
-SET &WHERECLAUSE = IF &TAKEIT EQ 'ALL' THEN
- 'WHERE DealID = &DealID AND AND LTRIM(RTRIM(TrancheName)) IN (SELECT LTRIM(RTRIM(TrancheName)) FROM IntexCDO.dbo.stdStressRun WITH (NOLOCK)'
- ELSE 'WHERE BLAH BLAH ...';
------------------------------------


-DEFAULTS BeginDate='10/1/2004 02:10 PM', EndDate='12/31/2004 02:10 PM', DataConnection='PvtPlace_ST01B'
-SET &STOREDPROC = 'selRptStuff';
-RUN
-*
-* get base data
-*
SQL SQLMSS SET DATETIME ON
SQL SQLMSS SET CONVERSION LONGCHAR ALPHA
SQL SQLMSS SET SERVER &DataConnection

ENGINE SQLMSS
SELECT DealID, IssuerID

FROM tblDeal d WITH (NOLOCK)

&WHERECLAUSE.EVAL

GROUP BY d.IssuerID
;

TABLEF FILE SQLOUT
PRINT *
ON TABLE HOLD AS H1
END
-RUN

---------------
The .EVAL suffix is used in order for the vars values to be translated.

This is where -SET &ECHO=ALL; is your friend.

Myabe that's what you're looking for?

Thanks again, Rick. I'll give it a try...

Rick:

Well, it took a few days of interruptions, but I did get it to work. I just had to re-conceptualize treatment of inline code from a SQL mindset to WF and your suggestions helped. I created my WHERE clauses as DM vars before calling the SQL, then substituted them in when needed. Works like a charm!

Next time I'll just call you direct -- looks like we're in the same building, perhaps?

Yup - I'm working with Carol on the 6th floor - the Crystal Reports conversions into PDFs. Been doing a mix of straight FOCUS code and a bunch of SQL passthru stuff.

Rick White