[SQL] subqueries as values in updates

[patrick . jacquot]

hello, list.
are subqueries alloved as values in an update command ?
e.g.

update a set a.attribute1 = a.attribute1 +
(select sum(b.attribute1) from b where b.attribute2=a.attribute2);

If yes, how is the correct syntax ?
If not, is there a hope for it in a future version ?

Re: [SQL] Query Help

[patrick . jacquot]

"Brian C. Doyle" wrote:

> What do I have to do a query where information in table1 is not in table2
>
> I am looking for something like
>
> Select table1.firstname where table1.firstname is not in table2.firstname
> and table2.date='yesterday'
>
> I tried
> Select table1.firstname where table1.firstname != table2.firstname and
> table2.date='yesterday'
>
> and that did not work just listed everyone

imho the most natrural way for what you need seems to be :

select  whatyouwant from table1 where not exists
select * from table2 where table2.firstname = table1.firstname and
table2.date='yesterday';

hoping that helps
P. Jacquot

Re: [SQL] select returns no line

[patrick . jacquot]

Attila Kevei wrote:

> Hi,
>
> Our database has a SELECT problem using varchar columns in WHERE clause
> (but not in all rows!!!).
> We can fix the whole table (or just the row) as shown below but later it
> seems wrong again (and after the fix the row isn't UPDATEd).
>
> Any idea?
>
> Thanks
> Attila
>
> 
> Environment:
>
> [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2]
>
> goodwill=>\d users
> Table= users
> +--+--+---+
> |  Field   |  Type| Length|
> +--+--+---+
> | user_id  | int4 not null default nextval (  | 4 |
> | user_login   | varchar() not null   |15 |
> | user_passwd  | varchar() not null   |15 |
> | user_exp | timestamp| 4 |
> +--+--+---+
> Indices:  users_pkey
>
> users_user_login_key
>
> 
> The problem:
>
> goodwill=>select * from users where user_login='test';
> user_id|user_login|user_passwd|user_exp
> ---+--+---+
> (0 rows)
>
> goodwill=> select * from users where user_id=4;
> user_id|user_login|user_passwd  |user_exp
> ---+--+-+
>   4|test  |0PDv7a2EESjZo|
> (1 row)
>
> goodwill=> update users set user_login=user_login where user_id=4;
> UPDATE
> 1
>
> goodwill=>select * from users where user_login='test';
> user_id|user_login|user_passwd  |user_exp
> ---+--+-+
>   4|test  |0PDv7a2EESjZo|
> (1 row)
>
> --
> x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x

hello
are you sure the value of the user_id in that line is "test" and not "test "
i.e it has not spurious spaces at the end of it ?
HTH
Patrick

Re: [SQL] Comparing dates

[patrick . jacquot]

Markus Fischer wrote:

> Hello,
>
> I've a SELECT statement on many joined Tabled and one of them has
> a date column called 'date_date'. When I fetch a date e.g.
> '02-03-2001', I get, say, 60 results back. When I now perform the
> same query with another date, lets take '03-03-2001', I get back
> about 70 results.
>
> When I now modify my query to get both results in one I write
>
> SELECT
> 
> FROM
> ..
> AND
> date_date >= '2001-03-02'
> AND
> date_date <= '2001-03-03'
> AND
> 
>
> I think I should get back the rows for both days, 60 + 70 makes
> 130 to me. But what I get back is even smaller then 60. I
> allready tried TO_DATE conversion, an OR construct but always
> the same result.
>
> Is there something special to know when comparing/working with
> date-datetypes ?
>
> kind regards,
> Markus
>
> --
> Markus Fischer,  http://josefine.ben.tuwien.ac.at/~mfischer/
> EMail: [EMAIL PROTECTED]
> PGP Public  Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc
> PGP Fingerprint: D3B0 DD4F E12B F911 3CE1  C2B5 D674 B445 C227 2BD0
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

ANDing restrictions makes them narrower.
If you want to broaden your selection, try  ORing the conditions
HTH
Patrick


---(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] referencing oid impozsible ?

[patrick . jacquot]

hello all
I tried recently (pgsql 7.1.2) to establish the oid of one table as foreign key
in another. 
To no avail : there was no uniqueness constraint on that column
Naturally, it seems impossible to add a uniqueness constraint to such a system
column.
As far as i know, the oid is by nature unique, excepting for the case of
wrapping of their sequence.
Is there any workaround ?

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] automatic update or insert

[Patrick JACQUOT]

tobbe wrote:


Hi.

I have a little problem.

In a system of mine i need to insert records into table [tbStat], and
if the records exist i need to update them instead and increase a
column [cQuantity] for every update.

I.e. the first insert sets cQuantity to 1, and for every other run
cQuantity is increased.

Currently i have implemented this as a stored procedure in the plpgsql
language. This means that in my stored procedure i first do a select to
find out if the row exists or not, then i do a insert or update
depending if the row existed.

Unfortunately, stored procedures seems awfully slow. And i need the
application to go faster.

One solution could be to implement the stored procedure in my program
instead. I think that this will be atleast 50% faster than my stored
procedure, so that would be ok.

However, this has made me thinking. Couldn't this be done directly in
SQL?


Brgds Robert


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

 

Maybe would it be better to insert always, and to use grouping and 
summation when using the table. That would enable you to preserve the 
history of events.

That's how I almost always work
hth
P.Jacquot

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Just 1 in a series...

[Patrick JACQUOT]

Mark Fenbers wrote:


What would have to be done if I needed a standard SQL solution?
Mark


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Maybe you could t'ry something like :
Select whatever  from  yourtable a where not exists
(select * from yourtable b where b.id=a.id and b.timestamp > a.timestamp)

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] RETURN SET OF DATA WITH CURSOR

