Solved

Oracle 9I - Decode issue on a query

Posted on 2014-11-19
5
207 Views
Last Modified: 2014-11-19
Hi all,

I have this SQL below that pull all  data from a table:
select * from SRV_CLASS_detail order by SRV_CLASS_ID;

Open in new window


You will have a sample of the table in the attachment.

as you will see, in the table, i have 2 columns for the OPEN Time and CLOSED time and 7 columns for the days.

I would like to extract from the table in a specific format.

In the excel attachment, when you will look into sheet: Result from new SQL, you will see that i'm putting the open and closed time inder the day's opened.

N = Not opened
Y = Opened

It should only put the time Under opened days.

How can i do this.

Thanks for your help
business-hours.xls
0
Comment
Question by:Wilder1626
  • 3
  • 2
5 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Try this:
select srv_class_id,
	decode(SUN,'Y',open_time,null) Sunday_open,
	decode(SUN,'Y',close_time,null) Sunday_closed,
	decode(MON,'Y',open_time,null) Monday_open,
	decode(MON,'Y',close_time,null) Monday_closed,
	decode(TUE,'Y',open_time,null) Tuesday_open,
	decode(TUE,'Y',close_time,null) Tuesday_closed,
	decode(WED,'Y',open_time,null) Wednesday_open,
	decode(WED,'Y',close_time,null) Wednesday_closed,
	decode(THU,'Y',open_time,null) Thursday_open,
	decode(THU,'Y',close_time,null) Thursday_closed,
	decode(FRI,'Y',open_time,null) Friday_open,
	decode(FRI,'Y',close_time,null) Friday_closed,
	decode(SAT,'Y',open_time,null) Saturday_open,
	decode(SAT,'Y',close_time,null) Saturday_closed
from from SRV_CLASS_detail order by SRV_CLASS_ID;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I think 9i also supports a CASE statement:
select srv_class_id,
	case when SUN='Y' then open_time end Sunday_open,
	case when SUN='Y' then close_time end Sunday_closed,
	case when MON='Y' then open_time end Monday_open,
	case when MON='Y' then close_time end Monday_closed,
	case when TUE='Y' then open_time end Tuesday_open,
	case when TUE='Y' then close_time end Tuesday_closed,
	case when WED='Y' then open_time end Wednesday_open,
	case when WED='Y' then close_time end Wednesday_closed,
	case when THU='Y' then open_time end Thursday_open,
	case when THU='Y' then close_time end Thursday_closed,
	case when FRI='Y' then open_time end Friday_open,
	case when FRI='Y' then close_time end Friday_closed,
	case when SAT='Y' then open_time end Saturday_open,
	case when SAT='Y' then close_time end Saturday_closed
rom from SRV_CLASS_detail order by SRV_CLASS_ID;

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
HI

this is almost perfect.

In the table, i'm having 2 records of AKITA_FOODS

When i use the above query, it puts the info on 2 different rows.
select distinct  srv_class_id,
	case when SUN='Y' then open_time end Sunday_open,
	case when SUN='Y' then close_time end Sunday_closed,
	case when MON='Y' then open_time end Monday_open,
	case when MON='Y' then close_time end Monday_closed,
	case when TUE='Y' then open_time end Tuesday_open,
	case when TUE='Y' then close_time end Tuesday_closed,
	case when WED='Y' then open_time end Wednesday_open,
	case when WED='Y' then close_time end Wednesday_closed,
	case when THU='Y' then open_time end Thursday_open,
	case when THU='Y' then close_time end Thursday_closed,
	case when FRI='Y' then open_time end Friday_open,
	case when FRI='Y' then close_time end Friday_closed,
	case when SAT='Y' then open_time end Saturday_open,
	case when SAT='Y' then CLOSE_TIME end SATURDAY_CLOSED
from SRV_CLASS_detail order by SRV_CLASS_ID;

Open in new window


How can i have in into 1 row with all the open and closed time?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
Missed that.

Aggregate them (i used the CASE syntax since it was my last post but MAX on the decode should work as well):
select srv_class_id,
	max(case when SUN='Y' then open_time end) Sunday_open,
	max(case when SUN='Y' then close_time end) Sunday_closed,
	max(case when MON='Y' then open_time end) Monday_open,
	max(case when MON='Y' then close_time end) Monday_closed,
	max(case when TUE='Y' then open_time end) Tuesday_open,
	max(case when TUE='Y' then close_time end) Tuesday_closed,
	max(case when WED='Y' then open_time end) Wednesday_open,
	max(case when WED='Y' then close_time end) Wednesday_closed,
	max(case when THU='Y' then open_time end) Thursday_open,
	max(case when THU='Y' then close_time end) Thursday_closed,
	max(case when FRI='Y' then open_time end) Friday_open,
	max(case when FRI='Y' then close_time end) Friday_closed,
	max(case when SAT='Y' then open_time end) Saturday_open,
	max(case when SAT='Y' then close_time end) Saturday_closed
from tab1
group by srv_class_id
/

Open in new window

0
 
LVL 11

Author Closing Comment

by:Wilder1626
Comment Utility
I really like the CASE one.

Thanks a lot, this is perfect.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now