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,

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 ?

What's @COL? A SQL parameter? What is the objective of this SQL?

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]

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

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....

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!

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.

@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,