Pass thru Query problem - converting from a case /replace in SQL

[Holly]

Hi guys. I have a pretty dumb question to ask. I have created a new
Access database to get data from a pass thru query, because we have
some Oracle tables I need to get data from.
I used a sql query created using TOAD. I had a case statement to
convert some commas that were included in a text field (freeform, in
ClearCase (yuck)) and put a delimiter of '?' where I found those. This
was because we had some values of XX12345, some of 12345 and some of
12345, 56789 and those needed to be split into another column so that
each column had only one 5 digit numeric value.
Well, we were going to export the data from the query into Excel, then
import those into Access. I thought it would save a lot of time to do
a Access db with a direct query to the Oracle tables used in the first
place, to cut out the text file middleman. Well, now I don't know how
to separate fields with commas into separate columns in an access
query. Is it best to do this in the pass thru query and how can you do
this?


SELECT
case
when t1.field_one is null then null
else
replace(replace(replace(t1.field_one,'XX'),'&','?'),',','?')||'?' end||

I had figured out up to this point, (I couldn't get the case statement
to work at all (this is a direct odbc connection to Oracle), so I just
went with a replace) but I don't know how to split the commas into new
columns...

REPLACE (t1.field_one,'XX','') AS FieldOne,

 

[Holly]

Hi guys. I have a pretty dumb question to ask. I have created a new
Access database to get data from a pass thru query, because we have
some Oracle tables I need to get data from.
I used a sql query created using TOAD. I had a case statement to
convert some commas that were included in a text field (freeform, in
ClearCase (yuck)) and put a delimiter of '?' where I found those. This
was because we had some values of XX12345, some of 12345 and some of
12345, 56789 and those needed to be split into another column so that
each column had only one 5 digit numeric value.
Well, we were going to export the data from the query into Excel, then
import those into Access. I thought it would save a lot of time to do
a Access db with a direct query to the Oracle tables used in the first
place, to cut out the text file middleman. Well, now I don't know how
to separate fields with commas into separate columns in an access
query. Is it best to do this in the pass thru query and how can you do
this?


SELECT
case
when t1.field_one is null then null
else
replace(replace(replace(t1.field_one,'XX'),'&','?'),',','?')||'?' end||

I had figured out up to this point, (I couldn't get the case statement
to work at all (this is a direct odbc connection to Oracle), so I just
went with a replace) but I don't know how to split the commas into new
columns...

REPLACE (t1.field_one,'XX','') AS FieldOne,

Never mind guys -
just in case anybody wondered what to do in a similar data cleanup of a
field with commas,
I did another query on that pass thru query, and in the expression
builder inserted some vba functions (microsoft had some actually good
examples you can download) where I could do a string parse and then
filter out fields 1, 2, and 3 of the comma delimited string into new
columns.
The article I found was here :
http://support.microsoft.com/kb/95608/

Thanks!