Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] DECLARE @
 Login/Join
 
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, 2010Reply With QuoteReport This Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2010Reply With QuoteReport This Post
Platinum Member
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: 8205
Dev: 8201M
Prod:8009
-********************
 
Posts: 226 | Location: Houston,TX | Registered: June 11, 2004Reply With QuoteReport This Post
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, 2010Reply With QuoteReport This Post
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, 2004Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
Platinum Member
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: 8205
Dev: 8201M
Prod:8009
-********************
 
Posts: 226 | Location: Houston,TX | Registered: June 11, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.