"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Connie,


That shouldn't make any difference at all.  Are you sure that moving the
expression isn't altering the meaning of the WHERE clause ?


I have a total of 456 records in services, and 377 records in invoices.

Using this query statement :


Select I.InvoiceDate, I.InvoiceNumber, I.CustName, I.CustUID, I.TaxSubTotal, I.NonTaxSubTotal,I.SalesTax, I.PrepaidUsed, I.Total,
S.InvoiceUID as InvoiceUID, S.UID as OrderUID, I.Total - (I.Deposit + I.PrepaidUsed) as Beginning, (I.Payments + I.Deposit + I.PrepaidUsed)
as TotalPayments
from Invoice I, service S


where
 I.IsFromOrder = true and
 S.InvoiceUID = I.UID and
 I.InvoiceDate >= :StartDate and I.InvoiceDate <= :EndDate

I get 309 records.


but ...
where
 I.InvoiceDate >= :StartDate and I.InvoiceDate <= :EndDate and
 I.IsFromOrder = true and
 S.InvoiceUID = I.UID

returns 0 records.

I don't know how it could be changing the meaning.  The clauses are connected by 'ands'.

btw, if the parameters are empty, I prefer it to return the 309 records.

BTW, I AM using DBSYS, so I know it isn't a version thing.

Connie,

<< BTW, I AM using DBSYS, so I know it isn't a version thing. >>

I'll have to double-check to see what is causing this, but you should always
add the proper parameters when executing a parameterized query.  If you have
ParamCheck=False (like DBSYS does), then currently the behavior is
"undefined" as to what could possibly happen.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Connie,

<< BTW, I AM using DBSYS, so I know it isn't a version thing. >>

I'll have to double-check to see what is causing this, but you should always
add the proper parameters when executing a parameterized query.  If you have
ParamCheck=False (like DBSYS does), then currently the behavior is
"undefined" as to what could possibly happen.


I do have ParamCheck = false in my code.
But the query parameters are being filled out for reporting purposes.
I would like it to ignore the blank parameters, or something, so if the user doesn't fill in a value, that particular
parameter is ignored.
Is there a way to do this?

Connie,

A parameter is a substitution, so I doubt that the SQL
parser could be easily made to ignore a term solely because
a parameter substitution used in the term is blank. I would
guess a better method would be to build your query (or maybe
just a WHERE clause) in code based on what information the
user gives you.

HTH,

Sean





Connie McBride wrote:
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
>
> Connie,
>
> << BTW, I AM using DBSYS, so I know it isn't a version thing. >>
>
> I'll have to double-check to see what is causing this, but you should always
> add the proper parameters when executing a parameterized query.  If you have
> ParamCheck=False (like DBSYS does), then currently the behavior is
> "undefined" as to what could possibly happen.
>
>
> I do have ParamCheck = false in my code.
> But the query parameters are being filled out for reporting purposes.
> I would like it to ignore the blank parameters, or something, so if the user doesn't fill in a value, that particular
> parameter is ignored.
> Is there a way to do this?
>
>

Connie McBride <conniem@axcis.net> wrote in
news:61577970-13A6-4BC8-A9C6-C018B28C19AB@news.elevatesoft.com:

> I would like it to ignore the blank parameters, or something, so if
> the user doesn't fill in a value, that particular parameter is
> ignored. Is there a way to do this?
>

That's what my:

  (:StartDate IS NULL OR I.InvoiceDate >= :StartDate) and

was intended to do. I've used it with other databases in the past.

perhaps this syntax isn't valid in DBISAM?

--
Chris

Chris


I don't think you can supply field names as parameters in DBISAM

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in news:0EF03D56-7E71-4A0C-
AFD5-DAB0A89F0FBD@news.elevatesoft.com:

> I don't think you can supply field names as parameters in DBISAM

Roy,

 You're quite right, but that's not what I'm doing. In my reply to
another  of Connie's posts I wrote:

> What happens if you alter it to:
>
> where
>    I.IsFromOrder = true and
>    (:StartDate IS NULL OR I.InvoiceDate >= :StartDate) and
>    (:EndDate IS NULL OR I.InvoiceDate <= :EndDate) and
>    S.InvoiceUID = I.UID
>
> ?

 I'm testing the *value* of the parameter against NULL, (in the hope
that an undefined parameter's value evaluates to NULL in DBISAM) and if
it's not NULL (hopefully left-to-right is respected here) then I'm
testing it against the pre-defined fieldname.

I was sure I'd used this in MSSQL or INFORMIX in the past, but I can't
find any examples at the moment, grepping through my archives, so perhaps
I imagined it...

--
Chris

Chris


Sorry for misinterpreting, looks like a fun feature to have though.

Roy Lambert

Connie,

<< I do have ParamCheck = false in my code.  But the query parameters are
being filled out for reporting purposes. I would like it to ignore the blank
parameters, or something, so if the user doesn't fill in a value, that
particular parameter is ignored. Is there a way to do this? >>

The only way to do so is the way that Chris indicated with the IS NULL
check.  However, you *must* make sure that you add the proper parameters to
the TDBISAMQuery component since you're using ParamCheck=False.  Frankly, I
would set ParamCheck:=True and save yourself the trouble of having to
manually add the parameters.

--
Tim Young
Elevate Software
www.elevatesoft.com