CODEWITHSUNDEEP
sqlsql-servervariables
user3270211
user3270211

Reputation: 933

SQL query error with variable

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;

Upvotes: 0

Views: 74

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

 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;

Upvotes: 2

Derek
Derek

Reputation: 8763

 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;

Upvotes: 2

Related Questions