Re: How to use a an integer parameter to fetch parameter = "N" or all rows.

Article View: borland.public.interbase.sql

Article #852

Back to borland.public.interbase.sql Previous Next

#852

From: Sergio
Date: Sun, 03 Jun 2007 06:07

31 lines
2348 bytes

I don't know if interbase have this function "Coalesce()" (i use firebird)

Where
    ProjectID = Coalesce(:ProjectID, ProjectID) And
    LocationID = Coalesce(:LocationID, LocationID)

if the parameters is passed as null, is considered the actual field value = 
actual field value (all)
Coalesce() returns the first nonnull parameter passed (you can use 
Coalesce(ValueA, ValueB, ValueC, ValueD...)


"Wayne Niddery [TeamB]" <wniddery@chaffaci.on.ca> escreveu na mensagem 
news:465dc28f$1@newsgroups.borland.com...
> Mitch wrote:
>> WHERE
>>  ProjectID = :ProjectID and (LocationID = :LocationID or
>> :all_locations =1 )
>>
>> If I set :all_locations = 0 then only locationID shows.
>> If I set :all_locations = 1 then all locationIDs show.
>
> Didn't think of using a separate parameter purely as a switch. Check the 
> plan on this - I expect it should still be able to use any index you have 
> on locationid, if so then that's the better way to do it.
>
> -- 
> Wayne Niddery - Winwright, Inc (www.winwright.ca)
> "It used to be that other people's achievements were considered an
> inspiration, not a grievance."
>

Technical Details

Message-ID: <46625b0a@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news1.google.com!news3.google.com!proxad.net!feeder1-2.proxad.net!213.200.89.82.MISMATCH!tiscali!newsfeed1.ip.tiscali.net!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

References:

<465d29fa$1@newsgroups.borland.com> <465d8a1c$1@newsgroups.borland.com> <465da751$1@newsgroups.borland.com> <465dc28f$1@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,d43d3e3c175ab47e
X-Google-Attributes: gid114a0e,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
From: "Sergio" <khalebsuperbad@ig.com.br>
Newsgroups: borland.public.interbase.sql
References: <465d29fa$1@newsgroups.borland.com> <465d8a1c$1@newsgroups.borland.com> <465da751$1@newsgroups.borland.com> <465dc28f$1@newsgroups.borland.com>
Subject: Re: How to use a an integer parameter to fetch parameter = "N" or all rows.
Date: Sun, 3 Jun 2007 03:07:28 -0300
Lines: 33
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
X-Antivirus: avast! (VPS 000746-0, 30/05/2007), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: 201.14.170.238
Message-ID: <46625b0a@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1180850954 201.14.170.238 (2 Jun 2007 23:09:14 -0700)
Path: arc1-ca.pugleaf.net!g2news1.google.com!news3.google.com!proxad.net!feeder1-2.proxad.net!213.200.89.82.MISMATCH!tiscali!newsfeed1.ip.tiscali.net!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

Reply to Parent Message