0

FacilityTable

FACILITYNO    FACILITYNAME
'FACNO01'        Toilet
'FACNO02'       Staff Office
'FACNO03'     Principle Office
'FACNO04'       Science Lab
'FACNO05'       Math Lab
'FACNO06'       Computer Lab
'FACNO07'        Biology Lab
'FACNO08'       Chemical Lab

BookingTable

FACILITYNO  bookingID       userID     Timebooked  
'FACNO01'       1             0003    10-May-2016       
'FACNO04'       2             0001    10-May-2018    
'FACNO05'       3             0001    10-Apr-2017
'FACNO01'       4             0001    10-Apr-2017
'FACNO02'       5             0003    10-Jan-2011
'FACNO04'       6             0006    10-Apr-2018
'FACNO06'       7             0003    10-Apr-2016
'FACNO07'       8             0006    10-Apr-2015
'FACNO08'       9             0001    10-Apr-2017

Expected Result (after insert &n -> Year 2016)

FACILITYNAME  count_times  MONTH
Toilet          1            5
Computer Lab    1            4

On this code I , when runtime is used (&n) and I entered 2016' it will show the expected table that I have provided above.

Below is my code ( but ended up error/duplicate , appearing the data even is not the year I have inserted (2016) )

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE TO_CHAR(b.Timebooked , 'YYYY') = &n
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ))
whalesboy
  • 21
  • 8
  • "When **runtime** is used"?? Do you mean "substitution variable"? –  Mar 29 '18 at 16:46
  • @mathguy the & is used where to allow user to enter the data , in my case which is enter year . – whalesboy Mar 29 '18 at 16:51
  • Please copy and paste the error message exactly as it appeared when you ran the query. Your explanation "ended up error/duplicate" doesn't make any sense; what "duplicate"? Did it end up in an error, or did it work but produced the wrong answer? –  Mar 29 '18 at 16:51
  • I know how the & is used. That is not called "runtime" (which has a completely different meaning); what you have there is called a SUBSTITUTION VARIABLE. –  Mar 29 '18 at 16:52
  • @mathguy , sorry for the lack of understanding regarding on the keyword, the result that appeared is that will show everything (facilityname and the count )that existing in the BookingTable , for now I unable to access to my computer which i will be online in the next 5-6 hour , and i will update the result – whalesboy Mar 29 '18 at 16:58
  • what exactly is the error you are getting – Daniel Marcus Mar 29 '18 at 17:05
  • @DanielMarcus example, if i enter 1980 , the result will still show the data that existing on my database – whalesboy Mar 29 '18 at 17:08
  • And i am not sure weather is that my query have some error that caused this problem , or i used the wrong method – whalesboy Mar 29 '18 at 17:09
  • Your query is good. You have some issue with your substitution. – ArtBajji Mar 29 '18 at 17:24
  • As in substitution is on fk and pk ? – whalesboy Mar 29 '18 at 17:26
  • Be cautious! There are always people on this site that want to get out the full error message text from you. – miracle173 Mar 29 '18 at 18:20
  • I did not take any advantage on this site , just want to know that is my query have some error , and i need to know. I tried all my best to solved it before I post my issue on this site. Sorry if you felt that I taking this site as a advantage to solve my code – whalesboy Mar 29 '18 at 23:44

1 Answers1

0

No problem seems with proper formatting (using quotes & to_date whenever needed) :

create table FacilityTable (FACILITYNO varchar2(30), FACILITYNAME varchar2(30));
insert into FacilityTable values('FACNO01','Toilet');
insert into FacilityTable values('FACNO02','Staff Office');
insert into FacilityTable values('FACNO03','Principle Office');
insert into FacilityTable values('FACNO04', 'Science Lab');
insert into FacilityTable values('FACNO05','Math Lab');
insert into FacilityTable values('FACNO06','Computer Lab');
insert into FacilityTable values('FACNO07','Biology Lab');
insert into FacilityTable values('FACNO08','Chemical Lab');


create table BookingTable (FACILITYNO varchar2(30),bookingID int, userID varchar2(30), Timebooked date);
insert into BookingTable values('FACNO01',1,'0003',to_date('10-May-2016','dd-Mon-yyyy'));       
insert into BookingTable values('FACNO04',2,'0001',to_date('10-May-2018','dd-Mon-yyyy'));    
insert into BookingTable values('FACNO05',3,'0001',to_date('10-Apr-2017','dd-Mon-yyyy'));
insert into BookingTable values('FACNO01',4,'0001',to_date('10-Apr-2017','dd-Mon-yyyy'));
insert into BookingTable values('FACNO02',5,'0003',to_date('10-Jan-2011','dd-Mon-yyyy'));
insert into BookingTable values('FACNO04',6,'0006',to_date('10-Apr-2018','dd-Mon-yyyy'));
insert into BookingTable values('FACNO06',7,'0003',to_date('10-Apr-2016','dd-Mon-yyyy'));
insert into BookingTable values('FACNO07',8,'0006',to_date('10-Apr-2015','dd-Mon-yyyy'));
insert into BookingTable values('FACNO08',9,'0001',to_date('10-Apr-2017','dd-Mon-yyyy'));

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE TO_CHAR(b.Timebooked , 'YYYY') = &n
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ));

FACILITYNAME    COUNT_TIMES MONTHS
Computer Lab           1    4
Toilet                 1    5

Demo(2016 directly substituted for &n)

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55