[SQL] How to Select with more than one AND in a Where SQL Clause?
Dear friends, I need a SQL sentence like this: Qry = "Select * from Table Where A1 Like '%' AND A2 Like '%' AND A3 Like '%' AND A4 Like '%' AND A5 Like '%'" But in spite of I need a SQL sentence with four (04) AND's in its Where clause, my SQL compiler only allow me using one AND. So How can I solve such a problem. Many thanks in advance! Zenith Vivas _ Charle con sus amigos online usando MSN Messenger: http://messenger.msn.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Why must the function that a trigger calls return "opaque" ???
This seem rather limiting... Suppose I want a trigger that after insert, returns the currval(sequence) of the newly inserted row automatically without having to run another query ??? GP ---(end of broadcast)--- TIP 3: 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] RFC822 Checker
Howdy, Does anyone know of an SQL function that will check a string for compliance with the RFC822 mail address spec? I have a script that sends mail from a db (no, I am not a spammer) but I often have mails sitting in my queue because the MTA (correctly) refuses to process some of the bogus things that users enter by mistake. Thanks, Matthew GET INTERNET ACCESS FROM JUNO! Juno offers FREE or PREMIUM Internet access for less! Join Juno today! For your FREE software, visit: http://dl.www.juno.com/get/web/. ---(end of broadcast)--- TIP 3: 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
Re: [SQL] RFC822 Checker
On Fri, Aug 30, 2002 at 14:07:28 +, Matthew Price <[EMAIL PROTECTED]> wrote: > Howdy, > > Does anyone know of an SQL function that will check a string for compliance with the >RFC822 mail address spec? I have a script that sends mail from a db (no, I am not a >spammer) but I often have mails sitting in my queue because the MTA (correctly) >refuses to process some of the bogus things that users enter by mistake. You might consider storing the unecoded address. If you are using an MTA that doesn't provide a way to input unencoded addresses (e.g. sendmail), you can encode the address before passing it to the MTA or adding it into a header if you need to do that. It isn't even that hard to tack on the person's name (from somewhere else in your database) or other comment on when putting the encoded address into a message header. When collecting input you might ask for confirmation when seeing unusual addresses. This should cut down on the number of bogus ones that get into the sysem. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Why must the function that a trigger calls return "opaque" ???
Greg, > This seem rather limiting... Suppose I want a trigger that after insert, > returns the currval(sequence) of the newly inserted row automatically > without having to run another query ??? Because you can't capture the return value of a trigger. The trigger is returning the new/altered/deleted row. If you want a function to return a value, don't use a trigger. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to Select with more than one AND in a Where SQL Clause?
Zenith, > Qry = "Select * from Table Where A1 Like '%' AND A2 Like '%' AND A3 Like '%' > AND A4 Like '%' AND A5 Like '%'" > > But in spite of I need a SQL sentence with four (04) AND's in its Where > clause, my SQL compiler only allow me using one AND. > > So How can I solve such a problem. This is not a PostgreSQL problem, it is a problem with whatever program you're using. Use different database tools, or access PostgreSQL directly through PSQL. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] query problem
Hi suppose I have the following situation: citydate -+--- London | 2002-08-08 07:05:16+00 London | 2002-07-30 13:08:22+00 London | 2002-07-30 07:39:15+00 London | 2002-07-29 17:51:47+00 London | 2002-07-29 17:45:49+00 London | 2002-07-29 17:45:47+00 Paris| 2002-04-08 15:04:28+00 Paris| 2002-03-29 17:22:18+00 Paris| 2002-02-15 12:50:32+00 Paris| 2002-01-22 11:40:22+00 Paris| 2002-01-07 17:41:23+00 Paris| 2001-11-12 16:37:37+00 Paris| 2001-11-05 15:28:23+00 Paris| 2001-11-05 08:21:19+00 Oslo | 2002-07-19 15:42:20+00 Oslo | 2002-07-19 15:42:18+00 Oslo | 2002-07-18 10:03:58+00 Oslo | 2002-07-18 08:56:30+00 Oslo | 2002-07-17 17:17:27+00 Oslo | 2002-07-17 16:11:38+00 For each city I have a couple of dates in DESC order. For each city i need to get the first record which date comes after a given date. If the given date was for example "2002-07-19 15:39:15+00", I would get the following records: London | 2002-07-29 17:45:47+00 Oslo | 2002-07-19 15:42:18+00 Is there a way to obtain this records by performing one single query and not by making for each city something like "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19 15:39:15+00' ORDER BY date ASC LIMIT 1;"? Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] query problem
Marco,
> Is there a way to obtain this records by performing one
> single query and not by making for each city something like
> "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19
> 15:39:15+00' ORDER BY date ASC LIMIT 1;"?
Close. Try:
SELECT city, MAX("date") as last_date
FROM table
WHERE "date" > $date
GROUP BY city
ORDER BY city
Though as an aggregate query, this will be slow on large tables.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Large number of lookups
I have a large table (>2 Mrows) against which my application runs some 1M queries per day. The queries are almost all of the nature of select PRIMARY_KEY_FLD from MY_TABLE where SECONDARY_KEY_FLD = 'something'; I've optimized the table to the limits of what I can think of, now I'm looking to optimize the application. I'm really only interested in the yes/no question of whether the row exists (the returned PRIMARY_KEY_FLD value is pretty much just for debug documentation). What I'm wondering is whether my application would get better (faster) results if I ran a number of queries at once. For example, I could build SQL that looks like: select PRIMARY_KEY_FLD, SECONDARY_KEY_FLD from MY_TABLE WHERE SECONDARY_KEY_FLD in (first_val, second_val, ...); so that the list contains some number of values (either a limited "slice" of the values I care about, or the whole pile--usually 100-200), and iterate this until I've checked them all. Another technique would be to construct a "UNION" table: select PRIMARY_KEY_FLD, SECONDARY_KEY_FLD from MY_TABLE join ( select first_val as "SECONDARY_KEY_FLD" union select second_val union ...) as TEMP_TABLE using (SECONDARY_KEY_FLD) likewise, either taking some number of my desired values in "chunks" or all at once. What's likely to work better, and why? (I'm going off to write a benchmark script, but I'd like to hear some theoretical answers, too.) -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] query problem
How about: select city, min(date) from thetable where date > '2002-07-19 15:39:15+00' group by city; JLL Marco Muratori wrote: > > Hi > suppose I have the following situation: > > citydate > -+--- > London | 2002-08-08 07:05:16+00 > London | 2002-07-30 13:08:22+00 > London | 2002-07-30 07:39:15+00 > London | 2002-07-29 17:51:47+00 > London | 2002-07-29 17:45:49+00 > London | 2002-07-29 17:45:47+00 > Paris| 2002-04-08 15:04:28+00 > Paris| 2002-03-29 17:22:18+00 > Paris| 2002-02-15 12:50:32+00 > Paris| 2002-01-22 11:40:22+00 > Paris| 2002-01-07 17:41:23+00 > Paris| 2001-11-12 16:37:37+00 > Paris| 2001-11-05 15:28:23+00 > Paris| 2001-11-05 08:21:19+00 > Oslo | 2002-07-19 15:42:20+00 > Oslo | 2002-07-19 15:42:18+00 > Oslo | 2002-07-18 10:03:58+00 > Oslo | 2002-07-18 08:56:30+00 > Oslo | 2002-07-17 17:17:27+00 > Oslo | 2002-07-17 16:11:38+00 > > For each city I have a couple of dates in DESC order. > For each city i need to get the first record which date > comes after a given date. If the given date was for example > "2002-07-19 15:39:15+00", I would get the following > records: > > London | 2002-07-29 17:45:47+00 > Oslo | 2002-07-19 15:42:18+00 > > Is there a way to obtain this records by performing one > single query and not by making for each city something like > "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19 > 15:39:15+00' ORDER BY date ASC LIMIT 1;"? > Thanks. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] query problem
I think you meant min(date)...
Josh Berkus wrote:
>
> Marco,
>
> > Is there a way to obtain this records by performing one
> > single query and not by making for each city something like
> > "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19
> > 15:39:15+00' ORDER BY date ASC LIMIT 1;"?
>
> Close. Try:
>
> SELECT city, MAX("date") as last_date
> FROM table
> WHERE "date" > $date
> GROUP BY city
> ORDER BY city
>
> Though as an aggregate query, this will be slow on large tables.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
