ODBC stage doesn't run valid SQL statement

[nnes]

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?

[jwiles]

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,

[nnes]

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)

[jwiles]

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.

Code: Select all

FROM fix_hra

in the SELECT statement becomes

Code: Select all

FROM (select ID,convert(varchar(255),NAME) NAME, version,CONVERT(xml,meta_data) META_DATA from HRA)

then remove the 'with' statement (the first line).

Regards,

[nnes]

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)

[suse_dk]

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.