Using a subselect in an open query
-
I'm having syntax error with the following openquery statement:
UPDATE OPENQUERY(BHOBHT01,
'Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
where UNIQUE_TRANS_NO IN
(select UNINO from MOMTRX_Pull)')
I'm getting
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'Y'.
I've tried:
UPDATE OPENQUERY(BHOBHT01,
"Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
where UNIQUE_TRANS_NO IN
(select UNINO from MOMTRX_Pull)")
Results:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
where UNIQUE_TRANS_NO IN
(select UNINO from MOMTRX_Pull)'.
I've tried:
UPDATE OPENQUERY(BHOBHT01,
'Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = "Y"
where UNIQUE_TRANS_NO IN
(select UNINO from MOMTRX_Pull)')
Results:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.
Use of four-part name does not work on this particular server
Update BHOBHT01..BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
where UNIQUE_TRANS_NO IN
(select UNIQUE_TRANSACTION_NUMBER from MOMTRX_Pull)
Results:
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e57].
HELP!!
-
Have you tried changing the Sub query to JOIN?
Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
From MOMTRX_Pull
where UNIQUE_TRANS_NO =MOMTRX_Pull.UNINO
-
Results from:
update openquery(BHOBHF02,'Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
From MOMTRX_Pull
where UNIQUE_TRANS_NO =MOMTRX_Pull.UNINO')
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Y'.
-
I think the string is breaking near Y so try changing it to "Y".
-
I've tried that also...see 4th try in original posting
-
I guess the syntax itself is wrong in your tries.. try this
update
openquery(LS01,'SELECT A.RECORD_PROCESSED FROM OPR..T_ERP_MOM_TRANS_OUTGOING A
INNER JOIN OPR..MOMTRX_Pull B ON A.UNIQUE_TRANS_NO =B.UNINO'
)
set
RECORD_PROCESSED = 'Y'
The only Change here I used database Name as 'OPR' and linked server as LS01
-
Results from:
update openquery(BHOBHT01,'SELECT A.RECORD_PROCESSED FROM BHDBO.T_ERP_MOM_TRANS_OUTGOING A
INNER JOIN db1_Porini_Rep.dbo.MOMTRX_Pull B ON A.UNIQUE_TRANS_NO =B.UNINO')
set RECORD_PROCESSED = 'Y'
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00933: SQL command not properly ended
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare:
repare returned 0x80040e14].T_ERP_MOM_TRANS_OUTGOING is on an Oracle 8.1.6 instance and MOMTRX_Pull is on SQL Server 2000.
-
Why can't you run the update directly in the openquery or using a linked server??
-
I assumed that both the tables are in linked server only.
ok try this.. this has to be run with the connection to the MOMTRX_Pull's Database
update
openquery(PAERSCBBLD0104,'SELECT UNIQUE_TRANS_NO,RECORD_PROCESSED FROM OPR..T_ERP_MOM_TRANS_OUTGOING')
set
RECORD_PROCESSED = 'Y'
WHERE
UNIQUE_TRANS_NO IN (SELECT UNINO FROM MOMTRX_Pull)
-
Thanks, this seems to work!
-
I am getting the same error today . Did you get any resolution on this?
here is the query..and the tale is in linked tables.
declare @query1 varchar(8000)
/* truncate table upon new load of inIRNs to be run */
--truncate table DUP_PHONE
/* populate the table */
insert into LEADSTATUSDM_STAGE.dbo.DUP_PHONE
Select * from openquery(Campusodsprd ,'
SELECT ph2.Parent_Oid AS PERSON_OID, PH.phone_number AS formatted_phone_number, ph2.Area, ph2.Phone_Number, ph2.Type,
ph.person_count, SYSDATE AS DATE_CREATED, ROW_NUMBER() OVER(PARTITION BY PH2.Parent_Oid, PH.Phone_Number
ORDER BY PH2.Type) AS PERSON_COUNTER
FROM (SELECT upper(trim(translate(ph.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1''))) AS phone_number,
upper(trim(translate(ph.Area, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1''))) AS area, COUNT(DISTINCT p.Oid)
AS person_count
FROM galaxy.Person p JOIN
galaxy.Phone ph ON ph.Parent_Oid = p.Oid AND ph.Phone_Number IS NOT NULL AND ph.Legacy_Type IN (''HM'', ''HM2'', ''CE'')
WHERE p.IRN IN (9027122388,9031824159,9032129480,9031929100,9032051753)
AND
length(translate(upper(ph.Phone_Number), ''qABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''q'')) >= 7 AND
LENGTH(TRIM(SUBSTR(upper(translate(ph.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1'')), 1, 1)
FROM upper(translate(ph.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1'')))) > 0
GROUP BY upper(trim(translate(ph.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1''))), upper(trim(translate(ph.Area,
''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1'')))) PH JOIN
galaxy.Phone ph2 ON upper(translate(ph2.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1'')) = ph.Phone_Number AND
upper(trim(translate(ph2.Area, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1''))) = ph.Area AND ph2.Legacy_Type IN (''HM'', ''HM2'', ''CE'')) PH
WHERE PH.PERSON_COUNTER = 1;
')
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply