I have these two queries:
Query 1 -
Select I.*, I.UID as OrderUID
from Invoice I
where
  I.IsFromOrder = false and
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and     (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or (I.CustUID = :RecordUID or I.UID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed)

union all
Select I.*, S.UID as OrderUID
from Invoice I, Service S
where
  I.IsFromOrder = true and
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and   (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or (I.CustUID = :RecordUID or S.UID = :RecordUID or S.InvoiceUID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed) and
  S.InvoiceUID = I.UID

union all
Select I.*, S.UID as OrderUID
from Invoice I, Install S
where
  I.IsFromOrder = true and
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and   (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or( I.CustUID = :RecordUID or S.UID = :RecordUID or S.InvoiceUID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed) and
  S.InvoiceUID = I.UID

Query 2 :
Select I.*
from Invoice I
where
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and     
 (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or (I.CustUID = :RecordUID or I.UID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed)



Query 1 works correctly, including the invoices.
Query 2 returns 0 records with the same parameters, except in DBSYS, when I hard code the parameter values(because if I don't code the
parameter values, 0 records are returned in DBSys).
Can ANYONE tell me why?

Further inspection :
if I change query 2 to :
Select I.*
from Invoice I
where
 I.IsFromOrder = false and
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and     (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or (I.CustUID = :RecordUID or I.UID = :RecordUID)) and
 (:Closed is null or I.Closed = :Closed)

Then I get the correct results (sort of, I want all my invoices, not just where that is false)

I assume you are putting both queries into the same TDBISAMQuery?.

You need to code the OnGetParams as the parameters are only picked up for
the first query by default, to provide params for the second and subsequent
you need to code the OnGetParams event.




"Connie McBride" <conniem@axcis.net> wrote in message
news:6F6EA19C-9EDC-48B4-A3DD-3FF11A649FA8@news.elevatesoft.com...
>I have these two queries:
> Query 1 -
> Select I.*, I.UID as OrderUID
> from Invoice I
> where
>   I.IsFromOrder = false and
>  (:StartDate is null or  I.InvoiceDate >= :StartDate) and     (:EndDate is
> null or I.InvoiceDate <= :EndDate) and
>  (:RecordUID is null or (I.CustUID = :RecordUID or I.UID = :RecordUID))
> and
>  (:Closed is null or I.Closed = :Closed)
>
> union all
> Select I.*, S.UID as OrderUID
> from Invoice I, Service S
> where
>   I.IsFromOrder = true and
>  (:StartDate is null or  I.InvoiceDate >= :StartDate) and   (:EndDate is
> null or I.InvoiceDate <= :EndDate) and
>  (:RecordUID is null or (I.CustUID = :RecordUID or S.UID = :RecordUID or
> S.InvoiceUID = :RecordUID)) and
>  (:Closed is null or I.Closed = :Closed) and
>   S.InvoiceUID = I.UID
>
> union all
> Select I.*, S.UID as OrderUID
> from Invoice I, Install S
> where
>   I.IsFromOrder = true and
>  (:StartDate is null or  I.InvoiceDate >= :StartDate) and   (:EndDate is
> null or I.InvoiceDate <= :EndDate) and
>  (:RecordUID is null or( I.CustUID = :RecordUID or S.UID = :RecordUID or
> S.InvoiceUID = :RecordUID)) and
>  (:Closed is null or I.Closed = :Closed) and
>   S.InvoiceUID = I.UID
>
> Query 2 :
> Select I.*
> from Invoice I
> where
>  (:StartDate is null or  I.InvoiceDate >= :StartDate) and
>  (:EndDate is null or I.InvoiceDate <= :EndDate) and
>  (:RecordUID is null or (I.CustUID = :RecordUID or I.UID = :RecordUID))
> and
>  (:Closed is null or I.Closed = :Closed)
>
>
>
> Query 1 works correctly, including the invoices.
> Query 2 returns 0 records with the same parameters, except in DBSYS, when
> I hard code the parameter values(because if I don't code the
> parameter values, 0 records are returned in DBSys).
> Can ANYONE tell me why?
>
>
>

Connie McBride <conniem@axcis.net> wrote in
news:A3D98A12-0B6B-4EE3-B657-35A4850EEF1F@news.elevatesoft.com:

> Further inspection :
> if I change query 2 to :
> Select I.*
> from Invoice I
> where
>   I.IsFromOrder = false and
>   (:StartDate is null or  I.InvoiceDate >= :StartDate) and    
>   (:EndDate is null or I.InvoiceDate <= :EndDate) and (:RecordUID is
>   null or (I.CustUID = :RecordUID or I.UID = :RecordUID)) and (:Closed
>   is null or I.Closed = :Closed)
>
> Then I get the correct results (sort of, I want all my invoices, not
> just where that is false)
>

Connie,

Is it possible that DBISAM is now testing whether I.IsFromOrder is equal
to
( false and
   (:StartDate is null or  I.InvoiceDate >= :StartDate) and     
(:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or (I.CustUID = :RecordUID or I.UID = :RecordUID))
and
 (:Closed is null or I.Closed = :Closed)
)
which is bound to be false due the the implicit parentheses (false and
....), making the result true for all rows where I.IsFromOrder is false,
whatever the values of the parameters?

This would only apply because there is an unbracketed test of two boolean
values up front.

If so, it's a sort-of bug, isn't it, Tim?
--
Chris

"Clive" <dd@dddd.com> wrote:

I assume you are putting both queries into the same TDBISAMQuery?.

No.

Chris Erdal <chris@No-Spam-erdal.net> wrote:

Connie McBride <conniem@axcis.net> wrote in
news:A3D98A12-0B6B-4EE3-B657-35A4850EEF1F@news.elevatesoft.com:

> Further inspection :
> if I change query 2 to :
> Select I.*
> from Invoice I
> where
>   I.IsFromOrder = false and
>   (:StartDate is null or  I.InvoiceDate >= :StartDate) and    
>   (:EndDate is null or I.InvoiceDate <= :EndDate) and (:RecordUID is
>   null or (I.CustUID = :RecordUID or I.UID = :RecordUID)) and (:Closed
>   is null or I.Closed = :Closed)
>
> Then I get the correct results (sort of, I want all my invoices, not
> just where that is false)
>

Connie,

Is it possible that DBISAM is now testing whether I.IsFromOrder is equal
to
( false and
   (:StartDate is null or  I.InvoiceDate >= :StartDate) and     
(:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or (I.CustUID = :RecordUID or I.UID = :RecordUID))
and
 (:Closed is null or I.Closed = :Closed)
)


Well I 'worked around' by changing it to
> Select I.*
> from Invoice I
> where
  (I.IsFromOrder = false or I.IsFromOrder = true) and  ---->>>changed to this
>   (:StartDate is null or  I.InvoiceDate >= :StartDate) and    
>   (:EndDate is null or I.InvoiceDate <= :EndDate) and (:RecordUID is
>   null or (I.CustUID = :RecordUID or I.UID = :RecordUID)) and (:Closed
>   is null or I.Closed = :Closed)
>
> Then I get the correct results (sort of, I want all my invoices, not
> just where that is false)
>
Now I get the correct ones.
And the problem is that I want the invoices that match the parameters only.  I shouldn't have to put in the fake condition.

Connie,

<< Query 1 works correctly, including the invoices.
Query 2 returns 0 records with the same parameters, except in DBSYS, when I
hard code the parameter values(because if I don't code the parameter values,
0 records are returned in DBSys). Can ANYONE tell me why? >>

If it works when you hard-code the parameters, then you must be assigning
the parameters incorrectly.  Could you send me an example of how you're
setting the parameters for the query ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Well then you are not assigning your parameters correctly.

"Connie McBride" <conniem@axcis.net> wrote in message
news:3C39C4F3-77DE-4A9B-B9B6-4391904605E4@news.elevatesoft.com...
> "Clive" <dd@dddd.com> wrote:
>
> I assume you are putting both queries into the same TDBISAMQuery?.
>
> No.
>

The process is that the user is prompted for the parameters.
The results are placed in a list, in the form of pname=pvalue;pdatatype.
This code goes through the list, and replaces the parameters.
And, BTW, This is happening for all of the previous queries.  

procedure TdmReports.ReplaceParams2(var aQuery : tdbIsamQuery; PreviewOnly : boolean; pResult : tStringList);
var
  x : integer;
  sDate : tDateTime;
  eDate : tDateTime;
  aUID : string;
  pName : string;
  pValue : string;
  wString : string;
  pType : string;
begin
  aUID := SelectedReport.RecUID;
  for x := 0 to pResult.Count - 1 do
  begin
        wString := pResult[x];
        ParsePResults(wString, pName, pValue, pType);
        if uppercase(pType) = 'BOOLEAN' then
        begin
           if uppercase(pValue) = 'FALSE' then
              aQuery.ParamByName(pName).AsBoolean := false
           else
           if uppercase(pValue) = 'TRUE' then
              aQuery.ParamByName(pName).AsBoolean := true
           else
           begin
              aQuery.ParamByName(pName).DataType := ftBoolean;
              aQuery.ParamByName(pName).Clear;
           end;
        end
        else
        if uppercase(pType) = 'INTEGER' then
        begin
           if pValue = '' then
           begin
              aQuery.ParamByName(pName).DataType := ftInteger;
              aQuery.ParamByName(pName).Clear;
           end
           else
              aQuery.ParamByName(pName).AsInteger := StrToIntDef(pValue, 0)
        end
        else
        if uppercase(pType) = 'DECIMAL' then
        begin
           if pValue = '' then
           begin
              aQuery.ParamByName(pName).DataType := ftFloat;
              aQuery.ParamByName(pName).Clear;
           end
           else
              aQuery.ParamByName(pName).AsFloat := StrToFloatDef(pValue, 0)
        end
        else
        if uppercase(pType) = 'CURRENCY' then
        begin
           if pValue = '' then
           begin
              aQuery.ParamByName(pName).DataType := ftCurrency;
              aQuery.ParamByName(pName).Clear;
           end
           else
              aQuery.ParamByName(pName).AsCurrency := StrToCurrDef(pValue, 0)
        end
        else
        if uppercase(pType) = 'DATE' then
        begin
           if pValue = '' then
           begin
              aQuery.ParamByName(pName).DataType := ftDate;
              aQuery.ParamByName(pName).Clear;
           end
           else
           begin
              try
                 aQuery.ParamByName(pName).AsDate := StrToAnsiDate(pValue);
              except
                 try
                    aQuery.ParamByName(pName).AsDate := StrToDate(pValue);
                 except
                    aQuery.ParamByName(pName).DataType := ftDate;
                    aQuery.ParamByName(pName).Clear;
                 end
              end
           end;
        end
        else
        begin
           if pValue = '' then
           begin
              aQuery.ParamByName(pName).DataType := ftString;
              aQuery.ParamByName(pName).Clear;
           end
           else
              aQuery.ParamByName(pName).AsString := pValue;
        end;
     end;
  end;
end;

Connie McBride <conniem@axcis.net> wrote in
news:F347D413-7C6D-4448-81E1-55A791C79AFC@news.elevatesoft.com:

> Well I 'worked around' by changing it to
>> Select I.*
>> from Invoice I
>> where
>    (I.IsFromOrder = false or I.IsFromOrder = true) and  ---->>>changed
>    to this
>>   (:StartDate is null or  I.InvoiceDate >= :StartDate) and    
>>   (:EndDate is null or I.InvoiceDate <= :EndDate) and (:RecordUID is
>>   null or (I.CustUID = :RecordUID or I.UID = :RecordUID)) and
>>   (:Closed is null or I.Closed = :Closed)
>>
>> Then I get the correct results (sort of, I want all my invoices, not
>> just where that is false)
>>
> Now I get the correct ones.
> And the problem is that I want the invoices that match the parameters
> only.  I shouldn't have to put in the fake condition.
>

In view of that, how about trying the original WHERE clause with
parentheses around every test, i.e.

((:StartDate is null) or (I.InvoiceDate >= :StartDate)) and
etc.

What does that give?
--
Chris