Hi,

I got a problem in running the Sql Server query in WebFocus. I have the query as follows
FOR EXAMPLE...

WITH PROD_INDICES
AS
(
SELECT 0 S, 1 E
UNION ALL
SELECT E, CHARINDEX('&&SEPARATOR', '&&ddlDrug', E) + LEN('&&SEPARATOR')
FROM PROD_INDICES
WHERE E > S
)

SELECT DDP.DOS_Q,
DDP.PROD_DSPN_Q,
DDPH.PSCB_DEA_I,
DDPH.PSCB_FRST_N, DDPH.PSCB_LAST_N,
DDPH.PTNT_FRST_N,
DDPH.PTNT_LAST_N from XXXX......;

TABLE FILE SQLOUT
PRINT *
END

---

I have used the SQL query along with the CTE common table Expression. Its runing fine in Sql server but in webfocus showing error as given below. Any suggestions or ideas please update me.


(FOC1400) SQLCODE IS 156 (HEX: 0000009C) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near
: the keyword 'WITH'. [42000] Statement(s) could not be prepared. [] Defer
: red prepare could not be completed.
L (FOC1405) SQL PREPARE ERROR.
.
Thanks all

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

Hi
I have ran into this type of thing before.Is your SQL Server adapter pointing to the SQL Server database per se. If not this may be the problem. It is difficult to say without having tried the code. The other thing is that WebFOCUS does not seen to like the schema/database name tacked on the query.
Perhaps we can take this one off line. I work with WebFocus and SQL Server on a daily basis.

Hi Here is the solution. Let me know how you fare.
Regards Steve,
Explanation:
"Common Table Expressions are a new feature of MS SQL2005. They are used to do recursive joins with multiple cursors.
Can you change the cursors settings on the adapter to Client. The default is Server."

-* File CTE.fex
ENGINE SQLMSS SET CURSORS CLIENT
SQL SQLMSS PREPARE SQLOUT FOR
WITH a AS
(
SELECT t1.FUND_ID, T2.ASSET_ID
FROM POS_SUM_DLY AS t1 INNER JOIN POS_SUM_DLY_HIST AS t2
ON t1.FUND_ID = t2.FUND_ID
AND t1.ASSET_ID = t2.asset_id
)
SELECT a.ASSET_ID, b.ASSET_ID
FROM a INNER JOIN POS_SUM_DLY_HIST AS b
ON a.FUND_ID =b.FUND_ID
AND a.ASSET_ID = b.asset_id;
END


TABLE FILE SQLOUT
PRINT *
WHERE RECORDLIMIT IS 10
END

Hi Stefaans

I have a question, i am actually trying to do the same myself and i have a CTE table however i dont want to hold it i just want to preform the CTE and then an update, how would you adjust the engine for that? i'm on webfocus 8 and sql 2008 r2 if that does make a difference?

example of the code is:

ENGINE SQLMSS SET DEFAULT_CONNECTION CMH_STAGING
SQL SQLMSS

;WITH temp (employee_number, earned_hours, workdate) AS
(select t1.employee_number,sum(t1.EARNED_HOURS),MAX(workdate) from STG_MT_Pa t1
LEFT OUTER join STG_EMP_ATT_Man t2
on t1.Employee_Number = t2.EMPLOYEE_NUMBER
where t1.EARNING = '401' and WORKDATE >= t2.benchmark_Date
or EARNING = '116' and WORKDATE > = t2.benchmark_Date
or EARNING='400' and WORKDATE >= t2.benchmark_Date
or EARNING='505' and WORKDATE >=t2.benchmark_Date
or EARNING='515' and WORKDATE >=t2.benchmark_Date
group by t1.EMPLOYEE_NUMBER
)

update STG_EMP_ATT_Man set earned_hours =
(
select SUM(temp.earned_hours) from temp
where STG_EMP_ATT_Man.employee_number=temp.employee_number
group by temp.employee_number
)

END