Re: [SQL] Using Query Result in WHERE Clause
On Fri, Jun 02, 2006 at 01:06:40PM -0700, [EMAIL PROTECTED] wrote: > hi all, > > SUMMARY: > > i want to replace > > AND t_inspect.inspect_pass = 'f' > > with a complex query that yields the same result. the > complex query is in the form of... [snip] But your complex query isn't in the form where anything will come out "= 'f'". You have too many columns in the SELECT. What you prabably need to do is rewrite it as a subquery in the FROM clause, alias it as (say) alias_t_inspect, and then use your condition to get you what you want. I won't warrant what the performance will be, however. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Get max value from an comma separated string
Mauro Bertoli wrote: Hi, I've a field that contain values-comma-separated like A) 1;2;3;;5 -- ;2;;4;5 but also B) 12;34;18 how I can get the max value? For A I tried: SELECT max(array_upper(string_to_array(answer,';'),1)) FROM values; and work fine, but for B case I don't find a solution like SELECT max(string_to_array(answer,';')) FROM values; Any ideas? Thanks for any hint You could try rearranging the values into rows, like so: CREATE OR REPLACE FUNCTION text2rows (TEXT,TEXT) RETURNS SETOF TEXT AS $$ SELECT (string_to_array($1, $2))[x.i] FROM generate_series(1,array_upper(string_to_array($1,$2),1)) AS x(i); $$ language sql strict; select max(val) from (SELECT text2rows(answer,';') as val FROM answer) as t; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] How To Exclude True Values
hi all, how can i exclude true values for this query? http://www.rafb.net/paste/results/obtkGz26.html if i uncomment out --AND t_inspect_result.inspect_result_pass = 'f' it looks for prior falses within an inspect_id and returns it. i want the original result set minus the trues, if possible. tia... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How To Exclude True Values
> hi all, > > how can i exclude true values for this query? > > http://www.rafb.net/paste/results/obtkGz26.html > > if i uncomment out > > --AND t_inspect_result.inspect_result_pass = 'f' > > it looks for prior falses within an inspect_id and > returns it. i want the original result set minus > the > trues, if possible. > > tia... this SQL appears to do the trick... http://www.rafb.net/paste/results/zZKIjH80.html __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How To Exclude True Values
> > hi all, > > > > how can i exclude true values for this query? > > > > http://www.rafb.net/paste/results/obtkGz26.html > > > > if i uncomment out > > > > --AND t_inspect_result.inspect_result_pass = > 'f' > > > > it looks for prior falses within an inspect_id and > > returns it. i want the original result set minus > > the > > trues, if possible. > > > > tia... > > this SQL appears to do the trick... > > http://www.rafb.net/paste/results/zZKIjH80.html > the finished query can be found here: http://www.rafb.net/paste/results/Cu2FoO56.html working out that query sure felt an awful lot life fighting a fire breathing dragon... with tissue paper armour... -lol- __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How To Exclude True Values
> > how can i exclude true values for this query? > > > > http://www.rafb.net/paste/results/obtkGz26.html > > > > if i uncomment out > > > > --AND t_inspect_result.inspect_result_pass = 'f' > > > > it looks for prior falses within an inspect_id and > > returns it. i want the original result set minus > > the > > trues, if possible. > > > > tia... > > this SQL appears to do the trick... > > http://www.rafb.net/paste/results/zZKIjH80.html I have one idea that hopefully wont complicate you query but it could simplfy your query by getting rid of the query nexting. Also, I haven't tested it. Basically, Replace the DISTINCT ON (t_inspect.inspect_id) construct with GROUP BY t_inspect.inspect_id HAVING t_inspect_result.inspect_result_pass = 'f' Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Update Problem
Hi, I’m encountering some weird problem on Update. I have a program that updates a table and it updates more than 1 column at a single query. Like this one: Update table1 set column1 = ‘a’, column2 = ‘b’, column3 = time ’08:00 AM’ where column1 = ‘c’ Sometimes only column1 and column2 is updated and column3 is not updated. What could be the possible cause of this? Thanks, IANIAN
