0

I'm struggling to write a query in the correct way. Although it does work I'm given the error:

A new statement was found but no delimiter between it and the previous one (near select)

Obviously I can still work with this error but #1. I don't particularly like 'hacky' methods of coping and #2. It's causing problems in php further down the line trying to run the query.

From Google Research it suggests that the error may be a bug however I'm dubious to believe this.

What I'm Actually Trying To Do :

I have 3 Tables:

Table P [Process_ID+, Process_Name, Risk_ID*]
Table V [Validation_ID, Process_ID+, Validation_Date]
Table R [Risk_ID*, Risk_TimePeriod]

The plan is to:

  1. Select Process Details
  2. Join Validation Details
  3. Join Risk Details
  4. Generate a "Due_Date" Based on Validation_Date and Risk_TimePeriod (i.e. If Time Period is 150 then Add 150 Days to Validation_Date).

Something like the below:

Process_ID  |  Process_Name  |  Validation_Date  |  Due_Date  |  Due_Days  |  Risk_Level
1              My_Process       2017-02-17          2017-07-17   -150         High

So here; the Due_Date has been created by adding 150 (Risk_Level) to the Validation Date.

Current Query

Set @TimePeriod = 
(Select r.Risk_TimePeriod 
from processes_active p
inner join processes_risk_config r
on r.Risk_ID = p.Process_Risk
where p.Process_ID = 2);

Select p.Process_ID,
p.Process_Name,
v.Validation_Date,
Date_Add(v.Validation_Date, interval @TimePeriod Day) as Due_Date,
Datediff(Now(), Date_Add(v.Validation_Date, interval @TimePeriod Day)) as Due_Days,
r.Risk_Level

From processes_active p

left JOIN processes_validations v
on p.Process_ID = v.Validation_Process_ID

inner join processes_risk_config r
on r.Risk_ID = p.Process_Risk

Where p.Process_ID = 2
Order By v.Validation_Date Desc

My First "Select" clause is highlighted red. This is where the error message appears. If anyone can point me as to where I've gone wrong that would be greatly appreciated! Thanks

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
billcox33
  • 35
  • 3
  • You get this error when running the query via a PHP script or from the MYSQL Console for example – RiggsFolly Feb 17 '17 at 09:14
  • I get it from both. The MySQL Console reproduces the error as shown above (the actual query is highlighted as time of launch, but it still shows the results). The problem in PHP is that as there is an error in MySQL showing; the results from the query can't be displayed due to a fetch_assoc() error. – billcox33 Feb 17 '17 at 09:16

1 Answers1

0

Turns out I was over-complicating this completely!

New Working Query :

Select

p.Process_ID,
p.Process_Name,
v.Validation_Date,
r.Risk_TimePeriod,
Date_Add(v.Validation_Date, interval r.Risk_TimePeriod Day) as Due_Date,
Datediff(Now(), Date_Add(v.Validation_Date, interval r.Risk_TimePeriod Day)) as Due_Days,
r.Risk_Level

From pdc_processes.processes_active p

left JOIN pdc_processes.processes_validations v
on p.Process_ID = v.Validation_Process_ID

inner join processes_risk_config r
on r.Risk_ID = p.Process_Risk

Where p.Process_ID = 2
Order By v.Validation_Date Desc
billcox33
  • 35
  • 3