Message-ID: <23260790.post@talk.nabble.com>
Date: Mon, 27 Apr 2009 10:11:09 -0700 (PDT)
From: landsharkdaddy <ldodd@landsharksoftware.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query with Parameters and Wildcards
In-Reply-To: <200904271829.26557.j.kobal@gmx.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <23248274.post@talk.nabble.com> <23257346.post@talk.nabble.com>
	<487E9B6FF30F43DFB2DC0B00E81DAF7A@dayatPC>
	<200904271829.26557.j.kobal@gmx.com>


That is it! It works just fine and from my Typed Dataset in c# I just call
the function using SELECT * FROM
lanemanager.GetCustomerByLastName(:customerlastname) and it works perfectly.
Through this whole process I have also gained a greater understanding of
using Functions as well. I guess I would like to know why

SELECT * FROM lanemanger.customers WHERE (customerfirstname ILIKE $1 || '%')

doesnt work when used as the select query of the dataset. Using functions is
just as well but I would like to understand why the query doesnt work unless
it is called from a function. Thanks to all that helped with this one, you
have been very helpful.


Jure Kobal wrote:
> 
> In the original select you missed a small part. The operation || needs a
> value 
> on every side and you missed the value on the left side. You had it as:
> WHERE 
> (customerlastname ILIKE || '%') instead of WHERE (customerlastname 
> ILIKE 'lastname' || '%'). And that is the reason for the error you got.
> 
> The function from dayat has a small mistake. It should be:
> CREATE OR REPLACE FUNCTION TEST(lastname VARCHAR)
> else it will result in an error because of the missing space.
> 
> On Monday 27 of April 2009 16:47:40 dayat wrote:
>> Do you run this code in the function? If so, the following example
>> function
>> with LANGUAGE SQL function:
>>
>> CREATE OR REPLACE FUNCTION TEST(lastnameVARCHAR)
>> RETURNS SETOF lanemanager.customers
>> AS $$
>> SELECT customercellphone, customercity, customerdatecreated,
>> customerdatelastmodified, customeremail, customerfax, customerfirstname,
>> customerid, customerlastname, customermiddleinitial, customerphone,
>> customerreferredby, customerstateabbr, customerstreet1, customerstreet2,
>> customersuffix, customertitle, customerworkphone, customerworkphoneext,
>> customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE $1
>> ||
>> '%')
>> $$
>> LANGUAGE SQL;
>>
>> Please tell me is it work for you.
>>
>> Regards
>> Hidayat
>>
>> ----- Original Message -----
>> From: "landsharkdaddy" <ldodd@landsharksoftware.com>
>> To: <pgsql-sql@postgresql.org>
>> Sent: Monday, April 27, 2009 9:19 PM
>> Subject: Re: [SQL] Query with Parameters and Wildcards
>>
>> > When I try the following like you suggested I get an error that says
>> > "operator does not exist: || unknown
>> >
>> >
>> >
>> > SELECT customercellphone, customercity, customerdatecreated,
>> > customerdatelastmodified, customeremail, customerfax,
>> customerfirstname,
>> > customerid, customerlastname, customermiddleinitial, customerphone,
>> > customerreferredby, customerstateabbr, customerstreet1,
>> customerstreet2,
>> > customersuffix, customertitle, customerworkphone, customerworkphoneext,
>> > customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE ||
>> > '%')
>> >
>> > landsharkdaddy wrote:
>> >> I have a query that works on SQL Server to return customers that
>> contain
>> >> the string entered by the user by accepting parameters and using the
>> >> LIKE keyword. I would like to move this to postgreSQL but I'm just not
>> >> sure how
>> >> to get it done. This is the query
>> >>
>> >> SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%';
>> >>
>> >> This works great on SQL Server but not on postgreSQL. Any help would
>> be
>> >> appreciated.
>>
>> __________________________________________________
>> Apakah Anda Yahoo!?
>> Lelah menerima spam?  Surat Yahoo! memiliki perlindungan terbaik terhadap
>> spam http://id.mail.yahoo.com
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 
> 

-- 
View this message in context: http://www.nabble.com/Query-with-Parameters-and-Wildcards-tp23248274p23260790.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.