SQL Many-to-Many Query in Apex Oracle

Question

I created a many-to-many relationship between the tables PRODUCT_TYPE and LABEL by creating and intermediate table PRODUCT_TYPE-LABELS. I wanted to retrieve all the products that have the labels 'Gluten free' and 'Lactose free' and found help on a similar subject (How to filter SQL results in a has-many-through relation) but never got it to work properly.

The tables are as follows:

PRODUCT_TYPE{

  PRODUCT_TYPE ->Primary Key
  CONTAINER
    VOLUME_L
     PRICE_EUROS
    ...
}

LABEL{

  LABEL_NAME ->Primary Key
  REQUIREMENTS
 }

PRODUCT_TYPE-LABELS{

  PRODUCT_TYPE
  LABEL_NAME
}

In fact, even when creating the simplest command

SELECT PRODUCT_TYPE-LABELS.PRODUCT_TYPE
FROM PRODUCT_TYPE-LABELS

I obtain the following error ORA-00933: SQL command not properly ended that I can't solve. I'm working on Apex Oracle (Required for this course). Thanks !

score 0 · Answer 1 · answered Mar 11 '21 at 23:04

If your table is really named PRODUCT_TYPE-LABELS then you need to enclose it within double quotes. - is not a standard character that is allowed in table names so to use special characters such as that, you need to put quotes around the table. I would recommend AGAINST using a table name such as that and maybe use something like PRODUCT_TYPE_LABEL.

Does the query work from SQL Developer, SQLPlus or any other tool that you can use to run queries?

Try running the query:

SELECT PRODUCT_TYPE
FROM "PRODUCT_TYPE-LABELS"

Thanks! After correcting this, the solutions from worked perfectly — Nathan Tasset, Mar 12 '21 at 08:17

score 0 · Answer 2 · edited Apr 06 '21 at 10:12

select * from (
    select rownum rowno, CUST_ID,CUST_NAME,no_of_orders,orders_amount,EMAIL from (
    select mst.CUST_ID, mst.CUST_NAME, count(mst.INVONO) no_of_orders, sum(SUB_TOTAL) orders_amount, au.EMAIL
    from sales_mst mst,CUSTOMER_INFO cust, APP_USERS au
    where cust.USER_NAME = au.USERNAME
    and cust.cust_id=mst.cust_id
    and (au.gender= :P41_GENDER or :P41_GENDER is null)
    and (au.DOB = :P41_DOB or :P41_DOB is null)
    group by mst.CUST_ID, mst.CUST_NAME,au.EMAIL
    having nvl(sum(SUB_TOTAL),0) > 0
    order by 3 desc,4 desc
    )) where rowno <=:P41_TO_CUSTOMER
/*select * from (
select rownum rowno, CUST_ID,CUST_NAME,no_of_orders,orders_amount from (
select mst.CUST_ID, mst.CUST_NAME, count(mst.INVONO) no_of_orders, sum(SUB_TOTAL) orders_amount
  from sales_mst mst
 group by mst.CUST_ID, mst.CUST_NAME
having nvl(sum(SUB_TOTAL),0) > 0
 order by 3 desc,4 desc
)) where rowno <=:P41_TO_CUSTOMER
*/

SQL Many-to-Many Query in Apex Oracle

2 Answers2