Solved

Oracle 9I - Decode issue on a query

Posted on 2014-11-19
5
210 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)
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 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

930 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

11 Experts available now in Live!

Get 1:1 Help Now