Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Problem when using the CTE ( Common table Expression) in Direct Sql Passthru

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Problem when using the CTE ( Common table Expression) in Direct Sql Passthru
 Login/Join
 
Gold member
posted
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,
 
Posts: 78 | Registered: January 07, 2008Report This Post
Silver Member
posted Hide Post
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.


WebFOCUS 7.7.01 Windows, TSO 7.0
 
Posts: 43 | Registered: April 18, 2007Report This Post
Silver Member
posted Hide Post
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


WebFOCUS 7.7.01 Windows, TSO 7.0
 
Posts: 43 | Registered: April 18, 2007Report This Post
Gold member
posted Hide Post
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


WebFocus 8.02, SQL Server 2008r2
 
Posts: 51 | Registered: November 30, 2012Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Problem when using the CTE ( Common table Expression) in Direct Sql Passthru

Copyright © 1996-2020 Information Builders