oracle, query joining issues "SQL command not properly ended"

Question

I would like to join 2 SQL queries in oracle, but I'm really not familiar with it, my structure seems correct to me, but I have this SQL error message: "[Err] ORA-00933: SQL command not properly ended"

So I have two queries: the first one is:

select 
    campaign_id, count(*) as "number of emails sent"
from 
    dg_res_sent
where 
    dg_end_date > sysdate
group by campaign_id

and the second one is:

select 
    offer_name, 
    campaign_id, 
    offer_category as "link category", 
    count(*) as "number of clicks"
from 
    dg_res_click
where
    dg_end_date > sysdate
and
    SUBSTR(offer_name,1,3) = 'SKU'
group by 
    offer_name, 
    campaign_id, 
    offer_category

I want to do my join on campaign_id so I did:

select 
    offer_name, 
    campaign_id, 
    offer_category as "link category", 
    count(*) as "number of clicks",
    sent.nb_sent
from 
    dg_res_click
where
    dg_end_date > sysdate
and
    SUBSTR(offer_name,1,3) = 'SKU'
inner join
    (select 
        campaign_id, count(*) as "nb_sent"
    from 
        dg_res_sent
    where 
        dg_end_date > sysdate
    group by campaign_id) sent
on 
    sent.campaign_id = dg_res_click.campaign_id
group by 
    offer_name, 
    campaign_id, 
    offer_category

any idea why I get this message:

[Err] ORA-00933: SQL command not properly ended

I removed the MySQL and SQL Server tags, because the question is explicitly about Oracle. — Gordon Linoff
– Gordon Linoff, Commented May 12, 2014 at 5:02

Ehsan Khaveh · Accepted Answer · 2014-05-12 04:49:44Z

2

Your Inner join should be placed before your where clause.

answered May 12, 2014 at 4:49

Ehsan Khaveh

1,45317 silver badges21 bronze badges

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

Comments

G one · Accepted Answer · 2014-05-12 05:27:05Z

2

You have placed inner join at wrong position.It should be before the where clause

select 
    dg_res_click.offer_name, 
    dg_res_click.campaign_id, 
    dg_res_click.offer_category as "link category", 
    count(*) as "number of clicks",
    sent.nb_sent
from 
    dg_res_click 
inner join
    (select 
        campaign_id, count(*) as nb_sent
    from 
        dg_res_sent
    where 
        dg_end_date > sysdate
    group by campaign_id) sent
on 
    sent.campaign_id = dg_res_click.campaign_id

where
    dg_res_click .dg_end_date > sysdate
and
    SUBSTR(dg_res_click.offer_name,1,3) = 'SKU'
group by 
    dg_res_click.offer_name, 
    dg_res_click.campaign_id, 
    dg_res_click.offer_category,
    sent.nb_sent

edited May 12, 2014 at 5:27

answered May 12, 2014 at 4:48

G one

2,7392 gold badges17 silver badges18 bronze badges

7 Comments

user2461031 Over a year ago

thanks, it seems correct to me, but I have this error message: ""SENT"."NB_SENT": invalid identifier"

user2461031 Over a year ago

but it won't be a string anymore, no?

G one Over a year ago

its just an alias so the quotes are not required

G one Over a year ago

And in mysql back ticks are used for strings.. `nb sent`

user2461031 Over a year ago

in your first select should it be dg_res_click.offer_name and dg_res_click.campaign_id instead of sent.offer_name and sent.campaign_id ?

|

Collectives™ on Stack Overflow

oracle, query joining issues "SQL command not properly ended"

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related