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
[CLOSED] DECLARE @
 Login/Join
 
roy
Silver Member
posted
What is the syntax that will allow this declare to pass-through in SQL
Works in SQL Server Management Studio



-* File WHY IS THIS SO DIFFICULT IN FOCUS.fex
ENGINE SQLMSS SET DEFAULT_CONNECTION CON01
SQL SQLMSS PREPARE SQLOUT FOR

DECLARE @COL VARCHAR(MAX)
SELECT @COL = COALESCE(@COL + ' + ','') + 'COALESCE( MAX ('+ QUOTENAME(LABEL)+'),'''')'
FROM BUSINESSPROCESS
SELECT @COL AS [ALL BU PROCESS]

;
END
TABLE FILE SQLOUT
PRINT
*
END

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 37 | Registered: December 03, 2010Report This Post
Waz
Expert
posted Hide Post
I think you need a semicolon after the declare.

You also have two select statements, I don't think this is supported.

Is this returning a single recordset ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
What's @COL? A SQL parameter? What is the objective of this SQL?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
roy
Silver Member
posted Hide Post
The @COL is a SQL parameter. Unfortunately I have the ability to create stored procedures I just don’t have the permissions... therefore running the stored procedure is not an option. But a Temp Table works!
The intention is to create a dynamic pivot table query; this was just one chunk of stand alone SQL code required for the entire query... when this works then the entire query will work. It’s the ability to pass the declare that seams to be the stumbling block.
Maybe it’s just a syntax thing that someone can help me with in WEB FOCUS.
A static Pivot Table query would require maintenance as new categories are introduced into the database so this would not be ideal.

Please try this in SQL and change as required then try with WEB FOCUS.


DECLARE @COL VARCHAR(MAX)
SELECT @COL = COALESCE(@COL + ' + ','') + 'COALESCE( MAX ('+ QUOTENAME( any column name )+'),'''')'
FROM ( any SQL table )
SELECT @COL AS [ALL BU PROCESS]


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 37 | Registered: December 03, 2010Report This Post
vaayu
Guru
posted Hide Post
Could you try this and see if it works?
quote:
DECLARE @COL VARCHAR()
SELECT @COL = COALESCE(@COL + ' + ','') + 'COALESCE( MAX ('+ QUOTENAME( any column name )+'),'''')' AS TEST
FROM ( any SQL table );
END
TABLE FILE SQLOUT
PRINT
*
END



I have never used a DECLARE in WF SQL Pass thru but just looking at the sql I am just curious if such a simple sql like this would be ok

quote:

DECLARE @TESTCOUNT INT
SELECT @TESTCOUNT=COUNT(FIELDNAME) FROM TABLENAME;
END
TABLE FILE SQLOUT
PRINT
*
END




-********************
Sandbox: 8206.10
Dev: 8201M
Prod:8009
-********************
 
Posts: 289 | Location: Houston,TX | Registered: June 11, 2004Report This Post
roy
Silver Member
posted Hide Post
With all due respect only post something that works,
Create a simple sql declare query and if you get it to successfully pass-through then post something....


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 37 | Registered: December 03, 2010Report This Post
Glenda
Guru
posted Hide Post
quote:

With all due respect only post something that works,
Create a simple sql declare query and if you get it to successfully pass-through then post something....


That was uncalled for! Keep on saying things like that and people will quit trying to help you!


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
njsden
Virtuoso
posted Hide Post
Roy, you'd be better off opening a support case with IBI so you'll know for sure if what you need to achieve is feasible or not from WebFOCUS.

As for your last comment, I totally agree with Glenda. I don't think anybody here posts anything just to make others waste their time. Sometimes we cannot really test stuff (I, for instance, have no access to SQL Server) but have always felt free to give out ideas for the interested party to try out. If silence is a better answer for you in cases like those that's fine but as many other people are usually appreciative of the help they receive even if it doesn't always work, one would have not expected that someone would be bothered by:
quote:
Could you try this and see if it works?

At the end of the day, whoever answers here does it by taking time our of her/his own busy schedule with no other interest than to be supportive. Anyway, we know better now.

Make sure to open your case with tech support so they will provide the guidance you need.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
vaayu
Guru
posted Hide Post
@roy, Sorry I wasn't testing that when I posted. Were you able to get an answer else where? I just stumbled upon this thread again, I need to do something very similar now. Has anyone found a solution to use @DECLARE. May be someone can help me translate it here's my code trying to reset a counter in a table with sequential number.
DECLARE @i As int
SET @i = 0
UPDATE myTable
SET @i= SEQUENCE = @i+1
WHERE OWNER_ID='USER6'

I tried to do a sql pass thru but will not work in a WF request but works great SQL query in MSSQL Mgmt Studio.
Please help!!

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


-********************
Sandbox: 8206.10
Dev: 8201M
Prod:8009
-********************
 
Posts: 289 | Location: Houston,TX | Registered: June 11, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders