Oracle 9I - Decode issue on a query

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

Oracle DatabaseSQL

Last Comment

Wilder1626

8/22/2022 - Mon

slightwv (䄆 Netminder)

11/19/2014

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

slightwv (䄆 Netminder)

11/19/2014

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

Wilder1626

11/19/2014

ASKER

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;