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

Query with 2 variables

I have a query with two variables (Employee and Date). The query is working fine if both of the variables are check and values assigned. But if only one variable is checked and a value assigned then the query will return 0 results. Is it possible that the query cannot work with only one variable out of the 2? Do I need a workaround or am i doing something wrong?

Here is the query:

SELECT T0.DocNum, T0.DocDate, T0.DocDueDate, T0.CardCode, T0.CardName, T0.Address, T0.DocTotal, T0.U_Employee, T1.firstName,T1.lastName, T0.Comments FROM OVPM T0 LEFT JOIN OHEM T1 ON T0.U_EMPLOYEE = T1.FIRSTNAME' 'T1.LASTNAME WHERE T0.DocType = 'A' AND T0.U_EMPLOYEE = [%0] AND T0.DocDate =[%1].

Thank you so much for your help,

Irina Stanca

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2007 at 10:40 PM

    AFAIK, at least with 'complex queries' unchecked parameter gets

    some default/blank value. Therefore, you should allow default values in your

    WHERE clause.

    You want: WHERE T0.COL = @VAR

    You write: WHERE (T0.COL = @VAR OR [logical test for @VAR is a default value])

    Snippets like this may help you in finding out that default value:

    -- snip --

    /SELECT FROM [dbo].[OHEM] T9/

    declare @VAR_EMPL as char(20)

    /* WHERE */

    set @VAR_EMPL = /* T9.lastName */ '[%0]'

    /SELECT FROM [dbo].[OVPM] T8/

    declare @VAR_DATE as char(20)

    /* WHERE */

    set @VAR_DATE = /* T8.DocDate */ '[%1]'

    SELECT '@VAR_EMPL: ' + '|' + @VAR_EMPL + '|' + '@VAR_DATE: ' + @VAR_DATE + ' DATEDIFF: ' + cast(DATEDIFF(day, CAST('1900-01-01' as datetime), CAST(@VAR_DATE as datetime)) as varchar)

    [/code]

    -- snip --

    Yes, it looks like the default value for dates is '1900-01-01'.

    Therefore, you may want to code this way

    .. WHERE (DocDate = @VAR_DATE OR DATEDIFF(day, CAST('1900-01-01' as datetime), CAST(@VAR_DATE as datetime)) > 0)

    I also noticed a date-related issue that may cause problems in complex queries

    if you work with dates from last century AND if your date format in B1 is YY instead of CCYY:

    you enter 1907 and B1 interprets it as 2007.

    HTH

    Juha

    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.