[Patrick JACQUOT]

grupos wrote:


Hi Richard,

Thanks for your promptly answer. I don't have experience returning 
refcursor but my choice would be it.
I read the documentation but I didn't find any example with my 
necessity...


Could you give me a small example on the same "basis" that I want?

Thanks,

Rodrigo Carvalhaes

Richard Huxton wrote:


grupos wrote:


Hi !

I am making one plpgsql function and I need to return of setof data 
using a cursor.


The problem is that my function is returning only the first row and 
column of the query. This query have more than 80 columns and 1.000 
rows. Enyone have any tip to give me?



Yes - decide whether you are returning a set of rows or a refcursor.

Check the plpgsql docs again for how to return SETOF  using 
the RETURN NEXT statement. Then you call your function like: SELECT * 
FROM my_func();




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


There is a standard way :

Select * from memberstatus A where not exists
   (select * from emberstatus B where B.member_id=A.member_id and  
B.status_date >A.status_date)


---(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] Need SQL Help Finding Current Status of members

[Patrick JACQUOT]

Richard Huxton wrote:


Michael Avila wrote:

I have a table which keeps track of the status of members. In the 
table is


member_id int(8)
status_code char(1)
status_date date
KEY member_id (member_id,status_code,status_date)


Each member can have multiple records because a record is added each 
time

the status changes but the old record is kept for history.

What I want to do is find the latest status for each member.



Michael Fuhr has already described on solution, but if you can alter 
the table definition then there might be a neater solution.


Replace "status_date" with "status_expires" and make it a "timestamp 
with time zone". Set the expiry to 'infinity' for the current record 
and  you then have a simple select to find the most recent.


If you regularly want to find which record was active on a particular 
time you'll want two columns: valid_from and valid_to. This makes it 
much easier to find a row for a specific date.


There is a standard way :

Select * from memberstatus A where not exists
  (select * from emberstatus B where B.member_id=A.member_id and  
B.status_date >A.status_date)


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Matching several rows

[Patrick JACQUOT]

Michael Glaesemann wrote:



On Jan 18, 2006, at 21:48 , Volkan YAZICI wrote:


AFAICS, the bottleneck in above query is ANY(ARRAY[]) clause usage.
Instead of that, if you replace «rights = ANY(ARRAY[2,5,10])» with
«rights IN (2,5,10)» it's overhead decreases to 0.200-0.300ms domain.



explain analyze
SELECT id
FROM (
SELECT id, sum(1) AS s
FROM urights
WHERE uright in (2,5,10)
GROUP BY id) AS t
WHERE s = 3;
 QUERY PLAN
 
-
Subquery Scan t  (cost=1.14..1.19 rows=2 width=4) (actual  
time=0.106..0.108 rows=1 loops=1)
   ->  HashAggregate  (cost=1.14..1.17 rows=2 width=4) (actual  
time=0.103..0.105 rows=1 loops=1)

 Filter: (sum(1) = 3)
 ->  Seq Scan on urights  (cost=0.00..1.10 rows=4 width=4)  
(actual time=0.029..0.038 rows=5 loops=1)

   Filter: ((uright = 2) OR (uright = 5) OR (uright = 10))
Total runtime: 0.386 ms
(6 rows)


Michael Glaesemann
grzm myrealbox com




---(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


why not use an having clause in the GROUP BY?
HTH

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] problem referencing an attrib which is not unique

