Fwd: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Aaron Bono
Can you provide an example?Thanks,AaronOn 6/11/06, Phillip Smith <[EMAIL PROTECTED] > wrote: Hi All,   Hope someone can help me – our main company system runs on Raining Data PICK/D3 (if anyone familiar with it) which stores records in it's "tables" as variable length items. Every

[SQL] Refactor Query...

2006-06-12 Thread operationsengineer1
hi all, i'm trying to refactor this query: http://www.rafb.net/paste/results/RnkFWZ46.html i've added notes to make its function a bit more clear. i have trie dto refactor it to get rid of the "distinct on" function, but i've had no luck. due to all the WHERE clauses, i can't figure out how to

Re: [SQL] Refactor Query... SOLVED

2006-06-12 Thread operationsengineer1
--- [EMAIL PROTECTED] wrote: > hi all, > > i'm trying to refactor this query: > > http://www.rafb.net/paste/results/RnkFWZ46.html > > i've added notes to make its function a bit more > clear. i have trie dto refactor it to get rid of > the > "distinct on" function, but i've had no luck. > > d

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Phillip Smith
No problem!   SYS  'ZKCOST' size = 21 01 2750 This is a simple record in the SYS file. Primary Key is “ZKCOST” and the Data would be 1 element with a value of 2750.   SYS  'ZPRECMPL' size = 2069  O 01 928898 02 928899 03 928900 04 92

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Tom Lane
"Phillip Smith" <[EMAIL PROTECTED]> writes: > The whole sys file is variable length records like this - they range = > from 1 > to over 17,000 fields per record. 17000? I think you really need to rethink your schema. While you could theoretically drop 17000 elements into a PG array column, you w

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Aaron Bono
I agree with Tom.  Personally I cannot think of a time I would use an array column over a child table.  Maybe someone can enlighten me on when an array column would be a good choice.What language are you using to do the export if I may ask? -AaronOn 6/12/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Phi

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Phillip Smith
The export from the D3 system is written in PICK BASIC – similar to ‘normal’ BASIC.   This currently exports Attribute (field) 1 to 9 of each SYS record with a Primary Key starting with “Z” (‘]’ is a wildcard in the SSELECT statement) 153 EXECUTE 'SSELECT SYS WITH A0 "Z]"' CAPTURING JUNK

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Rod Taylor
On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote: > I agree with Tom. Personally I cannot think of a time I would use an > array column over a child table. Maybe someone can enlighten me on > when an array column would be a good choice. Arrays are a good choice when the data comes naturally s

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Richard Broersma Jr
> On Mon, 2006-06-12 at 21:58 -0500, Aaron Bono wrote: > > I agree with Tom. Personally I cannot think of a time I would use an > > array column over a child table. Maybe someone can enlighten me on > > when an array column would be a good choice. > > Arrays are a good choice when the data comes

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Aaron Bono
I think two tables should suffice: ZKCOST and ZPRECMPL. So you would have ZKCOST zkcost_id, zkcost_value and ZPRECMPL zkcost_id, zprecmpl_id, zprecmpl_value where zkcost_id is the primary key for ZKCOST and zkcost_id, zprecmpl_id together are the primary key for ZPRECMPL and zk

Re: [SQL] COPY to table with array columns (Longish)

2006-06-12 Thread Phillip Smith
Not quite... ZKCOST and ZPRECMPL are two completely different things. They have no relation except they're both stored in the SYS table in D3. If we put it in a tree: SYS | +- ZKCOST | \- | +- ZPRECMPL | +- | +-