MYSQL Workbench Error while trying nested query

Question

I am using MySQL workbench 8.0 . I am trying to implement a nested query for the following query which is working. I am trying to avoid the create table command. However it is showing the error.( Select is not valid at this position for this server version, expecting '(', with )

The executable query is

create table recent as
select f.member_id,f.fill_date,f.insurancepaid, 
p.member_first_name,p.member_last_name,d.drug_name,
row_number() over (partition by p.member_id order by f.fill_date desc) as flag
from fact_member_drug f 
left join dimension_drug_name p 
on f.member_id = p.member_id 
left join dim_drugdetail_table d 
on f.drug_ndc = d.drug_ndc;

select member_id,member_first_name,member_last_name,drug_name,fill_date,insurancepaid 
from recent where flag = 1;

The query that I wrote is

select sub.member_id,sub.member_first_name,sub.member_last_name,sub.drug_name,sub.fill_date,sub.insurancepaid 
from (
    select f.member_id,f.fill_date,f.insurancepaid, 
    p.member_first_name,p.member_last_name,d.drug_name,
    row_number() over (partition by p.member_id order by f.fill_date desc) as flag
    from fact_member_drug f 
    left join dimension_member p 
    on f.member_id = p.member_id 
    left join dimension_drug_name d 
    on f.drug_ndc = d.drug_ndc;
) as sub
where sub.flag = 1;

Kindly help what am I doing wrong.

Thanks

Zaynul Abadin Tuhin · Accepted Answer · 2018-12-14 05:14:32Z

you can try like below

   with cte as
   ( 
    select f.member_id,f.fill_date,f.insurancepaid, 
    p.member_first_name,p.member_last_name,d.drug_name,
    row_number() over (partition by p.member_id order by f.fill_date desc) as flag
    from fact_member_drug f 
    left join dimension_drug_name p 
    on f.member_id = p.member_id 
    left join dim_drugdetail_table d 
    on f.drug_ndc = d.drug_ndc
 )  select * from cte where flag=1

from your query just remove semicolon

select sub.member_id,sub.member_first_name,sub.member_last_name,sub.drug_name,sub.fill_date,sub.insurancepaid 
from (
    select f.member_id,f.fill_date,f.insurancepaid, 
    p.member_first_name,p.member_last_name,d.drug_name,
    row_number() over (partition by p.member_id order by f.fill_date desc) as flag
    from fact_member_drug f 
    left join dimension_member p 
    on f.member_id = p.member_id 
    left join dimension_drug_name d 
    on f.drug_ndc = d.drug_ndc // just remove semicolon
) as sub
where sub.flag = 1

Collectives™ on Stack Overflow

MYSQL Workbench Error while trying nested query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related