[Patrick JACQUOT]

Vic Rowan wrote:




-- Forwarded message --
From: *Vic Rowan* <[EMAIL PROTECTED] >
Date: Feb 7, 2006 2:31 PM
Subject: problem referencing an attrib which is not unique
To: pgsql-sql@postgresql.org 


hello everybody,

I need some thing like this below for an application which stores log 
messages in multiple languages. The table 'event_msg' stores 
predefined messages in multiple languages which can be populated with 
place holder values from the application. (These of course are 
language independent). So, the event_id associates these predefined 
messages from both the tables so that displaying a log message is as 
simple as looking up the event_id from the 'logs' table and similarly 
looking up the event_id and language from the 'event_msg' table to 
retreive the predefined_msg with the correct language - the 
application determines the lang from a settings file - and combining 
them to display the log message.


CREATE TABLE event_msg (
  event_id varchar(30) NOT NULL,
  language char(2) NOT NULL,
  predefined_msg varchar(250) NOT NULL,
  PRIMARY KEY (event_id, language)
);

CREATE TABLE logs (
  id int NOT NULL,
  event_id varchar(30) REFERENCES event_msg (event_id) NOT NULL, 
  placeholder_values varchar(250),

  priority varchar(20) NOT NULL,
  timestamp Date NOT NULL,
  primary key (id)
);


The problem I am facing is the event_id from logs is not able to 
reference event_id from event_msg as its not unique.
There are as many entries for each event_id as there are languages 
supported in the 'event_msg' table.
I would be glad if somebody could suggest some work around here to the 
above structure. Or alternately do I need to alter the table structure 
altogether and if so what is the better way of doing this?


Thanks in advance for any help offered.

Cheers,
Vic Rowan.


I think you need three tables

One to list the allowable events, which will be used as reference
CREATE TABLE eventlist (
 event_id varchar(30) PRIMARY-KEY
);

One to give the messages translations
CREATE TABLE messagetranslations(
event-id varchar(30) references eventlist (event_id) NOT NULL
language char(2) not null
event-translation varchar(250)
PRIMARY KEY (event_id, language)
);

and your log table
CREATE TABLE logs (
 id int NOT NULL,
 event_id varchar(30) REFERENCES eventlist (event_id) NOT NULL, 
 placeholder_values varchar(250),

 priority varchar(20) NOT NULL,
 timestamp Date NOT NULL,
 primary key (id)
);

btw,  event-id could be just an integer. If, as I  understand, event-id 
is so large a string,

it's probably because it contains the english name of the event.
Just put it in an occurrence of messagetranslation, with language = 'EN'
other thing : with only 2 chars as language id, how do you distinguish 
EN-US and EN-UK

(or whatever id  the latter can have assigned)?.

hth
P. Jacquot


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Non Matching Records in Two Tables

[Patrick JACQUOT]

Ken Hill wrote:

I need some help with a bit of SQL. I have two tables. I want to find 
records in one table that don't match records in another table based 
on a common column in the two tables. Both tables have a column named 
'key100'. I was trying something like:


SELECT count(*)
FROM table1, table2
WHERE (table1.key100 != table2.key100);

But the query is very slow and I finally just cancel it. Any help is 
very much appreciated.


-Ken 


Maybe you could use a NOT EXISTS subquery, as in
SELECT count(*) from table1
WHERE NOT EXISTS(SELECT count(*) from table2 WHERE table1.key100 
=table2.key100)
which gives you the number of records in table1 without corresponding 
records in table2.

That kind of query is quite fast, if there exists an index on table2.key100
hth
P.Jacquot

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] SELECT on a to-be-determined table

[Patrick JACQUOT]

garhone wrote:


Hi,

I'm a new at this. So please forgive if I mess up. Also, if there is
already a reference/tutorial somewhere, feel free to point me to it.

Here's my situation:
db=# select * from projects;
projid | projname
+--
 1 | cars
 2 | houses
 3 | pets
(3 rows)

db=# select * from cars;
carid | carname
---+-
1 | ford
2 | mazda
(2 rows)

db=# select * from houses;
houseid | housename
-+---
  1 | semi
  2 | trailer
  3 | mansion
(3 rows)

db=# select * from pets;
petid | petname
---+-
1 | dog
2 | cat
3 | bird
(3 rows)

Is it possible to do this:
Give me all the rows of the table whose project id is 2 (or whatever
projid).

Thanks


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org



Your way of thinking leads to the need of comparing a field to a table name.
Such a request requires two steps
1 - retrieve the name of the table to search in, store it in a variable
2 - use execute to issue a request to that table.

