Convert MS Compliant GROUP BY for Infomaker

[unknown]

Hi all,
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 such as Infomaker.

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))

[Paul Horan[Sybase]]

This really isn't an Infomaker problem - it's the SQLAnywhere database that
isn't liking the syntax.

From the looks of your query, you could rewrite this completely without
subqueries. I'm not sure why you have the Max() function in that first item
though...

This code (of course) isn't tested.

SELECT
'Month' = DATEPART(MONTH, MAX(w1.wo_date_created)),
'Jobs Raised' = count(*),
'Jobs Completed' =
sum(IF wo_status = 7 THEN 1 ELSE 0 ENDIF),
'Jobs Outstanding' =
sum(IF wo_status > 7 THEN 1 ELSE 0 ENDIF)
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))
--
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/

Post by unknown
Hi all,
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 such as Infomaker.
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!)
and adding BEGIN/END would allow this to work in I-SQL
against a sybase replica of the database.
"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
*************************************/
CONVERT(VARCHAR
(10),GETDATE(),103), 103))
/************************************/
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
GROUP BY
DATEPART(MONTH, w1.wo_date_created)
ORDER BY
DATEPART(MONTH, MAX(w1.wo_date_created))

[unknown]

I had lost the bookmark for this forum post, and completely
given up by the time you replied. Just wanted to thank you
massively Paul. Much appreciated.

Post by Paul Horan[Sybase]
This really isn't an Infomaker problem - it's the
SQLAnywhere database that isn't liking the syntax.
From the looks of your query, you could rewrite this
completely without subqueries. I'm not sure why you have
the Max() function in that first item though...
This code (of course) isn't tested.
SELECT
'Month' = DATEPART(MONTH, MAX(w1.wo_date_created))
,
'Jobs Raised' = count(*),
'Jobs Completed' =
sum(IF wo_status = 7 THEN 1 ELSE 0 ENDIF),
'Jobs Outstanding' =
sum(IF wo_status > 7 THEN 1 ELSE 0 ENDIF)
FROM
workorders AS w1
WHERE
GROUP BY
DATEPART(MONTH, w1.wo_date_created)
ORDER BY
DATEPART(MONTH, MAX(w1.wo_date_created))
--
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/

Post by unknown
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 such as Infomaker.
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? >

Post by unknown
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
allow this to work in I-SQL against a sybase replica of

the database. >

Post by unknown
"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. >

Post by unknown
/*************************************
SECTION REPLICATES RETRIEVAL ARGUMENTS
*************************************/
CONVERT(VARCHAR
(10),GETDATE(),103), 103))
/************************************/
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
GROUP BY
DATEPART(MONTH, w1.wo_date_created)
ORDER BY
DATEPART(MONTH, MAX(w1.wo_date_created))