Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle 9I - Decode issue on a query

Posted on 2014-11-19
5
Medium Priority
?
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40453515
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40453524
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
ID: 40453544
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40453558
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
ID: 40453562
I really like the CASE one.

Thanks a lot, this is perfect.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

688 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