Instead, I think it would be better to use only two tables:
1 - projects (projid, projname)
2 - items (itemid, projid, itemproperty1,itemidproperty2,...)
You would have in the second table, to take your example:
projid | itemid | itemname   |
1 |  1 | ford   |
1 |  2 | mazda  |
2 |  1 | semi   |
2 |  2 | trailer|
2 |  3 | mansion|
3 |  1 | dog|
3 |  2 | cat|
3 |  3 | bird   |
Your request would become :
SELECT itemid, itemname FROM items where projid=2

The problem of having a different set of properties
for the items of differents projects could be solved with three tables:
project(projid, projname)
itempropertymeaning(projid, propid, propmeaning)
itemproperty(projid, itemid, propid, propvalue)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Need help: Find dirty rows, Update, Delete SQL

[Patrick JACQUOT]

Janning Vygen wrote:


Am Samstag, 18. Februar 2006 18:41 schrieb [EMAIL PROTECTED]:
 


Hello,

I need a bit of help with some SQL.
I have two tables, call them Page and Bookmark.
Each row in Page can have many Bookmarks pointing to it, and
they are joined via a FK (Page.id = Bookmark.page_id).

Page has a 'url' column: Page.url, which has a unique index on it.

My Page.url column got a little dirty, and I need to clean it up,
and that's what I need help with.

Here is an example of dirtiness:

Page:

id=1 url = 'http://example.com/'
id=2 url = 'http://example.com/#' -- dirty
id=3 url = 'http://example.com/#foo'  -- dirty

The last two rows are dirty.  Normally I normalize URLs before
inserting them, but these got in, and now I need to clean them.

The problem is that rows in Bookmark table may point to dirty
rows in Page, so I can't just remove the dirty rows, and I can't
just update 'url' column in Page to 'http://example.com/',
because that column is unique.

Is there some fancy SQL that I can use them to find the dirty
rows in page (... where url like '%#%') and then find rows in
Bookmark table that point to them, then point those rows to
good rows in Page (e.g. id=1 row above), and finally remove the
dirty rows from Page?
   



try this. But please check if it really does its job. I just wrote it down in 
a minute or two. There will be an easier way or nicer written SQL but a sit 
is just a one time operation you shoudn't care too much. One more hint: you 
should add a CHECK clause to your page_url like  "page_url text NOT NULL 
UNIQUE CHECK (page_url !~ '#')"


here is my test code

CREATE TABLE pages (
 page_id SERIAL PRIMARY KEY,
 page_url text NOT NULL UNIQUE
);

CREATE TABLE bookmarks (
 bm_id SERIAL PRIMARY KEY,
 bm_text text not null,
 page_id int4 NOT NULL REFERENCES pages (page_id)
);

INSERT INTO pages (page_url) VALUES ('http://example.com/');
INSERT INTO pages (page_url) VALUES ('http://example.com/#');
INSERT INTO pages (page_url) VALUES ('http://example.com/#foo');
INSERT INTO pages (page_url) VALUES ('http://example2.com/#foo');
INSERT INTO pages (page_url) VALUES ('http://example3.com/#foobar');

insert into bookmarks (bm_text, page_id) values ('test1', 1);
insert into bookmarks (bm_text, page_id) values ('test2', 1);
insert into bookmarks (bm_text, page_id) values ('test3', 2);
insert into bookmarks (bm_text, page_id) values ('test4', 2);
insert into bookmarks (bm_text, page_id) values ('test5', 3);
insert into bookmarks (bm_text, page_id) values ('test6', 3);
insert into bookmarks (bm_text, page_id) values ('test7', 4);

BEGIN;
UPDATE bookmarks set page_id = pages2.page_id
FROM 
 pages AS pages1, 
 pages AS pages2
WHERE 
 pages1.page_id = bookmarks.page_id

 AND pages2.page_url = split_part(pages1.page_url, '#', 1)
;

DELETE FROM pages WHERE page_id IN (
 SELECT 
   pages1.page_id

 FROM
   pages AS pages1 
   JOIN pages AS pages2 ON ( 
 pages1.page_id != pages2.page_id 
 AND pages2.page_url = split_part(pages1.page_url, '#', 1)

   )
 WHERE position('#' in pages1.page_url) > 0
 AND pages1.page_id NOT IN (SELECT page_id FROM bookmarks)
);
;

UPDATE pages SET page_url = split_part(page_url, '#', 1) 
WHERE position('#' in pages.page_url) > 0

