By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,609 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,609 IT Pros & Developers. It's quick & easy.

alternate approach to this sql

P: n/a
My input data is csv file like this with '|' delimiter:
1|first
2|second
3|third
4|fourth
5|fifth

I need to load this into temp table for validation purposes...so I did
this:

with T (ID) as ( values '1|first','2|second','3|third','4|fourth','5|
fifth')
select substr(ID, 1,locate('|', ID)-1) as FIRST_COLUMN,
substr(ID, locate('|', ID)+1, length(ID)-locate('|', ID)) as
SECOND_COLUMN
from T

Is there better approach?
What is syntax for having more than one column in "with" clause, eg:
with T (ID, DESC) as ( values '1','2','3','4','5')
select ID, DESC from T -- where should I put DESC
('first','second','third','fourth','fifth') in "values" clause?

Oct 2 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Oct 2, 1:19 pm, "annecarterfr...@gmail.com"
<annecarterfr...@gmail.comwrote:
My input data is csv file like this with '|' delimiter:
1|first
2|second
3|third
4|fourth
5|fifth

I need to load this into temp table for validation purposes...so I did
this:

with T (ID) as ( values '1|first','2|second','3|third','4|fourth','5|
fifth')
select substr(ID, 1,locate('|', ID)-1) as FIRST_COLUMN,
substr(ID, locate('|', ID)+1, length(ID)-locate('|', ID)) as
SECOND_COLUMN
from T

Is there better approach?
What is syntax for having more than one column in "with" clause, eg:
with T (ID, DESC) as ( values '1','2','3','4','5')
select ID, DESC from T -- where should I put DESC
('first','second','third','fourth','fifth') in "values" clause?
I got it..I can use "values ('1','first')" in my query..

Oct 2 '07 #2

P: n/a
On Oct 2, 7:19 pm, "annecarterfr...@gmail.com"
<annecarterfr...@gmail.comwrote:
My input data is csv file like this with '|' delimiter:
1|first
2|second
3|third
4|fourth
5|fifth

I need to load this into temp table for validation purposes...so I did
this:

with T (ID) as ( values '1|first','2|second','3|third','4|fourth','5|
fifth')
select substr(ID, 1,locate('|', ID)-1) as FIRST_COLUMN,
substr(ID, locate('|', ID)+1, length(ID)-locate('|', ID)) as
SECOND_COLUMN
from T

Is there better approach?
import from f.csv of del modified by coldel0x7C insert into T

/Lennart

Oct 3 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.