SQL query error with variable

Question

Trying to run this query but its returning errors:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 156 [code] => 156 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'AND'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'AND'. ) )

SQL query:

 SELECT routines.date, routines.time, 
SUM( CASE WHEN measurements.title =  'T_Luft_Temperatur' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Temperatur,
SUM( CASE WHEN measurements.title =  'T_Badende_per_Time' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Badende_Per_Time,
SUM( CASE WHEN measurements.title =  'T_Luft_Temperatur' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Luft_Temperatur,
SUM( CASE WHEN measurements.title =  'M_Fritt_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS FrittKlor,
SUM( CASE WHEN measurements.title =  'M_Bundet_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS BundetKlor,
SUM( CASE WHEN measurements.title =  'M_Total_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS TotalKlor,
SUM( CASE WHEN measurements.title =  'M_PH' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS PH,
SUM( CASE WHEN measurements.title =  'M_Auto_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS AutoKlor,
SUM( CASE WHEN measurements.title =  'M_Auto_PH' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS AutoPh
AND routines.date between '".$fraDato."' AND '".$tilDato."'
AND pools.name = '".$basseng."' 
AND routines.time between '".$fraTid."' AND '".$tilTid."'
FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
INNER JOIN pools ON measure_routine.pool_id = pools.id
GROUP BY routines.date, routines.time
ORDER BY routines.date, routines.time;

Where is the rest of your query? You need something like: select * from tablename where ... You can't have a query that starts with the word "AND" — Derek
– Derek, Commented Apr 21, 2014 at 15:31
well, you don't have a valid query. You have part of a query. Is that you're entire query? If not, please include the rest of it. — Tucker
– Tucker, Commented Apr 21, 2014 at 15:31
You might have some quote in one of those vars,show the whole thing with the exact place where the error is occurring. — Mihai
– Mihai, Commented Apr 21, 2014 at 15:32

Derek · Accepted Answer · 2014-04-21 15:34:46Z

 SELECT routines.date, routines.time, 
SUM( CASE WHEN measurements.title =  'T_Luft_Temperatur' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Temperatur,
SUM( CASE WHEN measurements.title =  'T_Badende_per_Time' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Badende_Per_Time,
SUM( CASE WHEN measurements.title =  'T_Luft_Temperatur' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Luft_Temperatur,
SUM( CASE WHEN measurements.title =  'M_Fritt_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS FrittKlor,
SUM( CASE WHEN measurements.title =  'M_Bundet_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS BundetKlor,
SUM( CASE WHEN measurements.title =  'M_Total_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS TotalKlor,
SUM( CASE WHEN measurements.title =  'M_PH' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS PH,
SUM( CASE WHEN measurements.title =  'M_Auto_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS AutoKlor,
SUM( CASE WHEN measurements.title =  'M_Auto_PH' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS AutoPh

FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
INNER JOIN pools ON measure_routine.pool_id = pools.id

WHERE routines.date between '".$fraDato."' AND '".$tilDato."'
AND pools.name = '".$basseng."' 
AND routines.time between '".$fraTid."' AND '".$tilTid."'

GROUP BY routines.date, routines.time
ORDER BY routines.date, routines.time;

UnhandledExcepSean · Accepted Answer · 2014-04-21 15:35:25Z

 SELECT routines.date, routines.time, 
SUM( CASE WHEN measurements.title =  'T_Luft_Temperatur' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Temperatur,
SUM( CASE WHEN measurements.title =  'T_Badende_per_Time' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Badende_Per_Time,
SUM( CASE WHEN measurements.title =  'T_Luft_Temperatur' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS Luft_Temperatur,
SUM( CASE WHEN measurements.title =  'M_Fritt_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS FrittKlor,
SUM( CASE WHEN measurements.title =  'M_Bundet_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS BundetKlor,
SUM( CASE WHEN measurements.title =  'M_Total_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS TotalKlor,
SUM( CASE WHEN measurements.title =  'M_PH' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS PH,
SUM( CASE WHEN measurements.title =  'M_Auto_Klor' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS AutoKlor,
SUM( CASE WHEN measurements.title =  'M_Auto_PH' THEN CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ) ELSE NULL END) AS AutoPh
FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
INNER JOIN pools ON measure_routine.pool_id = pools.id
WHERE routines.date between '".$fraDato."' AND '".$tilDato."'
AND pools.name = '".$basseng."' 
AND routines.time between '".$fraTid."' AND '".$tilTid."'
GROUP BY routines.date, routines.time
ORDER BY routines.date, routines.time;

Collectives™ on Stack Overflow

SQL query error with variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related