;
select * from bookmarks;
select * from pages;
COMMIT;


kind regards,
janning

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

 

why do you consider as dirty perfectly honest URLs as 
http://example.com#foo ?
Such a construct points to a specific part (foo) of a specific document 
(http://example.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] Referential integrity broken (8.0.3), sub-select help

[Patrick JACQUOT]

[EMAIL PROTECTED] wrote:


Hello,

I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to 
"url" via FK.
Somehow I ended up with some rows in B referencing non-existent rows in U.
This sounds super strange and dangerous to me, and it's not clear to me how/why 
PG let this happen.
I'm using 8.0.3.

Here are the table references I just mentioned:

Table "bookmark":
id  SERIAL
CONSTRAINT pk_bookmark_id PRIMARY KEY

Table "url":
   url_id  INTEGER
CONSTRAINT fk_bookmark_id REFERENCES bookmark(id)

 

Your DDL  doesn't say :  "B references U", but the contrary : "U 
references B".
So it's perfectly right that somes tuples in B are not referenced by 
tuples in U.

Please correct your constraints.


Problem #1: Strange that PG allowed this to happen.  Maybe my DDL above allows 
this to happen and needs to be tightened?  I thought the above would ensure 
referential integrity, but maybe I need to specify something else?

Problem #2: I'd like to find all rows in B that point to non-existent rows in 
U.  I can do it with the following sub-select, I believe, but it's rather 
inefficient (EXPLAIN shows both tables would be sequentially scanned):

 SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u 
WHERE b.url_id=u.id);

Is there a more efficient way to get the rows from "bookmark"?

Thanks,
Otis


---(end of broadcast)---
TIP 6: explain analyze is your friend

 


I think, for that one Scott's answer is OK
You could also try SELECT * FROM url U WHERE NOT EXISTS(SELECT * FROM 
bookmark B WHERE B.url-id=U.id)

and see wich one is faster

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] insert related data into two tables

[Patrick JACQUOT]

[EMAIL PROTECTED] wrote:


Hello,

I have two tables like these:

TABLE_1:  people registry
fields: ID_T1, SURNAME, NAME
ID_T1 is primary key

TABLE_2: work groups
fields: ID_T2, TASK
ID_T2 is foreign key related to ID_T1

the first table is the list of employees, the second the task.

Sometime I need to insert a new employee and at the same time the task
for him:
I want to INSERT TO table 1 JOHN DOE (the ID is assigned automatically
since it's a primary key) and INSERT TO table 2 the ID and the task
name for JOHN DOE. Is it possible to create a single query? What is the
best way to do this?

Thanks,

Filippo


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

 


maybe you could create a view for the join of the two tebles,
then make that view writable by creating the
appropriate triggers.
Then a single insert into the view would in fact create everything.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] some error when executing query in pgAdmin tool

[Patrick JACQUOT]

Penchalaiah P. wrote:

I created one table in pgAdmin tool but when I am executing query it 
is giving error….


CREATE TABLE "ADV"

(

"T-Section_Id" varchar(10) NOT NULL,

"CDA_No" varchar(7) NOT NULL,

"Imp_Schedule_Id" int4 NOT NULL,

"Sanction_No" varchar(20) NOT NULL,

"Sanction_Date" date NOT NULL,

"Station_From" varchar(20) NOT NULL,

"Station_To" varchar(20) NOT NULL,

"Amt_Claimed" int4,

"Amt_Admitted" int4,

"Dak_Id" varchar(20) NOT NULL,

"Refund_Dak_Id" int4 NOT NULL,

"T-Wing_Allowance_Id" varchar(10) NOT NULL,

CONSTRAINT "ADV_pkey" PRIMARY KEY ("T-Section_Id")

)

WITHOUT OIDS;

ALTER TABLE "ADV" OWNER TO postgres;

Above table I created…..

Select * from ADV; when I am executing this query I am getting error is

Relation ADV does not exist… like this error is giving … may I know y 
am I getting this error


*Thanks & Regards*

*Penchal reddy **|** Software Engineer *

*Infinite Computer Solutions **|** Exciting Times…Infinite 
Possibilities... *


*SEI-CMMI level 5 **| **ISO 9001:2000*

*IT SERVICES **|** BPO *

*Telecom **|** **Finance **|** **Healthcare **| **Manufacturing **|** 
**Energy & Utilities **|** **Retail & Distribution **|** **Government *


*Tel +91-80-5193-(Ext:503)**|** Fax +91-80-51930009 **|** Cell No 
+91-9980012376**|**www.infics.com** *


*Information transmitted by this e-mail is proprietary to Infinite 
Computer Solutions and/ or its Customers and is intended for use only 
by the individual or entity to which it is addressed, and may contain 
information that is privileged, confidential or exempt from disclosure 
under applicable law. If you are not the intended recipient or it 
appears that this mail has been forwarded to you without proper 
authority, you are notified that any use or dissemination of this 
information in any manner is strictly prohibited. In such cases, 
please notify us immediately at** [EMAIL PROTECTED] _**and delete 
this mail from your records.*



by quoting ADV in the CREATE TABLE you made it case-sensitive
then by not quoting it in the SELECT, you used the case-insensitive form,
wich PostgreSQL (IIRC) translates as lower case
adv != ADV

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Am I crazy or is this SQL not possible

[Patrick Jacquot]

Collin Peters wrote:


I am having some serious mental block here.  Here is the abstract
version of my problem.  I have a table like this:

unique_id (PK)   broadcast_id   date_sent  status
1  1 2005-04-0430
2  1 2005-04-01 30
3  1 2005-05-20 10
4  2 2005-05-29 30

So it is a table that stores broadcasts including the broadcast_id,
the date sent, and the status of the broadcast.

What I would like to do is simply get the last date_sent and it's
status for every broadcast.  I can't do a GROUP BY because I can't put
an aggregate on the status column.

SELECT MAX(date_sent), status
FROM broadcast_history
GROUP BY broadcast_id

How do I get the status for the most recent date_sent using GROUP BY?

DISTINCT also doesn't work

SELECT DISTINCT ON (email_broadcast_id) *
FROM email_broadcast_history
ORDER BY date_sent

As you have to have the DISTINCT fields matching the ORDER BY fields.
I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent

I keep thinking am I missing something.  Does anybody have any ideas?

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


You can also try
SELECT * from broadcast_history A WHERE NOT EXISTS
(SELECT * from broadcast_history B WHERE B.date_sent >A.date_sent)
There isn't any PostgreSQL-ism, just a correlated subrequest wich is 
perfectly standars, afaik



---(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] "CASE" is not a variable

[Patrick Jacquot]

Keith Worthington wrote:


Hi All,

The following is a section of code inside an SQL function.   When I attempt to
run it I get the error message '"CASE" is not a variable'.  If I split this into
two queries (one for each variable) it works fine.  Obviously I have a work
around but I would like to understand what I am doing wrong.  TIA

SELECT tbl_item_bom.so_subline INTO v_so_subline,
  CASE WHEN tbl_mesh.mesh_type = 'square' THEN
( CASE WHEN tbl_mesh.unit_of_measure = 'in' THEN  
tbl_mesh.mesh_size

   WHEN tbl_mesh.unit_of_measure = 'ft' THEN 12.0*
tbl_mesh.mesh_size
   WHEN tbl_mesh.unit_of_measure = 'mm' THEN 25.4*
tbl_mesh.mesh_size
   WHEN tbl_mesh.unit_of_measure = 'cm' THEN  2.54   *
tbl_mesh.mesh_size
   WHEN tbl_mesh.unit_of_measure = 'm'  THEN  0.0254 *
tbl_mesh.mesh_size
   ELSE 0
  END
)
  WHEN tbl_mesh.mesh_type = 'diamond' THEN
( CASE WHEN tbl_mesh.unit_of_measure = 'in' THEN  
tbl_mesh.mesh_size / 2.0

   WHEN tbl_mesh.unit_of_measure = 'ft' THEN 12.0*
tbl_mesh.mesh_size / 2.0
   WHEN tbl_mesh.unit_of_measure = 'mm' THEN 25.4*
tbl_mesh.mesh_size / 2.0
   WHEN tbl_mesh.unit_of_measure = 'cm' THEN  2.54   *
tbl_mesh.mesh_size / 2.0
   WHEN tbl_mesh.unit_of_measure = 'm'  THEN  0.0254 *
tbl_mesh.mesh_size / 2.0
   ELSE 0
  END
)
  ELSE 0
  END INTO v_mesh_size
 FROM sales_order.tbl_item_bom
 LEFT JOIN peachtree.tbl_mesh
   ON tbl_item_bom.item_id = tbl_mesh.item_id
WHERE tbl_item_bom.so_number = rcrd_line.so_number
  AND tbl_item_bom.so_line = rcrd_line.so_line
  AND tbl_item_bom.component_type = 'net';

Kind Regards,
Keith

