Ayuda para parametrizar un OPENQUERY

Ruisu · #1 (**permalink**) 28/08/2008, 08:36

Quiero optimizar una consulta a un servidor ISeries, tengo en mi server una tabla con datos X y los quiero poner como parametro como subconsulta dentro del OPENQUERY.
Ejemplo de como pensé hacerlo, que esta mal?

DECLARE @CONTENEDORAS VARCHAR(12)
SET @CONTENEDORAS = (SELECT CAST(ETIQUETACONTENEDORA AS CHAR(12))
AS CONTENEDORA FROM REC_RECLAMOS)
SELECT *
FROM OPENQUERY(CURRENTWMS,'SELECT BPD_WFNAME AS "USUARIO",
BCH_NROCNT AS "CONTENEDORA",
BCH_CODLOC AS "LOCAL",
BPD_WFCASE AS "CAJAS",
BCH_FECENT AS "FECHA"
FROM FILELIB.BWPRDDIA T1 INNER JOIN FILELIB.BWCNTH01 T2
ON BPD_NROASI=BCH_NROASG AND BPD_WFASID=DIGITS(DECIMAL(BCH_CODLOC, 5, 0))
AND T1.CODFAC=T2.CODFAC AND T1.CODWHS=T2.CODWHS AND T1.WFACTD+19000000=T2.FECCRE
AND DIGITS(BCH_NROCNT) IN '' @CONTENEDORAS '' ')

Andres95 · #2 (**permalink**) 28/08/2008, 09:38

La funcion openquery no acepta parametros..(puedes confirmarlo en la ayuda de sql server).

la sintaxis del select deberia ser

in (item1, item2, etc )

tendrias que armar el listado de esa forma en un varchar y ejecutarlo en un sql dinamico.

algo como

execute (@miquery)

Saludos!

Ruisu · #3 (**permalink**) 28/08/2008, 10:05

Es posible concatenar una tabla que tengo en mi server dentro de la consulta para asi solo comparar el campo que quiero sin tener que cargar toda la base de datos y adjuntarlo fuera... nose si me explico bien...

Andres95 · #4 (**permalink**) 28/08/2008, 10:17

si puedes hacer joins con el openquery, pero no seria optimo..ya que te traerias toda la tabla desde db2 para compararla contra tu base en sql server...(a menos que sepas que siempre traera pocos registros tu openquery)

lo mejor seria construir la cadena y ejecutarla...

Saludos!!

Ruisu · #5 (**permalink**) 28/08/2008, 10:37

sip, me entendiste ese es el problema la base de datos es inmensa...
ahora, como hago la cadena?

Andres95 · #6 (**permalink**) 28/08/2008, 11:44

trabaje un buen tiempo interactuando con un sistema en DB2 desde mi sistema en SQL Server, jejeje

seria algo mas o menos asi...

ya solo tendrias que ajustar detalles de sintaxis que pudieran generarse..

Código:

DECLARE @CONTENEDORAS VARCHAR(8000), @sqlQuery varchar(8000)


SET @CONTENEDORAS = ''
Set @sqlQuery = ''

SELECT @CONTENEDORAS = @CONTENEDORAS + '''' + CAST(ETIQUETACONTENEDORA AS CHAR(12)) + ''','
FROM REC_RECLAMOS

if len(@CONTENEDORAS) > 0
	Set @CONTENEDORAS = SUBSTRING(@CONTENEDORAS, 1, len(@CONTENEDORAS)-1)   

--- con un print hay que validar que esta variable sea una lista valida 
--- de items varchar, algo como  'a','b','c'
-- Ademas hay que validar que la concanetacion no sobrepase los 8000 caracteres
-- sino quedara trunca y hara que truene el execute



Set @sqlQuery = @sqlQuery + ' SELECT * FROM OPENQUERY(CURRENTWMS, '
Set @sqlQuery = @sqlQuery + ' ''SELECT BPD_WFNAME AS [USUARIO], '
Set @sqlQuery = @sqlQuery + ' BCH_NROCNT AS [CONTENEDORA], '
Set @sqlQuery = @sqlQuery + ' BCH_CODLOC AS [LOCAL], '
Set @sqlQuery = @sqlQuery + ' BPD_WFCASE AS [CAJAS], '
Set @sqlQuery = @sqlQuery + ' BCH_FECENT AS [FECHA] '
Set @sqlQuery = @sqlQuery + ' FROM FILELIB.BWPRDDIA T1 INNER JOIN FILELIB.BWCNTH01 T2  '
Set @sqlQuery = @sqlQuery + ' ON BPD_NROASI=BCH_NROASG AND BPD_WFASID=DIGITS(DECIMAL(BCH_CODLOC, 5, 0)) '
Set @sqlQuery = @sqlQuery + ' AND T1.CODFAC=T2.CODFAC AND T1.CODWHS=T2.CODWHS AND T1.WFACTD+19000000=T2.FECCRE '
Set @sqlQuery = @sqlQuery + ' AND DIGITS(BCH_NROCNT) IN ( '

-- Para evitar que se sobrepasen los 8000 caracteres en sqlquery no concatenaremos contenedoras
-- se ejecuta directo

print @sqlQuery + @CONTENEDORAS + ') '') '

-- igual, primero imprime la cadena y ajusta lo necesario para que esa cadena impresa se pueda
-- ejecutar sin problemas de sintaxis desde el query analizer..y ya despues agregas el execute...

Execute (@sqlQuery + @CONTENEDORAS + ') '') ' )

Saludos! espero te sirva...

Ruisu · #7 (**permalink**) 28/08/2008, 11:46

uufff.. voy a analizarlo, aplicarlo y te cuento

gracias!!

Ruisu · #8 (**permalink**) 16/09/2008, 15:25

mmmh.. me salio este error, pero no capto que significa:

SELECT * FROM OPENQUERY(CURRENTWMS, 'SELECT BPD_WFNAME AS [USUARIO], BCH_NROCNT AS [CONTENEDORA], BCH_CODLOC AS [LOCAL], BPD_WFCASE AS [CAJAS], BCH_FECENT AS [FECHA] FROM FILELIB.BWPRDDIA T1 INNER JOIN FILELIB.BWCNTH01 T2 ON BPD_NROASI=BCH_NROASG AND BPD_WFASID=DIGITS(DECIMAL(BCH_CODLOC, 5, 0)) AND T1.CODFAC=T2.CODFAC AND T1.CODWHS=T2.CODWHS AND T1.WFACTD+19000000=T2.FECCRE AND DIGITS(BCH_NROCNT) IN ( '102000972744','105000870481','105000877178','1050 00877178','105000877178','105000877178','105000877 178','105000877178','107000844485','101000957288', '101000957289','101000858731','101000957388','1010 00958056','101000957289','101000851546','102000970 789','102000972917','102000972917','102000972917', '102000973907','101000957194','101000957194','1020 00972917','102000972917','102000972917','102000972 917','107000842613','107000844287','102000972917', '102000972917','107000842613','102000972917','1070 00841532','102000972917','107000844995','102000970 992','101000959331','107000845887','102000972918', '101000959331','102000972918','107000845887','1020 00972918','107000846908','102000972918','102000972 918','105000877235','102000972918','105000878422', '107000770213','105000879270','105000878423','1020 00972918','107000770217','105000878424','102000972 918','107000775023','105000887926','105000878425', '105000878425','105000879267','105000879088','1050 00879267','105000879088','105000879093','105000879 266','107000839528','105000879094','102000972815', '102000972815','105000878439','105000879094','1070 00839528','10700846261 ','102000972918','105000879094','107000846262','10 7000839940','105000879265','105000879094','1070008 46305','107000840636','102000972918','105000879096 ','105000879098','102000972918','107000839451','10 5000877544','105000879098','102000972918','1020009 71700','102000971700','102000971700','105000877544 ','102000972265','105000878215','102000972918','10 5000879099','102000972918','105000879099','1020009 72288','102000972288','105000878217','102000972918 ','105000878357','102000972918','105000877539','10 2000972918','105000877541','105000877178','1050008 77542','102000972918','105000877542','105000877542 ','102000972918','105000877543','105000877178','10 5000877178','105000877178','105000877178','1050008 77959','105000877178','105000877959','105000878835 ','105000877178','105000877178','102100009721','10 5000877178','105000877179','102000871672','1020008 71672','105000877179','102000971672','105000877179 ','102000871673','102000871673','105000877181','10 2000971674','105000877179','102000971675','1020009 71675','105000877179','105000877179','105000877179 ','102000974596','105000877179','102000974596','10 2000972756','105000877179','105000877179','1020009 74597','105000877179','105000877179','102000974597 ','102000972756','105000877179','105000877181','10 2000974597','105000877181','105000877179','846181 ','105000877426','102000974718','846181 ','102000974718','846181 ','102000974718','840224 ','102000974717','101000957973','101000957973','83 7763 ','101000958372','837763 ','101000958379','837763 ','102000971195','837763 ','102000973271','845471 ','102000972583','102000971196','837763 ','101000958127','102000971196','102000972808','10 200097356 ','102000969926','878697 ','102000971403','878698 ','102000972069','954535 ','954534 ','954534 ','878700 ','955974 ','101000963714','955974 ','955974 ','955974 ','102000974313','101000958577','878925 ','958335 ','101000958577','102000974313','958335 ','102000974313','101000960302','950452 ','101000960317','878700 ','958728 ','101000953531','950449 ','101000958577','101000960317','958736 ','101000960317','101000958577','101000960316','10 1000960316','101000958577','101000955979','1010009 58577','107000846980','101000955979','101000958577 ','102000973692','101000958577','107000845465','10 1000955979','101000958577','101000955979','1070008 46980','101000955979','102000973759','101000955979 ','107000846980','101000960304','102000973782','10 1000960304','107000844629','101000955979','1020009 73783','101000960318','101000960435','101000960318 ','101000960306','101000960306','101000960309','10 1000960312','107000841430','105000878973','1050008 78972','105000878970','105000878332','102000973904 ','102000973900','102000973884','102000973883','10 2000973194','102000974560','102000973194','1020009 71992','102000971932','102000974561','102000973536 ','102000972631','102000972631','102000972631','10 1000952185','102000973122','102000974559','1020009 71932','102000973122','102000974575','102000973190 ','102000974059','102000969698','002000974559','10 2000969698','969627 ','102000969698','102000974559','102000969698','95 8743 ','102000969698','102000969698','958741 ','102000970947','102000970947','102000972672','10 2000970947','102000970947','102000970947','1020009 72672','102000971643','102000971433','101000960930 ','101000960930','102000972350','101000960982','10 1000960982','105000878662','102000974576','1050008 78662','102000972353','105000878661','102000974053 ','105000878662','102000974225','105000878665','10 5000878666','102000973777','102000974053','1050005 79039','101000961363','105000879028','105000879037 ','102000971348','102000971348','105000879031','10 200073742 ','101000951107','105000879033','105000879033','10 5000879033','105000879275','101000962323','1010009 62323','102000971764','105000879029','102000971764 ','105000875812','102000973741','102000973741','10 5000879275','105000879029','102000972538','1050008 79275','105000879032','102000971764','960128 ','105000879275','101000962648','102000971764','96 0128 ','105000879280','101000962314','102000972555','96 0128 ','101000962639','102000972554','960880 ','105000879276','102000972553','962561 ','105000875112','101000952572','105000879278','10 5000879278','105000879276','105000879276','1050008 75812','102000974791','102000973323','101000962378 ','101000962378','101000956656','101000956660','10 5000875812','102000972606','102000973204','1020009 74990','105000875812','958731 ','957089 ','105000875812','957089 ','957089 ','958734 ','102000974792','958734 ','102000974793','102000974793','102000974795','10 2000974795','102000974789','102000974791','1050008 78518','102000972401','102000971738','102000972866 ','101000958964','105000878996','107000844418','10 5000878996','101000957106','101000961768','1010009 53530','101000958860','101000958860','105000878997 ','101000958048','101000958048','101000960068','10 5000878999','102000974835','101000961818','1010009 61825','101000961825','105000879000','102000973732 ','102000973732','102000973732','101000962722','87 9260 ','101000962722','102000974588','102000974588','10 5000879002','102000973734','102000973734','1020009 73734','101000957269','101000957269','879260 ','879260 ','105000879003','879260 ','879260 ','105000879004','879260 ','105000879004','879260 ','105000879004','879260 ','879260 ','105000879004','107000843960','879260 ','105000879004','879260 ','879260 ','879260 ','105000879004','101000959979','879260 ','879260 ','102000973180','879260 ','102000973180','879260 ','102000974633','879260 ','102000973586','879260 ','102000974062','879260 ','101000963215','102000974633','879260 ','102000973586','879260 ','102000973586','879260 ','879260 ','102000973590','879260 ','879260 ','102000973588','101000961029','879260 ','102000974313','101000961029','879260 ','102000973589','101000961029','879260 ','102000971733','879260 ','102000973588','879260 ','879260 ','879260 ','879260 ','879260 ','879260 ','879260 ','879260 ','879260 ','879260 ','879260 ','105000879081','105000879081','879261 ','879263 ','105000879081','105000879081','105000879081','10 2000973182','105000879081','974666
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '102000972744'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ') '.

Andres95 · #9 (**permalink**) 17/09/2008, 08:35

Lo que pasa es que el listdo de contenedoras es demasiado largo y supera los 8000 caracteres y la cadena queda incompleta...

Debes implementar un codigo antes para asegurar que no se desborde la cadena.

Tip. Por aca cuando tenemos algo similar, hacemos archivo en db2 y mediante un DTS subimos los elementos que necesitamos (solo los IDs)y ya en db2 mediante joins nos traemos la info correspondiente. Lo cual resulta muy rapido...

Saludos!

iislas · #10 (**permalink**) 18/09/2008, 11:40

No es por nada, pero recuerdo que este tema ya se habia tocado en este foro, incluso el amigo que lo planteo, se fue tirando "pestes" de SQL Server, ya que en MySQL, segun el, no habia restriccion en cuanto a la cadena que estuviera dentro del IN('cadena', 'cadena').....

Saludos

kirill · #11 (**permalink**) 19/09/2008, 08:07

Cita:

Iniciado por Andres95

Lo que pasa es que el listdo de contenedoras es demasiado largo y supera los 8000 caracteres y la cadena queda incompleta...

Debes implementar un codigo antes para asegurar que no se desborde la cadena.

Tip. Por aca cuando tenemos algo similar, hacemos archivo en db2 y mediante un DTS subimos los elementos que necesitamos (solo los IDs)y ya en db2 mediante joins nos traemos la info correspondiente. Lo cual resulta muy rapido...

Saludos!

aloha, pasaba por aqui gracias a google y creo que puedo aportar algo

amigo creo que no es ese el problema que tiene...

yo entiendo que es un problema de comillas... fijate que en el OPENQUERY para escibir la consulta, esta tiene que ir entre comillas simples y luego en el IN cada una de las "opciones" van entre comillas simples tambien... pero a la primera comilla simple que pongas (para abrir el primer elemento del IN) estarias cerrando la primera abierta (o sea la del OPENQUERY), ya que la consulta que estas escribiendo no se interpreta en lo absulto, se trata como texto...

la solución es quotear esas comillas simples que aparezcan dentro de la consulta... pero no te puedo ayudar ya que no se como se quotean en el SQL Server, intenta algo como <\'> o <''>. Fijate en la ayuda de tu SQL Server, seguramente explican como quotearlas o por lo menos como concatenar con un caracter ASCII cualquiera (en este caso el 0x27), puedes probar algo como char(39) (no si existira esa funcion en sql server, pero si es que existe devolveria un caracter, en este caso la comilla simple, y simplemente la concatenas con tu consulta).

y eso de que la cadena en el error aparece cortada seguramente sea porque el mensaje del error es un varchar ;)

PD: cuenta como te fue

saludosss.
Mr. K

Andres95 · #12 (**permalink**) 19/09/2008, 08:29

tienes razon kirill.. tambien hay que poner dos veces la comilla simple para que no marque error...

por ejemplo

Código:

Select * from openquery(linkedserver, 'Select * from mitabla where valor in (''a'',''b'',''c'')')

No es comilla doble, es dos veces la comilla sencilla...

Saludos!

Ruisu · #13 (**permalink**) 25/09/2008, 11:27

No logro pasarle la doble comilla...

Ruisu · #14 (**permalink**) 25/09/2008, 12:52

Logré ponerle doble comillas... pero me genero otro error, debe ser por el limite de la cadena...

SELECT * FROM OPENQUERY(CURRENTWMS, 'SELECT BPD_WFNAME AS [USUARIO], BCH_NROCNT AS [CONTENEDORA], BCH_CODLOC AS [LOCAL], BPD_WFCASE AS [CAJAS], BCH_FECENT AS [FECHA] FROM FILELIB.BWPRDDIA T1 INNER JOIN FILELIB.BWCNTH01 T2 ON BPD_NROASI=BCH_NROASG AND BPD_WFASID=DIGITS(DECIMAL(BCH_CODLOC, 5, 0)) AND T1.CODFAC=T2.CODFAC AND T1.CODWHS=T2.CODWHS AND T1.WFACTD+19000000=T2.FECCRE AND DIGITS(BCH_NROCNT) IN ( ''101000873999'',''102000931450'',''101000878195'' ,''101000878195'',''102000931141'',''102000930244' ',''102000931141'',''102000931141'',''101000877247 '',''101000868541'',''102000932059'',''10100087649 1'',''101000876323'',''101000876326'',''1020009302 43'',''102000932163'',''101000881805'',''102000932 163'',''102000932165'',''101000//1801'',''101000877748'',''101000880006'',''1010008 80006'',''101000877746'',''102000933398'',''102000 933267'',''101000881748'',''102000933267'',''10100 0879976'',''102000933267'',''101000880006'',''1010 00880006'',''101000879976'',''102000933268'',''102 000933266'',''107000810247'',''102000933265'',''10 2000932128'',''101000877904'',''102000928928'',''1 02000928928'',''102000933532'',''107000810246'','' 102000933532'',''107000810246'',''102000933532'',' '107000810540'',''107000810246'',''102000934188'', ''105000868711'',''101000873735'',''101000876260'' ,''102000933396'',''105000868711'',''102000933395' ',''105000868712'',''107000809886'',''105000868712 '',''107000810253'',''107000810506'',''10200093353 3'',''102000933533'',''107000807997'',''1010008829 77'',''102000933533'',''102000933533'',''107000809 063'',''102000933551'',''102000930753'',''10200093 3957'',''102000933551'',''101000877426'',''1010008 77426'',''102000933551'',''101000877432'',''107000 806241'',''101000864404'',''107000806240'',''10700 0806240'',''101000879210'',''101000862853'',''1050 00868194'',''107000806240'',''105000868195'',''107 000806240'',''105000868195'',''105000868199'',''10 7000810777'',''101000873275'',''101000878021'',''1 02000934666'',''105000867495'',''102000934668'','' 105000867795'',''102000933524'',''107000810508'',' '105000868622'',''105000867495'',''105000866994'', ''105000866995'',''102000933551'',''101000868061'' ,''105000867750'',''102000933554'',''102000933554' ',''101000871900'',''102000931235'',''105000867751 '',''101000871900'',''101000871900'',''10100087190 1'',''105000867495'',''101000874220'',''1020009330 89'',''102000933089'',''101000874220'',''107000810 508'',''105000867495'',''000000000 '',''101000874220'',''101000874220'',''10100087838 4'',''00000000000 '',''102000932621'',''102000933740'',''10500086749 5'',''101000878384'',''101000878384'',''1020009337 46'',''102000933746'',''102000933271'',''102000933 273'',''102000933760'',''102000930664'',''10200093 0664'',''107000807604'',''102000934825'',''1070008 09098'',''102000934827'',''102000934744'',''102000 932791'',''105000868725'',''105000868725'',''10700 0812785'',''876202 '',''876711 '',''107000812790'',''877359 '',''877359 '',''877861 '',''877863 '',''102000933828'',''102000930930'',''10200093382 8'',''102000934961'',''102000933828'',''1020009349 60'',''102000932652'',''102000932652'',''102000932 652'',''101000879959'',''102000934062'',''10200934 414 .... (etc)
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL0104 - Símbolo [ no válido. Símbolos válidos: <IDENTIFIER>.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

es eso cierto?

Ruisu · #15 (**permalink**) 25/09/2008, 12:57

Bueno... me di cuenta que el error fue que no me aceptaba corchetes [alias] asi que lo reemplace por doble comilla "alias"... pero me dió otro error :

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL0010 - Inicio de constante de serie '1020009)' no delimitado.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

debe haber cerrado la cadena hasta los 8000...

Ruisu · #16 (**permalink**) 25/09/2008, 15:22

Lo ejecuté con menos datos y funciono bien... pero no me sirve por ese límite de registros en la cadena.
Ahora, habrá alguna forma de unirlo con algun JOIN como tabla e insertarla en la consulta ISeries para solo elejir esos registros....

Andres95 · #17 (**permalink**) 25/09/2008, 20:00

En situaciones similares, yo subo los ids a una tabla de Db2 mediante un DTS para despues hacer los joins necesarios directamente desde Db2 para bajar la info a SQL.

Saludos!

Ruisu · #18 (**permalink**) 01/10/2008, 09:22

Hice eso, subí los datos a Iseries y hice los join ahi y me traje solo esos registros...
hubiera hecho eso desde el principio... ja
gracias!!

Andres95 · #19 (**permalink**) 01/10/2008, 09:46

bueno ahora ya conoces las dos opciones para hacer la consulta, ya sean pocos registros o muchos lo que quieras filtrar...

Saludos!