Solved

Oracle 9I - Decode issue on a query

Posted on 2014-11-19
5
215 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 500 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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

739 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