[SQL] return field from different table conditionally

I'm having trouble getting the values I want from my tables. Below you find the DDL's for all tables concerned and some data to test with. What I would like to get as a result is: for each billing_exact record the salesunit from account_ranges if there is a match between billing_exact.lineamount

[SQL] Like problem

8.2.5 I am having an issue with trying to use 'LIKE' so that I can match on a string with an underscore in it. What is the proper way to find the following string? Table t1 Column c1 String I want to match on 'abc_' SELECT c1 FROM t1 WHERE c1 LIKE '%abc\_%'; This gives me the follow

Re: [SQL] Like problem

Richard Huxton <[EMAIL PROTECTED]> writes: > Campbell, Lance wrote: >> WARNING: nonstandard use of escape in a string literal >> LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; > Either indicate you are using an escaped string: LIKE E'%abc\_%' Actually that's wrong, what he'd need is LIKE E'%a

Re: [SQL] Like problem

Richard, The first example you gave me does not work. Below is the test example I used (this example should NOT return 'matched'): SELECT 'matched' WHERE 'abcgxyz' LIKE E'%abc\_x%'; ?column? -- matched (1 row) The second example you gave me does work: SELECT 'matched' WHERE 'abcgxyz

Re: [SQL] Like problem

Campbell, Lance wrote: 8.2.5 I am having an issue with trying to use 'LIKE' so that I can match on a string with an underscore in it. What is the proper way to find the following string? WARNING: nonstandard use of escape in a string literal LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'

Re: [SQL] return field from different table conditionally

I gave up trying to do this is a single SQL statement and went over to writing a stored procedure. Unfortunately I get stuck here too. This function gets me the data I need from tables accounts_new, account_ranges and salesunits. CREATE OR REPLACE FUNCTION fnc_test ( INOUT p_dataareaid account

Re: [SQL] Like problem

Tom Lane wrote: Richard Huxton <[EMAIL PROTECTED]> writes: Campbell, Lance wrote: WARNING: nonstandard use of escape in a string literal LINE 1: ...ct c1 from t1 where c1 like '%abc\_%'; Either indicate you are using an escaped string: LIKE E'%abc\_%' Actually that's wrong, what he'd need

Re: [SQL] Like problem

Tom, From your comments the recommended approach moving forward would be to use ESCAPE and define your escape character? Thanks for your help, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -O

Re: [SQL] Like problem

Tom Lane answered this question in a prior post. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ca