1

I'm facing this issue where I'm trying to drop the table if it exists but I keeping getting error. Here's what I have

DROP TABLE IF EXISTS clips_cur;
CREATE TEMPORARY TABLE clips_cur(
SELECT year_r, month_n_r, IFNULL(SUM(current_clip_count),0) as curclips
FROM data_mining.clip_summary stb 
JOIN mstr_all_offer_sum o 
JOIN mstr_all_clip_red_sum d
WHERE stb.offer_id=o.oid AND
d.offer_id = o.oid AND
clip_date>='2022-03-01'AND 
clip_date>=SUBDATE( o.st_d, INTERVAL 1 DAY) AND 
clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) AND 
clip_date<='2022-03-28' AND
is_handraiser<>1 
group by year_r, month_n_r)

Here's the error:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE clips_cur( SELECT year_r, month_n_r, IFNULL(SUM(current_' at line 2

Error position: line: 1

Appreciate the help!

dummyboi
  • 11
  • 3

2 Answers2

0

Hi to create table from Query use "AS" not "()" try this:

DROP TABLE IF EXISTS clips_cur;

CREATE TEMPORARY TABLE clips_cur
AS
SELECT year_r, month_n_r, IFNULL(SUM(current_clip_count),0) as curclips
FROM data_mining.clip_summary stb 
JOIN mstr_all_offer_sum o 
JOIN mstr_all_clip_red_sum d
WHERE stb.offer_id=o.oid AND
d.offer_id = o.oid AND
clip_date>='2022-03-01'AND 
clip_date>=SUBDATE( o.st_d, INTERVAL 1 DAY) AND 
clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) AND 
clip_date<='2022-03-28' AND
is_handraiser<>1 
group by year_r, month_n_r
  • Hello, I tried this and still have error thrown at me – dummyboi Apr 25 '22 at 18:08
  • same error? or different error, because "and still have error thrown at me" is a bit general... – Luuk Apr 25 '22 at 18:15
  • This the error SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE clips_cur as SELECT year_r, month_n_r, IFNULL(SUM(curren' at line 2 – dummyboi Apr 25 '22 at 18:19
0

I tried and Work to me, you have error with JOIN its incomplete you need relation fields using "ON" not "WHERE"

example:

Select t1.field1,t2.field1 
From Table1 t1
Join Table2 t2 on t1.fkfield = t2.keyfield
Where t1.id = 1
  • Let me try this – dummyboi Apr 25 '22 at 18:20
  • Test your SELECT statement first, before CREATE statement, to ensure –  Apr 25 '22 at 18:23
  • DROP TABLE IF EXISTS clips_cur; CREATE TEMPORARY TABLE clips_cur AS SELECT year_r, month_n_r, IFNULL(SUM(current_clip_count),0) as curclips FROM data_mining.clip_summary stb JOIN mstr_all_offer_sum o ON stb.offer_id=o.oid JOIN mstr_all_clip_red_sum d ON d.offer_id = o.oid WHERE clip_date>='2022-03-01'AND clip_date>=SUBDATE( o.st_d, INTERVAL 1 DAY) AND clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) AND clip_date<='2022-03-28' AND is_handraiser<>1 group by year_r, month_n_r This is what I have it as right now, and still throws in error – dummyboi Apr 25 '22 at 18:24
  • Select statement worked in both the cases, even the create statement worked in both cases – dummyboi Apr 25 '22 at 18:25