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
Inline Dynamic SQ
 Login/Join
 
Member
posted
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!!
 
Posts: 21 | Location: Springfield, MA | Registered: August 17, 2004Report This Post
<RickW>
posted
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.
 
Report This Post
Member
posted Hide Post
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.
 
Posts: 21 | Location: Springfield, MA | Registered: August 17, 2004Report This Post
<RickW>
posted
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?
 
Report This Post
Member
posted Hide Post
Thanks again, Rick. I'll give it a try...
 
Posts: 21 | Location: Springfield, MA | Registered: August 17, 2004Report This Post
Member
posted Hide Post
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?
 
Posts: 21 | Location: Springfield, MA | Registered: August 17, 2004Report This Post
<RickW>
posted
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
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders