Re: [SQL] Insert into:Bad date external representation
Hi,
got an error as below :
dwnc=> insert into biosadm.custdo_dim
dwnc-> (do_key,do_no,do_date,attnto,custlo)
dwnc-> select nextval('cdo_seq'),c.dono::varchar,c.dodate::date,
dwnc-> c.attnto, c.custlo from custdo_temp c;
ERROR: Bad date external representation ''
Table structure (custdo_dim) :
dwnc(> \d custdo_dim
Table "biosadm.custdo_dim"
Column | Type | Modifiers
-+-+---
do_key | integer | not null default nextval('cdo_seq'::text)
do_no | text |
do_date | date |
custlo | text |
attnto | text |
Indexes: custdo_dim_pkey primary key btree (do_key)
Table structure(custdo_temp):
Table "biosadm.custdo_temp"
Column | Type | Modifiers
--+--+---
dono | text |
dodate | text |
custname | text |
custlo | text |
attnto | text |
pls help me thanks
Re: [SQL] Insert into:Bad date external representation
On Jan 14, 2004, at 4:39 PM, [EMAIL PROTECTED] wrote:
got an error as below :
dwnc=> insert into biosadm.custdo_dim
dwnc-> (do_key,do_no,do_date,attnto,custlo)
dwnc-> select nextval('cdo_seq'),c.dono::varchar,c.dodate::date,
dwnc-> c.attnto, c.custlo from custdo_temp c;
ERROR: Bad date external representation ''
Here's your clue right here: It means the dodate text field in
custdo_temp contains data that does not represent a valid date. Check
to make sure custdo_temp.dodate is all properly formatted.
HTH
Michael Glaesemann
grzm myrealbox com
---(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] Transpose rows to columns
El Lun 12 Ene 2004 22:12, David Witham escribió: >DW: Hi, >DW: >DW: I have a query that returns data like this: >DW: >DW: cust_idcust_name month costrevenue margin >DW: 991234 ABC 2003-07-01 10 15 5 >DW: 991234 ABC 2003-08-01 11 17 6 >DW: 991234 ABC 2003-09-01 12 19 7 >DW: 991235 XYZ 2003-07-01 13 21 8 >DW: 991235 XYZ 2003-08-01 12 19 7 >DW: 991235 XYZ 2003-09-01 11 17 6 >DW: >DW: I want to turn it around so it displays like this: >DW: >DW: 991234,ABC,2003-07-01,10,15,5,2003-08-01,11,17,6,2003-09-01,12,19,7 >DW: 991235,XYZ,2003-07-01,13,21,8,2003-08-01,12,19,7,2003-09-01,11,17,6 Hi, the following query select cust_id || ', ' || cust_name || ', ' || list(month::text || ', ' || cost || ', ' || revenue || ', ' || margin) as result from tmp122 group by cust_id, cust_name; *DISPLAYS* data like this: result - - 991234, ABC, 2003-07-01, 10, 15, 5, 2003-08-01, 11, 17, 6, 2003-09-01, 12, 19, 7 991235, XYZ, 2003-07-01, 13, 21, 8, 2003-08-01, 12, 19, 7, 2003-09-01, 11, 17, 6 (2 rows) the type 'list' and the function 'comma_cat' (I cannot remember where I took it, but are very useful)... CREATE FUNCTION comma_cat (text, text) RETURNS text AS 'select case WHEN $2 is null or $2 = THEN $1 WHEN $1 is null or $1 = THEN $2 ELSE $1 || '', '' || $2 END' LANGUAGE sql; CREATE AGGREGATE list ( BASETYPE = text, SFUNC = comma_cat, STYPE = text, INITCOND = '' ); -- Original data for test -- drop table tmp122; create temp table tmp122 ( cust_id integer, cust_name varchar, month date, costinteger, revenue integer, margin integer ); copy tmp122 from stdin; 991234 ABC 2003-07-01 10 15 5 991234 ABC 2003-08-01 11 17 6 991234 ABC 2003-09-01 12 19 7 991235 XYZ 2003-07-01 13 21 8 991235 XYZ 2003-08-01 12 19 7 991235 XYZ 2003-09-01 11 17 6 \. -- Chau, Luis ---(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] REPOST[GENERAL] Quoting for a Select Into - Please Help
Hi, Could someone help me with quoting this right? select into aliasvalue ''|| trim(arrayval[i]) ||'' from ''|| trim(realname) ||'' where ''|| trim(searchfield) ||'' like %''|| trim(searchvalue) ||''%; The parser does not seem to want to put the value of the variables into the statement. Alex
[SQL] Atomic query and update of sequence generators
If I want to allocate a block of adjacent values from a sequence generator,
is the following statement atomic with respect to the time between
when the call to nextval() and setval()?
SELECT setval('foo', nextval()+20) ...
The goal is to get a sequence of 20 values that are all +1 from each other.
If the above isn't safe in concurrent environments, is there something
else I can
do to achieve the effect safely?
I saw a similar unanswered question on one of the PostgreSQL newsgroups,
and have a need to do the same thing from my JDBC app.
Tips appreciated.
---(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
Re: [SQL] REPOST[GENERAL] Quoting for a Select Into - Please Help
Alex, Postgres's "select into" does not work like oracle. The "select into" for postgresql creates another table. Try this; select aliasvalue || trim(arrayval[i]) into newtablename from currenttablename where trim(searchfield) like '%' || trim(searchvalue) || '%'; Stuart --- A E <[EMAIL PROTECTED]> wrote: > Hi, > > Could someone help me with quoting this right? > > select into aliasvalue ''|| trim(arrayval[i]) ||'' > from ''|| trim(realname) ||'' where ''|| > trim(searchfield) ||'' like %''|| > trim(searchvalue) ||''%; > > The parser does not seem to want to put the value of > the variables into the statement. > > Alex > > __ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus ---(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] Atomic query and update of sequence generators
Jeffrey Tenny <[EMAIL PROTECTED]> writes:
> If I want to allocate a block of adjacent values from a sequence generator,
> is the following statement atomic with respect to the time between
> when the call to nextval() and setval()?
> SELECT setval('foo', nextval()+20) ...
Nope.
> The goal is to get a sequence of 20 values that are all +1 from each other.
Do you always want to pull exactly 20 values? If so you could set the
sequence's "cache" parameter to 20 (see the CREATE SEQUENCE man page for
details). I don't think there's any way at present to get varying sizes
of consecutively-allocated blocks. If you need that, it would likely
not be real hard to implement a "next_n_vals(seq, n)" variant of
nextval() to grab N consecutive values and return the first. But it's
not there at the moment.
regards, tom lane
---(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
