Re: [SQL] Conditional query plans.

[Continuing Technical Education]

> update users set enabled='f',disablereason='We do not allow our
> system to be used for SPAM.' where id in (select id from users where
> loginid in (select distinct loginid from logins where
> ip='123.123.12.12'));

Would it run better as:

update users set enabled='f',disablereason='We do not allow our
system to be used for SPAM.' where id in (select distinct loginid from
logins where
ip='123.123.12.12');

Or perhaps even:

update users set enabled='f',disablereason='We do not allow our
system to be used for SPAM.' where id in (select unique id from users,logins
where
users.loginid=logins.loginid where ip='123.123.12.12');

I don't know if that helps the query plan, but it looks prettier :)

Re: [SQL] Problem with coalesce..

[Continuing Technical Education]

Try:

 

SELECT COALESCE(CURVALUE,1) FROM TABLEKEYS WHERE 
TABLENAME = 'BUYER';
 

  - Original Message - 
  From: 
  George Henry C. 
  Daswani 
  To: [EMAIL PROTECTED] 
  Cc: [EMAIL PROTECTED] 
  
  Sent: Tuesday, October 31, 2000 5:52 
  PM
  Subject: [SQL] Problem with 
  coalesce..
  
  Hello,
   
      Was wondering if such a call 
  is unsupported, or a bug?
   
  7.0.2 (postgresql, linux redhat 7.0)
   
  SELECT COALESCE ((SELECT NULL,1) 
   
  returns a 1, correct..
   
  however..
   
  SELECT COALESCE ((SELECT CURVALUE FROM TABLEKEYS 
  WHERE TABLENAME = 'BUYER'),1)
   
  returns a "UNKNOWN expression type 
  501"
   
  SELECT CURVALUE FROM TABLEKEYS WHERE TABLENAME = 
  'BUYER'; returns a '0'
   
  Thanks..
   
  George
   
   

[SQL] Does anyone know of a function equivilant to Oracle's decode function?

[DalTech - Continuing Technical Education]

I have not seen much information about this anywhere on the 
web.
 
If anyone has seen, or written a function similar, could you 
please let me know where?
 
Thanks.

Re: [SQL] Re: unique sequences

[DalTech - Continuing Technical Education]

I imagine it should if you create a unique index on the field.

Cheers,
Christopher Currie


- Original Message -
From: K Parker <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, June 27, 2000 1:52 AM
Subject: [SQL] Re: unique sequences


> > Rather, each time you wish to do an insert,
> > query the existing data to see what the
> > maximum value is existing in the database.
> > Add 1 to that and use this as your new value.
> > This is guaranteed to not have any holes in
> > the sequence.
>
> True, but alas it _doesn't_ guarantee uniqueness, since there's nothing to
prohibit 2 or more back ends getting the same max() at the
> same time...
>
> Thinking off the top of my head, I'm not sure you can apply FOR UPDATE to
an aggregate function, so there's probably no solution there, either.
>
>
>
> Join 18 million Eudora users by signing up for a free Eudora Web-Mail
account at http://www.eudoramail.com

[SQL] Running queries from scripts.

[DalTech - Continuing Technical Education]

I was wondering if anyone could help me out with the syntax of 
running queries from a unix script.
 
psql -c select 'I am a dummy' -d dbname  - never seems to 
work.  I've tried variations on the command line, but to no 
success.
 
Does the -c option work?
 
Cheers,
Christopher Currie
__Dalhousie: DalTech - 
CTEVisit our Web site at http://www.cte.dal.ca(902) 
494-6079(800) 565-1179

Re: [SQL] Running queries from scripts.

[DalTech - Continuing Technical Education]

> It works for me, for example, this way:
> athens% psql -d template1 -c "select datname from pg_database"

Thanks, I feel a little sheepish at not trying it with quotes around the
query, but the manuals don't mention that you have to enclose the query in
quotes when you use the -c option.

-Christopher Currie

Re: [SQL] MAX() of 0 records.

[DalTech - Continuing Technical Education]

> UPDATE entry_stats 
> SET entry_maxprice=MAX(item_price) 
> FROM item 
> WHERE item.item_entry_id=NEW.item_entry_id
>   AND item.item_live = 't';

Try

COALESCE(MAX(item_price),0)

Christopher J.D. Currie
Computer Technician
Dalhousie: DalTech - CTE
_
Lord, deliver me from the man who never makes a mistake,
and also from the man who makes the same mistake twice.
-William James Mayo

Re: [SQL] problem with date

[DalTech - Continuing Technical Education]

Try NULL rather than ''.  '' is not a null in postgres.


- Original Message - 
From: Shalini shikha <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, July 10, 2000 3:15 PM
Subject: [SQL] problem with date


> How can I insert/update a null value in a date field. If a try a sql
> statement like the following:
> 
> update customer set birth_date = '' where cust_id like 'xyz', I get a
> error message saying: Bad date representation.
> 
> Thanks in advance,
> Shalini