DSXchange

I have this SQL statement that runs fine using the SQL Server client:

with fix_hra (id, name, version, META_DATA) as (select ID,convert(varchar(255),NAME) NAME, version,CONVERT(xml,meta_data) META_DATA from HRA)
SELECT
ID,
NAME,
Version,
Q.value('(@id)[1]', 'varchar(9)') AS 'QuestionID',
Q.value('(@type)[1]', 'varchar(40)') AS 'QuestionType',
Q.value('(@conceptid)[1]', 'varchar(9)') AS 'QuestionConceptID',
Q.value('(@value)[1]', 'varchar(255)') AS 'QuestionValue',
OPT.value('(@id)[1]', 'varchar(9)') AS 'OptionID',
OPT.value('(@value)[1]', 'varchar(255)') AS 'OptionValue'
FROM fix_hra
CROSS APPLY META_DATA.nodes('HRA/PAGE/CONDITION/QUESTION') AS QS(Q)
CROSS APPLY QS.Q.nodes('./OPTIONS/OPTION') AS OPTS(OPT)

but when I copy it into the ODBC stage I get this error:

Incorrect syntax near keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I tried putting a semicolon in front of the statement but then I get error:
Null argument: pDataSetDef

Has anybody dealt with this problem before?

You're seeing a difference in T-SQL (SQL Server's SQL implementation) vs ODBC SQL. The 'with' statement in T-SQL is essentially creating a macro for use in the SELECT statement.

Replace fix_hra in the SELECT statement with the logic after the 'as' keyword in the with statement and then have only the modified SELECT statement in your ODBC stage.

Regards,

Thanks jwiles. I am struggling a bit to follow your explanation. It is probably better if you show me some example SQL of what you mean. I tried this and get message

"Incorrect syntax near 'fix_hra'"


with (select ID,convert(varchar(255),NAME) NAME, version,CONVERT(xml,meta_data) META_DATA from HRA) fix_hra
SELECT
ID,
NAME,
Version,
Q.value('(@id)[1]', 'varchar(9)') AS 'QuestionID',
Q.value('(@type)[1]', 'varchar(40)') AS 'QuestionType',
Q.value('(@conceptid)[1]', 'varchar(9)') AS 'QuestionConceptID',
Q.value('(@value)[1]', 'varchar(255)') AS 'QuestionValue',
OPT.value('(@id)[1]', 'varchar(9)') AS 'OptionID',
OPT.value('(@value)[1]', 'varchar(255)') AS 'OptionValue'
FROM fix_hra
CROSS APPLY META_DATA.nodes('HRA/PAGE/CONDITION/QUESTION') AS QS(Q)
CROSS APPLY QS.Q.nodes('./OPTIONS/OPTION') AS OPTS(OPT)

Replace fix_hra in the SELECT statement with the logic after the 'as' keyword in the with statement and then have only the modified SELECT statement in your ODBC stage.

in the SELECT statement becomes
then remove the 'with' statement (the first line).

Regards,

I also tried to run the query from a stored procedure and get this error instead:

ODBC function "SQLFetch" reported: SQLSTATE = 24000: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver]Invalid cursor state (CC_OdbcDBStatement::dbsFetch, file CC_OdbcDBStatement.cpp, line 1,429)

You can use the WITH clause in the ODBC connector, for instance this CTE works...

WITH Emp_CTE AS (
SELECT EmployeeID, ManagerID, Title, EmployeeID AS HighParent, 'Y' AS HighestLevel, 0 AS Level
FROM dbo.MyEmployees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.Title, ecte.HighParent, 'N' AS HighestLevel, Level + 1
FROM dbo.MyEmployees e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE OPTION (MAXRECURSION 5)

Another option could be to use a view for your SQL statement, and just perform a SELECT .... FROM view from the ODBC Connector stage within DataStage.