Using multiple nested queries in Oracle DBMS

Question

I want to select the contact info (email and phonenumber) of all employees working between 2 time ranges in shops of type market and have currently more than 2 employees working together

What I want to do: I have an entity EMPLOYEE that is the owner entity of another entity SCHEDULE, and I have a "SHOP" entity with a "shopn" attribute as foreign key of EMPLOYEE

My attempt:

SELECT Email, Phonenumber  
FROM EMPLOYEE  
WHERE COUNT(  
        ID = (SELECT ID_Num  
            FROM SCHEDULE     
            WHERE Start_Time <= 12:07 AND End_Time >= 12:07)  
    AND Shopn = (SELECT Shopname  
            FROM SHOP  
            WHERE Shoptype = ‘market’)
    ) > 2

I am getting ORA-00934 error Any help would be appreciated.

There are too many mistakes in this code. Let's start with the easiest ones. What is 12:07 supposed to be in your code? Is it a string? Then put it in single quotes. What is the data type of Start_Time and End_Time? Are they strings? (If so... bad idea!) — user5683823
– user5683823, Commented Nov 22, 2020 at 17:00
they are of type varchar, I thought it wouldn't compare so I put equal only and it gave me that error — Sergio
– Sergio, Commented Nov 22, 2020 at 17:01
Put the 12:07 in single quotes, so it is a string also. Then: are the Start_Time and End_Time in the hh24:mi format (meaning, '04:45' for 4:45 AM and '15:00' for 3 PM? If they are not (for example: if the single-digit hours don't have the leading 0) then your comparisons won't work as intended. — user5683823
– user5683823, Commented Nov 22, 2020 at 17:03
@mathguy any idea about further errors? as these changes don't fix it — Sergio
– Sergio, Commented Nov 22, 2020 at 17:20

user3315556 · Accepted Answer · 2020-11-22 17:35:05Z

1

Your query:

SELECT Email, Phonenumber
FROM EMPLOYEE
WHERE COUNT(
ID = (SELECT ID_Num
FROM SCHEDULE
WHERE Start_Time <= 12:07 AND End_Time >= 12:07)
AND Shopn = (SELECT Shopname
FROM SHOP
WHERE Shoptype = ‘market’) ) > 2

Issues :

It seems you are trying to select id using "=" operator which will fail in case of multiple entries returned by inner query.
You cannot add aggregate operators in the where clause.

Probable Answer:

select Email, Phonenumber
FROM EMPLOYEE
WHERE ID in (SELECT ID_Num
FROM SCHEDULE
WHERE Start_Time <= '12:07' AND End_Time >= '12:07')
AND Shopn in (SELECT Shopname
FROM SHOP
WHERE Shoptype = 'market')
and (  
select count(distinct id)
FROM EMPLOYEE
WHERE ID in (SELECT ID_Num
FROM SCHEDULE
WHERE Start_Time <= '12:07' AND End_Time >= '12:07')
AND Shopn in (SELECT Shopname
FROM SHOP
WHERE Shoptype = 'market')) > 2;

edited Nov 22, 2020 at 17:35

answered Nov 22, 2020 at 17:07

user3315556

1751 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sergio Over a year ago

when I tried to run it as it is I got a weird windows prompt, tried to put quotes between the times since they are varchar and I got command not properly ended, try putting a ";" and error remained

user3315556 Over a year ago

‘market’ should be not used try using a conventional qoutes i have edited the script, if issue further persists removing the last condition and run. Let me know

Sergio Over a year ago

I already did that myself as I have noticed it, I get command not properly ended, I tried removing the "WHERE Shoptype = 'market' and got the same regardless if I leave statement as is or add a ";" at the end

user3315556 Over a year ago

Yes i think you are missing a semicolumn at the end

Sergio Over a year ago

I added it myself and still got the same, that is what I was saying

|

Collectives™ on Stack Overflow

Using multiple nested queries in Oracle DBMS

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related