---(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

 


shouldn't your CASE construct be in the select list, i.e
SELECT tbl_item_bom_so.subline, CASE ... END INTO ... FROM ...WHERE ...
?
HTH
--
Patrick

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] graph plottin engines compatible with postgres

[Patrick Jacquot]

Parang Saraf wrote:


hey,

I wanted to know about graph plotting engine that is compatible with 
postgres 8.1.4. I am performing some queries like :
Comparing the speed of three runners namely A, B and C versus time. I 
want to publish the result in a graphical manner on the net instead of 
just providing the data.


So, if you could provide me with some information on how i can do it 
then that would be great.


thanks and regards
parang saraf
[EMAIL PROTECTED] 


I use gnuplot to do that kind of stuff since 1997.
Example for two curves on the same graph :

LANG=fr
LINGUAS=fr
psql editor < plotdata
select distinct d, totblo, utiblo from area where tp='$1' and area = 
'EDDON01'  and d > 'today'::date-365 order by d;

\o
\q
finpsql
gnuplot 

Re: [SQL] simple problem???

[Patrick Jacquot]

ivan marchesini wrote:


Dear users
It's a simple problem I think (and I hope  :-)

I have a table like this:
A|B|C
1|2|20
1|3|10
1|4|21
2|3|12
2|4|22
3|4|23

where the first two column are a combination without repetition (with
k=2) of the numbers 1,2,3,4
for each pair I have a value in the column C.

I would want a select that can extract these records:

1|3|10
2|3|12
3|4|23


i.e. the select must look into the A field first and: 
-select all records where A=1, 
-find, into this selection, the record where there's the minimum value
of the field C 
-print all the fields for this record.


then

-select all records where A=2
-find, into this selection, the record where there's the minimum value
of the field C 
-print all the fields of this record.


and so on...

using 
SELECT a,MIN(c) FROM table GROUP BY a


is a partial solution because I can't see the value of B
and I obtain:

1|10
2|12
3|23

How can I do for plotting also the value of B???

Thank you very much

Ivan
 


Maybe you could try
Select distinct on (a,b) a,b,c from(select * from table order by A,C)
The distinct on construct is a postgreSql-ism

Cordialement
--
Patrick


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] GROUP by finish&&last day of month

[Patrick Jacquot]

Antti Linno wrote:

> Ok, if I want to get non-aggregat data in groups, I use order by. This
> group by seemed so logical though, but the fruit that u can't have, is
> usually the most sweet.
>
> New question, how to get the last day of month(order data by last day of
> month). And to prevent chain letter from misunderstanding, no I don't
> intend to look it up from calendar and then input it manually.
>
> A.

to get last day of month :
1) use date-trunc to truncate to first day of month
2) add one month
3) substract 1 day

Re: [SQL] confused by select.

[Patrick Jacquot]

John wrote:

> Hello.  I'm trying to do a select here that i have looked at from many
> angles and cannot find a solution too.  My main problem, (i believe) is
> that it is trying to create a many to many relationship.  I would be
> grateful if anyone knew a way around this.
>
> Here's my predicamint.
> I have a database for sales orders.
> An inventory table.
> And
> A history table.
>
> Inventory:
> Create t1 (sku char(4), type char(1));
> History:
> Create t2 (id char(6), items text);
>
> [There are more fields, but this is all that matters for this query]
>
> I would like to get the id's where the customer has purchased an item of a
> specific type.
>
> Problem A: most people order more than one item at a time.
>  So the 'items' field is a colon delimitted text field containing the
>skus of the purchased items.
>   
> Problem B: there are many skus of each type.
>as are there many purchases.
>
> What would the proper select be?
>
> create view v1 (select sku from t1 where type ='K');
>will get me all the skus of one type but i don't know where to go
> from there. And it feels as if i've exhausted all options.
>
> i've been working around:
> select id from t2 where items like sku;
> and no matter what i use in the where clause (regex, like, or wildcards).
> i get back an error or a zero.
> and there are no other related fields in the mentioned tables.
>
> is there a way to step through the sku field item by item without leaving
> postgres (i.e. resorting to scripting)?
>
> I have also tried different fieldtypes for the 'items' field.
> But they all give me problems too.
> The array works much the same way as the : delimitted field i have does.
> Except you have less operators that work with it.
> And to break it up into separate items fields. (item1, item2, item3,
> etc.) is a waste, seeing as the average order is 2.? but there are many
> orders with hundreds of items.
>
> Sorry for the long winded explanation.
> But I figured, that the more imformation i gave, the more someone may be
> able to help.
>
> Thanks in advance.
> .jtp

usually many-to-many relationships are handled by a third table, like this:

create table items (item_id,...)
create table customers (customer_id, ...)
create table orders (customer_id, item_id, quantity_orderered)

Hoping it may help

Patrick JACQUOT

Re: [SQL] join if there, blank if not

[Patrick Jacquot]

Henry Lafleur wrote:

> Jacques,
>
> The problem with using the union in this way is that you get NULLs for a
> number weather or not it has an associated record in calls.
>
> To do a pure outer join, it would be something like this:
>
> select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
> from calls c, numbers n
> where c.cnumber=n.nnumber
> union all
> select null as cdate, null as cextn, null as cnumber, nnumber, ndesc
> from numbers
> WHERE nnumber NOT IN (SELECT nnumber FROM calls);
>
> ---
>
> What I have always had trouble with, though, is if you have multiple fields
> for a primary key. For example, if a customer master table also had ship-to
> locations as the key and you wanted to get all customers and any orders for
> that customer, in rough ANSI SQL it would be:
>
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND
> c.ship_to = o.ship_to
>
> then, in the union, it is not clear how to do it:
>
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c, orders o
> WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to
> UNION
> SELECT cust_number, ship_to, NULL AS item
> FROM cust
> WHERE ???
>
> which I never know what to do at ??? -
> WHERE c.cust_number NOT IN (SELECT cust_number FROM orders)
> is one choice, but this doesn't help if the ship to doesn't match. We can
> get wild and try -
> WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE
> ship_to = cust.ship_to)
> but if you go to two and three keys, what happens then? It seems like it
> should work if we continue. But how efficiently does this work?
>
> Has anyone examined this problem?
>
> Thanks,
>
> Henry
>
> -Original Message-
> From: Jacques Williams [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, July 12, 2000 9:41 AM
> To: Gary Stainburn
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] join if there, blank if not
>
> Gary,
>
> What you want here is an outer join. The syntax would look something like
> this:
>
> select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
> from calls c, numbers n
> where c.cnumber=n.nnumber
> union all
> select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc
> from numbers;
>
> (I haven't tried this, but it should work.) For more information on outer
> joins, see Bruce Momjian's book at
> http://www.postgresql.org/docs/aw_pgsql_book/ .
>
> Jacques Williams
>
>
> On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote:
> > Hi all,
> >
> > I just can't get my head round this one so I hope one of you can.
> >
> > I've got two tables, one holding phone calls, and another holding phone
> numbers.
> >
> > I want to do a select where if the number in the calls table exists
> > in the numbers table, the description is included otherwise the
> > description field is blank.
> >
> > Unfortunately, using the select I've tried, if the number is not on
> > the one of the tables, it's ignored.
> >
> > Calls table
> > cdate date
> > ctime time
> > cextn char(3)
> > cnumber x(12)
> >
> > Numbers table
> >
> > nnumber x(12)
> > ndesc x(30)
> >
> > Select I tried.
> >
> > select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc
> > from calls c, numbers n where c.cnumber = n.nnumber;
> >
> > -
> > Gary Stainburn.
> > Work: http://www.ringways.co.uk mailto:[EMAIL PROTECTED]
> > REVCOM: http://www.revcom.org.uk mailto:[EMAIL PROTECTED]
> > -
> > Murphy's Laws: (327) The minute before the engineer arrives, the printer
> starts working.
> > -
> >

hi
for multiple fields in the join condition, i always succeeded wit a
WHERE NOT EXISTS SELECT ...
subselect in the second part of the UNION.
Hoing that may help (although perhaps suboptimal)

Re: [SQL] Please help me to slove this SQL statements

[Patrick JACQUOT (DSI NOISIEL)]

-Message d'origine-
De : Freshman [mailto:[EMAIL PROTECTED]
Envoyé : jeudi 30 octobre 2003 13:38
À : [EMAIL PROTECTED]
Objet : [SQL] Please help me to slove this SQL statements


There are three table in database which is suppliers, projects, and
shipments
suppliers contain suppliers id, name ...etc
projects contain project name ..suppliers ID ( J1---J7) ...etc
shipments table contain suppliers ID , PROJECTS ID

how can i query to find out the suppliers to supply all the projects ID

I would suggest:
SELECT * from suppliers
WHERE NOT EXISTS(
SELECT * FROM projects
WHERE NOT EXISTS(
SELECT * from shipments 
WHERE shipments.suppliers_ID = suppliers_suppliers_id
AND   shipments.project_ID   = project.project_id
)
);
so you select every supplier who didn't miss any project.
Is that you want to do?

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org