ca.portal.admin

Processing IDMS User-Defined Setname in SQL Server Linked Server

Discussion created by ca.portal.admin on Nov 16, 2008
Latest reply on Nov 16, 2008 by ca.portal.admin
The following SQL statement works in OCF where ""CSC-CMCSEV"" is a user-defin=
ed setname. The owner of the set is a Calc record with Calckey ""CSC_CASE_ID=
"".

SELECT *
FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
WHERE ""CSC-CMCSEV""
AND C.CSC_CASE_ID =3D 16

OCF 16.0 IDMS PAGE 1 LINE 1 DICT=3DQALDICT 1/86
SYST0005
SELECT *
FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
WHERE ""CSC-CMCSEV""
AND C.CSC_CASE_ID =3D 16
*+
+  CSC_CASE_ID  CMCSEV_CASE_EVENT_SEQ_NO  CMCSEV_CASE_EVENT_VERSION_NO + =
----------- ------------------------ ----------------------------
*+           16                         1                             1
*+
+ CMCSEV_OCOME_SORT_SEQ_ID  CMCSEV_SCHED_HEARING_DTE + ------------------=
------ ------------------------
*+                        1                  20080618


I have defined an SQL Server Linked Server using the ODBC provider:
Microsoft OLE DB Provider For ODBC Drivers and accessing a Data Source crea=
ted by the CA-IDMS ODBC Administration tool as a ""System""-type ODBC Data So=
urce using CA-IDMS ODBC driver. =20


The following SQL statement works in SQL Server Linked Server but returns t=
oo many record:

SELECT *
FROM [IDMS_DEV]..[NSQLCOURT].[CASE-C] C, [IDMS_DEV]..[NSQLCOURT].[C=
RIM-CASE-EVENT] B
WHERE C.CSC_CASE_ID =3D 16;

The following SQL statement gives me an error as expected since the setname=
""CSC-CMCSEV"" is not recognized by SQL Server Linked Server.

SELECT *
FROM [IDMS_DEV]..[NSQLCOURT].[CASE-C] C, [IDMS_DEV]..[NSQLCOURT].[C=
RIM-CASE-EVENT] B
WHERE ""CSC-CMCSEV""
AND C.CSC_CASE_ID =3D 16;

Msg 4145, Level 15, State 1, Line 4
An expression of non-boolean type specified in a context where a condition =
is expected, near 'AND'.

I am trying to investigate if SQL Server support SQL PASSTHRU option that d=
oes not edit the SQL Syntax and hence allow the setname to passthru.
I can't find any SQL Server Linked Server option that touches on SQL PASSTH=
RU

Does anyone know how to process setname in SQL Server Linked Serve?=20

Regards,
Paul Mak
Database Administrator - IDMS

EDS, an HP company

Applications Services, Data Engineering Capability - Sydney Level 3, 36-46 =
George Street, Burwood, NSW 2134, AUSTRALIA

Tel: +61 2 90125434
Fax: +61 2 90126612
Mobile: +61 419 398 116
E-mail: paul.mak@eds.com <mailTo:alex.leeflang@eds.com>=20

We deliver on our commitments so you can deliver on yours.
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Processing IDMS User-Defined Setname in SQL Server Linked Server
"Paul,
You can create a view in IDMS SQL catalog that encapsulates the set name. Then the SQL Server Linked Server won't even need to know about it. Example:

CREATE VIEW VIEWSCHM.CSC_CMCSEV_VIEW
AS
SELECT *
FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
WHERE ""CSC-CMCSEV""
WITH CHECK OPTION
;

Then you would code your select as follows:

SELECT *
FROM VIEWSCHM.CSC_CMCSEV_VIEW
WHERE CSC_CASE_ID = 16

Outcomes