pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL question
19+ messages / 13 participants
[nested] [flat]
* SQL question
@ 2000-07-17 02:07 Carolyn Lu Wong <carolyn@kss.net.au>
0 siblings, 1 reply; 19+ messages in thread
From: Carolyn Lu Wong @ 2000-07-17 02:07 UTC (permalink / raw)
To: pgsql-sql
I have table with the following definition:
create table table1(
account_no int4,
start_date_tme datetime
....
);
The table may contain null values for start_date_time.
When I run the following SQL query, it fails:
select * from table1
where start_date_time::date >= '01/01/2000'::date
and start_date_time::date <= '01/01/2001'::date;
I get error message 'Unable to convert null datetime to date.
It's fine if I run the same SQL query with added condition as follows:
select * from table1
> where account_no = 1
and start_date_time::date >= '01/01/2000'::date
and start_date_time::date <= '01/01/2001'::date;
Is this a bug? Or there's an logical explaination for this?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2000-07-17 02:18 Philip Warner <pjw@rhyme.com.au>
parent: Carolyn Lu Wong <carolyn@kss.net.au>
0 siblings, 2 replies; 19+ messages in thread
From: Philip Warner @ 2000-07-17 02:18 UTC (permalink / raw)
To: Carolyn Lu Wong <carolyn@kss.net.au>; pgsql-sql
At 12:07 17/07/00 +1000, Carolyn Lu Wong wrote:
>I have table with the following definition:
>
> create table table1(
> account_no int4,
> start_date_tme datetime
> ....
> );
>
>The table may contain null values for start_date_time.
>
>When I run the following SQL query, it fails:
>
> select * from table1
> where start_date_time::date >= '01/01/2000'::date
> and start_date_time::date <= '01/01/2001'::date;
>
>I get error message 'Unable to convert null datetime to date.
>
>It's fine if I run the same SQL query with added condition as follows:
>
> select * from table1
>> where account_no = 1
> and start_date_time::date >= '01/01/2000'::date
> and start_date_time::date <= '01/01/2001'::date;
>
>Is this a bug? Or there's an logical explaination for this?
The most logical explanation is that there are no null values in
start_date_time when account_no = 1.
Try
select count(*) from table1 where account_no = 1 and start_date_time is
null;
and see if you get 0.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2000-07-17 03:41 Carolyn Lu Wong <carolyn@kss.net.au>
parent: Philip Warner <pjw@rhyme.com.au>
1 sibling, 0 replies; 19+ messages in thread
From: Carolyn Lu Wong @ 2000-07-17 03:41 UTC (permalink / raw)
To: Philip Warner <pjw@rhyme.com.au>; +Cc: pgsql-sql
Philip Warner wrote:
>
> At 12:07 17/07/00 +1000, Carolyn Lu Wong wrote:
> >I have table with the following definition:
> >
> > create table table1(
> > account_no int4,
> > start_date_tme datetime
> > ....
> > );
> >
> >The table may contain null values for start_date_time.
> >
> >When I run the following SQL query, it fails:
> >
> > select * from table1
> > where start_date_time::date >= '01/01/2000'::date
> > and start_date_time::date <= '01/01/2001'::date;
> >
> >I get error message 'Unable to convert null datetime to date.
> >
> >It's fine if I run the same SQL query with added condition as follows:
> >
> > select * from table1
> >> where account_no = 1
> > and start_date_time::date >= '01/01/2000'::date
> > and start_date_time::date <= '01/01/2001'::date;
> >
> >Is this a bug? Or there's an logical explaination for this?
>
> The most logical explanation is that there are no null values in
> start_date_time when account_no = 1.
>
> Try
> select count(*) from table1 where account_no = 1 and start_date_time is
> null;
>
> and see if you get 0.
Yes, i get 0 from running the above query, but it fails if i re-arrange
the where clause to:
select * from table1
where start_date_time::date >= '01/01/2000'::date
and start_date_time::date <= '01/01/2001'::date
and account_no = 1;
with the same error message.
>
> ----------------------------------------------------------------
> Philip Warner | __---_____
> Albatross Consulting Pty. Ltd. |----/ - \
> (A.C.N. 008 659 498) | /(@) ______---_
> Tel: (+61) 0500 83 82 81 | _________ \
> Fax: (+61) 0500 83 82 82 | ___________ |
> Http://www.rhyme.com.au | / \|
> | --________--
> PGP key available upon request, | /
> and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2000-07-17 04:11 Philip Warner <pjw@rhyme.com.au>
parent: Philip Warner <pjw@rhyme.com.au>
1 sibling, 1 reply; 19+ messages in thread
From: Philip Warner @ 2000-07-17 04:11 UTC (permalink / raw)
To: Carolyn Lu Wong <carolyn@kss.net.au>; +Cc: pgsql-sql
At 13:41 17/07/00 +1000, Carolyn Lu Wong wrote:
>> Try
>> select count(*) from table1 where account_no = 1 and start_date_time is
>> null;
>>
>> and see if you get 0.
>
>Yes, i get 0 from running the above query, but it fails if i re-arrange
>the where clause to:
>
> select * from table1
> where start_date_time::date >= '01/01/2000'::date
> and start_date_time::date <= '01/01/2001'::date
> and account_no = 1;
>
>with the same error message.
>
I think that there is no guarantee of the order of evaluation of the
components of a predicate, but that putting 'account_no=1' early in the
statement means the row is excluded before it needs to evaluate the rest of
the statement. I don't think you should rely on this behaviour - it might
be classified as "it's a feature, not a bug". Maybe.
You probably need to tell me what NULLs in the start_date mean. If, eg,
they mean 'not started', then you could create a view:
create view started_things as select * from table1 where not start_date
is null;
then use:
select * from started_things
where start_date_time::date >= '01/01/2000'::date
and start_date_time::date <= '01/01/2001'::date
and account_no = 1;
AFAICT, this will still use nice indexes etc, but I could be wrong.
Another alternative would be to define a 'coalesce' function (I don't think
PG has one), which takes an arbitrary number of arguments and returns the
first non-null one. You could then say "where coalesce(start_date_time,
'1/1/1500')::date >= '01/01/2000'::date" etc, but then I think you will
lose the effectiveness of indexes.
Maybe someone else has a better idea...
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2000-07-17 05:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Philip Warner <pjw@rhyme.com.au>
0 siblings, 1 reply; 19+ messages in thread
From: Tom Lane @ 2000-07-17 05:48 UTC (permalink / raw)
To: Philip Warner <pjw@rhyme.com.au>; +Cc: Carolyn Lu Wong <carolyn@kss.net.au>; pgsql-sql
Philip Warner <pjw@rhyme.com.au> writes:
> Another alternative would be to define a 'coalesce' function (I don't think
> PG has one), which takes an arbitrary number of arguments and returns the
> first non-null one.
We surely do have that! It even works pretty well in 7.0 ;-)
(I think there were still some bugs lurking in 6.5.)
The immediate cause of this gripe was discussed just a day or so ago
on one or another of the pgsql lists. The timestamp-to-date conversion
routine has this weird idea that it should kick out an error instead
of returning NULL when presented with a NULL timestamp. That's a bug
IMHO, and I've already changed the code in current sources.
COALESCE would be a good workaround if you don't want to patch your
local copy of 7.0.*.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2000-07-17 06:23 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 19+ messages in thread
From: Thomas Lockhart @ 2000-07-17 06:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Philip Warner <pjw@rhyme.com.au>; Carolyn Lu Wong <carolyn@kss.net.au>; pgsql-sql
> The immediate cause of this gripe was discussed just a day or so ago
> on one or another of the pgsql lists. The timestamp-to-date conversion
> routine has this weird idea that it should kick out an error instead
> of returning NULL when presented with a NULL timestamp. That's a bug
> IMHO, and I've already changed the code in current sources.
That's not a bug, that was a feature, sort of. At least when I coded it,
Postgres *refused* to call any routine with NULL input, assuming that
NULL would be returned. A clever short-circuit, and the elog(ERROR) in
the conversion routine was just a safety net. Because it was also the
case that any routine returning a NULL pointer crashed the backend.
Now that those things aren't true, we are rewriting history to say that
they were bugs all along, eh? ;)
- Thomas
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2000-07-17 06:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 19+ messages in thread
From: Tom Lane @ 2000-07-17 06:26 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Philip Warner <pjw@rhyme.com.au>; Carolyn Lu Wong <carolyn@kss.net.au>; pgsql-sql
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> The immediate cause of this gripe was discussed just a day or so ago
>> on one or another of the pgsql lists. The timestamp-to-date conversion
>> routine has this weird idea that it should kick out an error instead
>> of returning NULL when presented with a NULL timestamp. That's a bug
>> IMHO, and I've already changed the code in current sources.
> That's not a bug, that was a feature, sort of. At least when I coded it,
> Postgres *refused* to call any routine with NULL input, assuming that
> NULL would be returned.
Well before my time, I guess --- as long as I've been paying attention,
the function manager's approach was to call the routine first and *then*
insert a NULL result ... if the routine hadn't crashed first. That's
about as braindead a choice as I can think of, but that's what it did.
> A clever short-circuit, and the elog(ERROR) in
> the conversion routine was just a safety net. Because it was also the
> case that any routine returning a NULL pointer crashed the backend.
> Now that those things aren't true, we are rewriting history to say that
> they were bugs all along, eh? ;)
Fixing that one routine to behave that way, when none of the hundreds
of others that might see a NULL input do the same, qualifies as a bug
IMHO. But it's all water over the dam, now that fmgr has been
redesigned.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2000-07-17 06:31 Philip Warner <pjw@rhyme.com.au>
parent: Thomas Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 1 reply; 19+ messages in thread
From: Philip Warner @ 2000-07-17 06:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Carolyn Lu Wong <carolyn@kss.net.au>; pgsql-sql
At 02:26 17/07/00 -0400, Tom Lane wrote:
>
>Well before my time, I guess --- as long as I've been paying attention,
>the function manager's approach was to call the routine first and *then*
>insert a NULL result ... if the routine hadn't crashed first. That's
>about as braindead a choice as I can think of, but that's what it did.
>
Out of curiosity, what does it do now?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2000-07-17 07:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Philip Warner <pjw@rhyme.com.au>
0 siblings, 0 replies; 19+ messages in thread
From: Tom Lane @ 2000-07-17 07:03 UTC (permalink / raw)
To: Philip Warner <pjw@rhyme.com.au>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Carolyn Lu Wong <carolyn@kss.net.au>; pgsql-sql
Philip Warner <pjw@rhyme.com.au> writes:
> At 02:26 17/07/00 -0400, Tom Lane wrote:
>> Well before my time, I guess --- as long as I've been paying attention,
>> the function manager's approach was to call the routine first and *then*
>> insert a NULL result ... if the routine hadn't crashed first. That's
>> about as braindead a choice as I can think of, but that's what it did.
> Out of curiosity, what does it do now?
As of current sources there is a distinction between "strict" and
"non-strict" functions. A strict function must return NULL if any
input is NULL, therefore the function manager won't call it at all
if there is a NULL input value, but just assume the NULL result.
(Some other DBMSes have the same concept under different names,
like "NOT NULL CALL".)
A non-strict function is assumed to be able to take care of itself.
It gets called anyway and must check to see if any of its inputs
are NULL, then decide what it wants to do about it.
Both strict and nonstrict functions can return NULL if they wish,
though I've not seen many cases where a strict function would want to.
This is all predicated on a new function call interface that provides
an explicit isNull flag for each input, as well as an isNull flag for
the function result. The real problem with the old code was that we
didn't have that, and there is no non-kluge workaround for not having
the information.
If I understand Thomas' remarks correctly, at one time in the past
the function-call code operated as though all functions were strict.
I suppose that foundered on the little problem that certain operations
like IS NULL and IS NOT NULL *must* be non-strict. So someone changed
it to the opposite convention, but didn't follow through to the bitter
end: there has to be an explicit null flag for *each* argument, as
well as a clean way for the function to say whether it is returning
a null or not.
Defaulting to non-strict also created a ton of potential crash sites
in routines that couldn't cope with null-pointer inputs. We've been
gradually "fixing" those by adding explicit tests for nulls, but it's
always been a stopgap solution IMHO. Now there's a better way.
99% of the built-in functions in the backend are "strict" and so
will no longer need special checks to defend against null inputs,
because they'll never see 'em again.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* SQL Question
@ 2000-08-01 20:38 Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 0 replies; 19+ messages in thread
From: Daryl Herzmann @ 2000-08-01 20:38 UTC (permalink / raw)
To: pgsql-sql
Hello,
I hope this question is not too novice for this group.
I have a table
portfolio=# \d questions
Table "questions"
Attribute | Type | Modifier
-----------+-------------+-----------------------------------------------------
qid | integer | not null default nextval('questions_qid_seq'::text)
optiona | text | default 'Z'
optionb | text | default 'Z'
optionc | text | default 'Z'
optiond | text | default 'Z'
optione | text | default 'Z'
optionf | text | default 'Z'
optiong | text | default 'Z'
optionh | text | default 'Z'
and other table named quizes, which contains references to the
questions. Basically a quiz is composed of up to three questions.
portfolio=# \d quizes
Table "quizes"
Attribute | Type | Modifier
-----------+-------------+------------------------------------------------------
quiznum | integer | not null default nextval('quizes_quiznum_seq'::text)
question1 | integer |
question2 | integer |
question3 | integer |
So my question is if I want to querry out a particular quiz and all its
questions and question options, how can I do that in one command. I know
that I could do it with a couple of loops, but I think the SQL is much
more eliquent. Maybe I have my tables incorrectly set up? And
suggestions?
Thanks all,
Daryl
^ permalink raw reply [nested|flat] 19+ messages in thread
* SQL question
@ 2001-01-12 13:36 Alain Lavigne <alavigne@zaq.com>
0 siblings, 0 replies; 19+ messages in thread
From: Alain Lavigne @ 2001-01-12 13:36 UTC (permalink / raw)
To: pgsql-sql
I'm trying to extract references (relationships) between tables for the
purpose of reverse/forward engineer from a modeling tool called
PowerDesigner.
Here is the sql:
select u.usename,
p.relname,
v.usename,
c.relname,
t.tgconstrname,
dumpref(t.tgargs, 4),
dumpref(t.tgargs, 5)
from pg_trigger t,
pg_proc f,
pg_class p,
pg_class c,
pg_user u,
pg_user v
where 1=1
and f.proname='RI_FKey_check_ins'
and t.tgfoid=f.oid
and c.oid=t.tgrelid
and p.oid=t.tgconstrrelid
and u.usesysid=p.relowner
and v.usesysid=c.relowner;
I always get the following message:
<< Error while executing the query; ERROR: Function 'dumpref(bytea, int4)'
does not exist Unable to identify a function that satisfies the given
argument types You may need to add explicit typecasts >>
What am I doing wrong ??
Alain Lavigne - Data Administrator - ZAQ.iTv - E-Mail: alavigne@zaq.com
297 St-Paul, West - Montreal, Quebec, Canada - H2Y 2A5
Phone: 514-282-7073 ext: 371
Fax: 514-282-8011
^ permalink raw reply [nested|flat] 19+ messages in thread
* SQL question
@ 2001-02-06 13:37 Alain Lavigne <alavigne@zaq.com>
0 siblings, 0 replies; 19+ messages in thread
From: Alain Lavigne @ 2001-02-06 13:37 UTC (permalink / raw)
To: pgsql-sql
I'm trying to extract references (relationships) between tables for the
purpose of reverse/forward engineer from a modeling tool called
PowerDesigner.
Here is the sql:
select u.usename,
p.relname,
v.usename,
c.relname,
t.tgconstrname,
dumpref(t.tgargs, 4), ** (I know this function does not exist in
postgresql)
dumpref(t.tgargs, 5)
from pg_trigger t,
pg_proc f,
pg_class p,
pg_class c,
pg_user u,
pg_user v
where 1=1
and f.proname='RI_FKey_check_ins'
and t.tgfoid=f.oid
and c.oid=t.tgrelid
and p.oid=t.tgconstrrelid
and u.usesysid=p.relowner
and v.usesysid=c.relowner;
I always get the following message:
** << Error while executing the query; ERROR: Function 'dumpref(bytea,
int4)'
does not exist Unable to identify a function that satisfies the given
argument types You may need to add explicit typecasts >>
Since integrity constraints are done using triggers, i makes sense that I
can reverse engineer those FK constraint from the pg_trigger table.
Unfortunately I don't know how to extract the information from the "tgargs"
field.
CAN ANYONE HELP ??
^ permalink raw reply [nested|flat] 19+ messages in thread
* SQL question
@ 2001-07-06 17:52 Deepali Agarwal <DAgarwal@PPC.com>
0 siblings, 2 replies; 19+ messages in thread
From: Deepali Agarwal @ 2001-07-06 17:52 UTC (permalink / raw)
To: pgsql-sql
hello,
I have a simple question about nested SQL statements. I remember
having learnt of another way of writing nested SQL statements, using Joins I
guess. I'm buildingh an ASP/ADO application or which using nested select
becomes too complicated. Could you please throw some light ( preferably with
an example) on how i can use a join or any other method instead of suing
nested SQL statements. Also, would you be able to tell me how to use a
nested DQL quesry/join query with an ADO command object, i.e the commandtext
property? Thank you for your help.
-Deepali
Deepali Agarwal
Intern
Project Performance Corporation
7600, Colshire Drive
McLean, VA 22101
(703)748-7089
www.ppc.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL question
@ 2001-07-11 00:26 Josh Berkus <josh@agliodbs.com>
parent: Deepali Agarwal <DAgarwal@PPC.com>
1 sibling, 0 replies; 19+ messages in thread
From: Josh Berkus @ 2001-07-11 00:26 UTC (permalink / raw)
To: Deepali Agarwal <DAgarwal@PPC.com>; pgsql-sql
Deepali,
> I have a simple question about nested SQL statements. I
> remember
> having learnt of another way of writing nested SQL statements, using
> Joins I
> guess. ...
I'm afraid that your question is much to general to be answered.
Frankly, I'm not sure what you mean by "nested SQL statement." Further,
It sounds like you need some general knowledge. SInce this is an
internship, ask them to buy you some books:
................Database Design for Mere Mortals
any/all of:.....PostgreSQL Introduction & Concepts
................SQL for Smarties
> Also, would you be able to tell me how to use
> a
> nested DQL quesry/join query with an ADO command object, i.e the
> commandtext
> property? Thank you for your help.
This is the wrong list for questions about ADO. Try pgsql-interfaces
instead, or perhaps MSDN (msdn.microsoft.com).
Good luck!
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: SQL question
@ 2001-07-11 02:51 Robby Slaughter <webmaster@robbyslaughter.com>
parent: Deepali Agarwal <DAgarwal@PPC.com>
1 sibling, 0 replies; 19+ messages in thread
From: Robby Slaughter @ 2001-07-11 02:51 UTC (permalink / raw)
To: Deepali Agarwal <DAgarwal@PPC.com>; pgsql-sql
Deepali,
Bruce Momjian's book on SQL provides a great intro to SQL,
including joins.
http://www.ca.postgresql.org/docs/aw_pgsql_book/
If you have a specific question, please post that to a list
Good luck!
-Robby
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Deepali Agarwal
Sent: Friday, July 06, 2001 12:53 PM
To: 'pgsql-sql@postgresql.org'
Subject: [SQL] SQL question
hello,
I have a simple question about nested SQL statements. I remember
having learnt of another way of writing nested SQL statements, using Joins I
guess. I'm buildingh an ASP/ADO application or which using nested select
becomes too complicated. Could you please throw some light ( preferably with
an example) on how i can use a join or any other method instead of suing
nested SQL statements. Also, would you be able to tell me how to use a
nested DQL quesry/join query with an ADO command object, i.e the commandtext
property? Thank you for your help.
-Deepali
Deepali Agarwal
Intern
Project Performance Corporation
7600, Colshire Drive
McLean, VA 22101
(703)748-7089
www.ppc.com
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
^ permalink raw reply [nested|flat] 19+ messages in thread
* sql question:
@ 2002-07-13 22:24 Chris Aiello <caiell02@comcast.net>
0 siblings, 1 reply; 19+ messages in thread
From: Chris Aiello @ 2002-07-13 22:24 UTC (permalink / raw)
To: pgsql-sql
hi all:
I'm trying to figure out SQL to do the following:
I have an application that tracks SQL that is being sent to the database,
and one of it's features is the ability to identify whether a query is an
insert, update, delete, select, select with all rows returned, the query is
the first in a user session....and many other criteria. Because of the
nature of SQL, i.e. many of the above could be true, the deisgners made each
flag a 'bit'. So an example is:
4 is a select
8 is insert
16 is update
32 is first query in session
64 is delete
128 is a cancelled query
256 is database cancelled query
Now the SQL that I have to find is 'which of these records is a delete?'
The values could be 64, 96, 416, 445, 320 and many others. All in all
there are probably 20 possible values and the permutations are to lengthy to
put in a 'like', so I need some kind of algorithm. Does anyone have any
ideas?
email:
caiell02@comcast.net
thanks, Chris
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: sql question:
@ 2002-07-14 02:38 Ahti Legonkov <lego@127.0.0.1>
parent: Chris Aiello <caiell02@comcast.net>
0 siblings, 0 replies; 19+ messages in thread
From: Ahti Legonkov @ 2002-07-14 02:38 UTC (permalink / raw)
To: pgsql-sql
Chris Aiello wrote:
> hi all:
>
> I'm trying to figure out SQL to do the following:
> I have an application that tracks SQL that is being sent to the database,
> and one of it's features is the ability to identify whether a query is an
> insert, update, delete, select, select with all rows returned, the query is
> the first in a user session....and many other criteria. Because of the
> nature of SQL, i.e. many of the above could be true, the deisgners made each
> flag a 'bit'. So an example is:
> 4 is a select
> 8 is insert
> 16 is update
> 32 is first query in session
> 64 is delete
> 128 is a cancelled query
> 256 is database cancelled query
>
>
>
> Now the SQL that I have to find is 'which of these records is a delete?'
> The values could be 64, 96, 416, 445, 320 and many others. All in all
> there are probably 20 possible values and the permutations are to lengthy to
> put in a 'like', so I need some kind of algorithm. Does anyone have any
> ideas?
The algorithm is as simple as that:
if (value & 64 == 64) {
// it is a delete
}
--
Ahti Legonkov
^ permalink raw reply [nested|flat] 19+ messages in thread
* SQL Question
@ 2004-11-19 05:57 Igor Kryltsov <kryltsov@yahoo.com>
0 siblings, 1 reply; 19+ messages in thread
From: Igor Kryltsov @ 2004-11-19 05:57 UTC (permalink / raw)
To: pgsql-sql
Hi,
If anybody can offer better SQL query please.
We have table
create table group_facility (
group_id integer not null,
facility_id integer not null
)
It stores facilities membership in group. For example: "North Region" -
facilityA, facilityB
I need to extract groups from this table which contain facilityN AND
facilityZ and may be others but these two(both) has to be a group member.
Query:
SELECT DISTINCT group_id FROM facility_group s1
WHERE EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id =
s1.group_id AND facility_id = 390)
AND
EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND
facility_id = 999)
works but what if I need to find groups where membership is (facilityN1,
....facilityN100)??
Thank you,
Igor K
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: SQL Question
@ 2004-11-19 19:23 Mischa Sandberg <ischamay.andbergsay@activestateway.com>
parent: Igor Kryltsov <kryltsov@yahoo.com>
0 siblings, 0 replies; 19+ messages in thread
From: Mischa Sandberg @ 2004-11-19 19:23 UTC (permalink / raw)
To: pgsql-sql
Igor Kryltsov wrote:
> We have table
> create table group_facility (
> group_id integer not null,
> facility_id integer not null
> )
> It stores facilities membership in group. For example: "North Region" -
> facilityA, facilityB
> I need to extract groups from this table which contain facilityN AND
> facilityZ and may be others but these two(both) has to be a group member.
>
> Query:
> SELECT DISTINCT group_id FROM facility_group s1
> WHERE EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id =
> s1.group_id AND facility_id = 390)
> AND
> EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND
> facility_id = 999)
>
> works but what if I need to find groups where membership is (facilityN1,
> ....facilityN100)??
Okay: suppose you have
table my_facilities(facility_id integer)
--- your facilityN1...facilityN100
SELECT group_id
FROM facility_group s1
JOIN my_facilities s2 USING(facility_id)
GROUP BY group_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM my_facilities)
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2004-11-19 19:23 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-17 02:07 SQL question Carolyn Lu Wong <carolyn@kss.net.au>
2000-07-17 02:18 ` Philip Warner <pjw@rhyme.com.au>
2000-07-17 03:41 ` Carolyn Lu Wong <carolyn@kss.net.au>
2000-07-17 04:11 ` Philip Warner <pjw@rhyme.com.au>
2000-07-17 05:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-17 06:23 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-07-17 06:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-07-17 06:31 ` Philip Warner <pjw@rhyme.com.au>
2000-07-17 07:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-01 20:38 SQL Question Daryl Herzmann <akrherz@iastate.edu>
2001-01-12 13:36 SQL question Alain Lavigne <alavigne@zaq.com>
2001-02-06 13:37 SQL question Alain Lavigne <alavigne@zaq.com>
2001-07-06 17:52 SQL question Deepali Agarwal <DAgarwal@PPC.com>
2001-07-11 00:26 ` Josh Berkus <josh@agliodbs.com>
2001-07-11 02:51 ` Robby Slaughter <webmaster@robbyslaughter.com>
2002-07-13 22:24 sql question: Chris Aiello <caiell02@comcast.net>
2002-07-14 02:38 ` Re: sql question: Ahti Legonkov <lego@127.0.0.1>
2004-11-19 05:57 SQL Question Igor Kryltsov <kryltsov@yahoo.com>
2004-11-19 19:23 ` Re: SQL Question Mischa Sandberg <ischamay.andbergsay@activestateway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox