asked on

how to parse comma separated fields

Hi sdstuber

from the same query that you modified I have a requirment to parse the or_id value from ref_request_table if the or_id values contains values like 1-A,1-B,1-C..for begin and end month of January 2010

then the expected data should be
req_id or_id begin month end month
1 1-A 01-JAN-2010 31-JAN-2010
1 1-B 01-JAN-2010 31-JAN-2010
1 1-C 01-JAN-2010 31-JAN-2010

SELECT DISTINCT
or_id,
TO_CHAR(TRUNC(each_day, 'MM'), 'MM/DD/YYYY') start_date,
TO_CHAR(LAST_DAY(each_day), 'MM/DD/YYYY') iend_date,
TO_CHAR(TRUNC(TRUNC(each_day, 'MM') - 1, 'MM'), 'MM/DD/YYYY') u_start_dt,
TO_CHAR(TRUNC(each_day, 'MM') - 1, 'MM/DD/YYYY') u_end_dt
FROM (SELECT or_id, req_id, begin_date - 1 + LEVEL each_day
FROM ref_request_date
WHERE req_id = 1001
CONNECT BY req_id = 1001 and LEVEL <= end_date - begin_date + 1)

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

I simplified the inner query from what you started with since you have a different date format in your expected output than what the starting query had, and you're not using the daily values, only the month begin/end

mahjag

ASKER

cool - I will try that out -

quick question if my begin date is not first but 02-JAN-2010 and end date is 28-JAN-2010 will that translate into 01-JAN-2010 and 31-JAN-2010?

Sean Stuber

the start/end dates are based on"each_day", which will always generate the first and last day of the month of "each_day"

so, if each_day is 2-jan-2010 it will return 1-jan-2010 and 31-jan-2010
and if each_day is 28-jan-2010 it will return 1-jan-2010 and 31-jan-2010

mahjag

ASKER

I started to test on a req_id value 1001 having 3 or_ids 1-A,1-B and 1-C the start month is 1/1/2010 and end month is 9/1/2010 - my expected result is
27 rows each or_id value should get 9 rows for 9 months but I only got a total of 9 rows after I created the table and function and re-running your query - let me know what I did wrong?

Sean Stuber

what does this return?

(SELECT DISTINCT
or_id,
TRUNC(each_day, 'MM') start_date,
LAST_DAY(each_day) end_date
FROM (SELECT or_id, req_id, begin_date - 1 + LEVEL each_day
FROM ref_request_date
WHERE req_id = 1001
CONNECT BY req_id = 1001 AND LEVEL <= end_date - begin_date + 1))

Sean Stuber

or maybe more importantly... what does this return?

SELECT or_id, req_id, begin_date - 1 + LEVEL each_day
FROM ref_request_date
WHERE req_id = 1001
CONNECT BY req_id = 1001 AND LEVEL <= end_date - begin_date + 1

mahjag

ASKER

that request_id 1001 had 4 or_ids 1-A,1-B,1-C,1-D - after running above query I have 6076 rows but the or_id did not change it showed only 1-A for all the rows - isn;t that odd?

Sean Stuber

what is 9/1/2010?

is that mm/dd/yyyy? or dd/mm/yyyy?

mahjag

ASKER

I also expect the req_id value to show as 1001 I am seeing 1 now - why is that? sorry too may questions?

Sean Stuber

using

req_id=1001
or_id = '1-A,1-B,1-C,1-D'
begin_date = TO_DATE('01-JAN-2010', 'dd-mon-yyyy')
and end_date = TO_DATE('01-SEP-2010', 'dd-mon-yyyy')

This query produced 36 rows, is that correct?

SELECT req_id,
COLUMN_VALUE or_id,
TO_CHAR(start_date, 'dd-MON-yyyy') begin_month,
TO_CHAR(end_date, 'dd-MON-yyyy') end_month
FROM (SELECT DISTINCT
or_id,
req_id,
TRUNC(each_day, 'MM') start_date,
LAST_DAY(each_day) end_date
FROM (SELECT or_id, req_id, begin_date - 1 + LEVEL each_day
FROM ref_request_date
WHERE req_id = 1001
CONNECT BY req_id = 1001 AND LEVEL <= end_date - begin_date + 1)) x,
TABLE(str2tbl(x.or_id))
ORDER BY start_date, or_id

mahjag

ASKER

mm/dd/yyyy

mahjag

ASKER

if you get 36 rows and each row as same req_id as 1001 and has 9 rows of months for each or_id value then it is correct..

Sean Stuber

I'm heading out soon. If this is important bump up the points and somebody else might jump in.
low points = not important and attract less attention.

If nobody else jumps in, I'll take a look tomorrow

mahjag

ASKER

thanks sdstuber appreciate your help!!