AW: SQL Column ranges into rows

... or

SELECT code, begindate
FROM qtemp/t1
GROUP BY code, begindate
Union
SELECT b.code, enddate
FROM qtemp/t1
GROUP BY code, enddate
ORDER BY 1, 2

View:
Create View MySchema/MyView
(Code, MyDate)
As (Select Code, begindate
From MyTable
Group By Code, BeginDate
Union
Select Code, EndDate
From MyTable
Group By Code, EndDate)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Elvis Budimlic
Gesendet: Friday, 30. January 2009 01:04
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL Column ranges into rows

This might work as a query, but not within a view:

WITH
cte1 AS
(SELECT code, begindate
FROM qtemp/t1
GROUP BY code, begindate),
cte2 AS
(SELECT b.code, a.enddate
FROM qtemp/t1 a JOIN cte1 b USING(code,begindate)
WHERE b.begindate <> a.enddate)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
ORDER BY 1,2

So try this instead:

CREATE VIEW myView (code,theDate) AS (
SELECT code, begindate
FROM qtemp/t1
GROUP BY code, begindate
UNION ALL
SELECT a.code, a.enddate
FROM qtemp/t1 a JOIN
(SELECT code, begindate
FROM qtemp/t1
GROUP BY code, begindate) b USING(code,begindate)
WHERE a.enddate <> b.begindate)

Then to get it in the order you want, you'll need to add the ORDER BY to the
SELECT that goes against this view:

SELECT * FROM myView ORDER BY 1,2

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL Column ranges into rows

Wresting with some database conversion issues. Actually, I don't want
to the database to change, I want my application that accesses these
different databases to change as little as possible so building views to
present the data in a similar fashion is what I am after.

I have a table that could either be in this format:

code, date
with data that looks like this:

'test', 20080115
'test', 20080116
'test', 20080117
'test', 20080118

OR I could have a table that looked like this:

code, begin date, end date
'test', 20080115, 20080118

What I would like do is have a view that structures the second table
like the first. If the two dates are the same, you get one row with
code and date. If you have a range of dates that span 6 days (for
example), then there would be 6 rows generated by the view.

Is this possible?

Thanks

Pete