Re: Query with Parameters and Wildcards

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(at)landsharksoftware(dot)com>
>> To: <pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)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.