Skip to Content
author's profile photo Former Member
Former Member

Colocar Parametros en Consulta de Query Manager

Saludos a la comunidad, necesito ayuda para colocar parametros a una consulta hecha en query manager para mostrar las ordenes de compra creadas, la idea es poder filtrar el resultado del query por empleado del departamento de ventas y por el rango de fechas de los documentos.

La consulta es la siguiente:

SELECT T1.DocEntry AS 'DocEntry Prel', T2.DocNum AS 'Num Doc DEF', t3.prjname as 'Proyecto', T2.[CardName] AS 'Nombre de deudor/acreedor', T2.[TaxDate] AS 'Fecha de documento', T2.[BaseAmnt] AS 'Importe base', T2.[DiscSum] AS 'Descuento total', T2.[DocTotal] AS 'Total del documento', T2.[U_Condicion_Entrega] AS 'Condiciones de Entrega', T2.[U_Criticidad] AS 'Criticidad', T5.[Descr] AS 'Solicitante', T4.[SlpName] AS 'Comprador', T6.WDDCode, T8.U_Name AS 'Usuario Aprobador',

CASE T7.Status WHEN 'W' THEN 'En Espera' WHEN 'Y' THEN 'Aprobado' WHEN 'N' THEN 'Rechazado' END 'Decisión', T7.Remarks, T7.UpdateDate AS 'Fecha Aprobación',

T7.UpdateTime AS 'Hora Aprobación'

FROM ODRF AS T1, OPOR AS T2, OPRJ as T3, OSLP as T4, OWDD AS T6, WDD1 AS T7, OUSR AS T8, UFD1 AS T5

WHERE T1.DocNum = T2.DocEntry AND

T1.DocEntry = T2.draftKey AND

T2.DocStatus = 'O' AND

T1.DocStatus = 'C' AND

T2.project=T3.prjcode AND

T2.slpcode=T4.slpcode AND

T2.Docentry=T6.Docentry AND

T6.WDDCode=T7.WDDCode AND

T7.UserID=T8.UserID AND

T2.[U_Solicitante]=T5.[FldValue] AND

T5.[FieldID]=29 AND

T5.tableid='ODRF'

Agradezco la ayuda!

Miguel

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 07, 2009 at 08:34 PM

    Hola.

    Agrega al final de la cláusua Where,

    AND T2. DocNum BETWEEN '[%0]' AND '[%1]'

    Esto pregunta el rango de fechas para ese documento.

    Para filtrar por el empleado de ventas, se puede pedir el valor así:

    AND T4.SlpName LIKE '[%2]'

    o también no pidiendo este parámetro y filtrando sobre el resultado del query, usando el botón del embudo verde en la barrra de botones.

    Saludos.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Saludos Hector, al colocar lo que me indicas

      AND T2. DocNum BETWEEN '[%0]' AND '[%1]'

      o

      AND T4.SlpName ...

      Me da un error que indica que no se puede convertir fecha a una cadena de caracteres.

      Probe sin colocarle [ ] y tambien me lanza error...

      Agradezco la ayuda

      Edited by: Miguel Osta on Sep 7, 2009 11:24 PM

  • author's profile photo Former Member
    Former Member
    Posted on Sep 07, 2009 at 10:38 PM

    Hola..

    Prueba con

    T2.DocDate >= [%0] and T2.DocDate <= [%1] and T4.SlpName = [%2]

    Saludos,

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Saludos a todos, he probado la ultima recomendacion y tambien da un error de sintaxis, pareciera que existen reglas para aplicar estos parametros y como en mi query existen varias condiciones, estas deben tener algun cambio de formato, seguire probando otras alternativas, si alguien sabe alguna distinta a las ya probadas, mucho lo agradezco.

      Miguel

  • author's profile photo Former Member
    Former Member
    Posted on Sep 08, 2009 at 02:09 PM

    Estimado,

    Dos errore:

    en el from debes vorrar el AS, solo debe quedar ODFR T1

    FROM ODRF AS T1, OPOR AS T2, OPRJ as T3, OSLP as T4, OWDD AS T6, WDD1 AS T7, OUSR AS T8, UFD1 AS T5

    En los parametros de fecha,

    '[%0]'

    Atte,

    Manuel Lazcano

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 08, 2009 at 07:40 PM

    Hola...

    Simplemente todos podemos equivocarnos...asi de simple

    Saludos,

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.