T-SQL Conversion
Tommy
I have problems on a day to day basis converting T-SQL that works with
MS SQL 2005/2008 but fails to work with Sybase products. The latest
candidate is below, apologies if it is hard to read, I'm just looking
for a suggestion on how to, for example, get this statement to run
from I-SQL. It seems to have an issue with the GROUP BY clause,
something to do with applying it to the nested selects incorrectly?
Please ignore the variable declarations, this SQL is intended to be
used with Infomaker so the variables are to be replaced with Retrieval
arguments, the example below simply returns today's datetime, and the
date 1 year ago (I appreciate the query can miscount the first/last
month but I'll deal with that once I can get the group count to
work!) Wishful thinking on my part was removing the '@' symbols and
adding BEGIN/END would allow this to work in I-SQL against a sybase
replica of the database.
However, I receive the message:
"Line 55, column 1
Could not execute statement.
ASA Error - 149: Function or column reference to 'wo_date_created' in
the select list must also appear in a GROUP BY"
Any suggestions (other than give up...) are very welcome.
/*************************************
SECTION REPLICATES RETRIEVAL ARGUMENTS
*************************************/
DECLARE @dttSTART DATETIME
DECLARE @dttEND DATETIME
SET @dttSTART = DATEADD(yy, -1, CONVERT(DATETIME, CONVERT(VARCHAR
(10),GETDATE(),103), 103))
SET @dttEnd = GETDATE()
/************************************/
SELECT
'Month' = DATEPART(MONTH, MAX(w1.wo_date_created)),
'Jobs Raised' =
(
SELECT
COUNT(w2.wo_id)
FROM
workorders AS w2
WHERE
w2.wo_date_created <= @dttEND AND
w2.wo_date_created > @dttSTART AND
DATEPART(MONTH, w2.wo_date_created) = DATEPART(MONTH,
w1.wo_date_created)
),
'Jobs Completed' =
(
SELECT
COUNT(w3.wo_id)
FROM
workorders AS w3
WHERE
w3.wo_date_created <= @dttEND AND
w3.wo_date_created > @dttSTART AND
DATEPART(MONTH, w3.wo_date_created) = DATEPART(MONTH,
w1.wo_date_created) AND
w3.wo_status = 7
),
'Jobs Outstanding' =
(
SELECT
COUNT(w4.wo_id)
FROM
workorders AS w4
WHERE
w4.wo_date_created <= @dttEND AND
w4.wo_date_created > @dttSTART AND
DATEPART(MONTH, w4.wo_date_created) = DATEPART(MONTH,
w1.wo_date_created) AND
w4.wo_status > 7
)
FROM
workorders AS w1
WHERE
w1.wo_date_created <= @dttEND AND
w1.wo_date_created > @dttSTART
GROUP BY
DATEPART(MONTH, w1.wo_date_created)
ORDER BY
DATEPART(MONTH, MAX(w1.wo_date_created))