Please verify this SQL fragment

Will someone please help verify this SQL fragment?

I want to ensure that it produces the same results as the 'Group By' clause of the MS Access fragment below.

T-SQL:

Group By Case

When r.LICFAC In ('CR', 'GC') And f.SOPNO < 38 Then -1

Else 0

End

, Case

When r.LICFAC = 'MU' And f.SOSTS <> '05' Then -1

Else 0

End

MS Access:

GROUP BY IIf((([LICFAC]="CR" Or [LICFAC]="GC") And ([SOPNO]<38)),True,False)
, IIf(([LICFAC]="MU") And ([SOSTS]<>"05"),True,False)

Seems to be ok. Did you test it?

AMB

|||

Thanks, Hunchback.

The query is failing. I get a message saying that the SOPNO and SOSTS fields are invalid because they are not contained in either an aggregate function or the GROUP BY clause.

Msg 8121, Level 16, State 1, Line 58

Column 'CSAS400.RCHASE5C.BPCSFFG.FSOL02.SOPNO' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8121, Level 16, State 1, Line 58

Column 'CSAS400.RCHASE5C.BPCSFFG.FSOL02.SOSTS' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

That true, of course.

The query works in MS Access.

Is there a way to restructure the query so that I get the same results as the Access query?

Here is the entire query:

Thanks,

Insert Into Rep2

Select LICFAC

, HORD

, HSAL

, CHBCON

, CHCRDH

, CHUSRH

, SUM(Amt) As SumOfAmount

, Min(SchedDate) As MinOfSchedDate

, Min(OrigDate) As MinOfOrigDate

, CNME

, SNAME

From #Rep1 r

Inner Join CSAS400.RCHASE5C.BPCSFFG.FSOL02 f On r.CLSORD = f.SCORD

And r.HORD = f.SCORD

Group By r.LICFAC

, r.HORD

, r.HSAL

, r.CHBCON

, r.CHCRDH

, r.CHUSRH

, r.CNME

, r.SNAME

, f.SID

, Case

When r.LICFAC In ('CR', 'GC') And f.SOPNO < 38 Then -1

Else 0

End

, Case

When r.LICFAC = 'MU' And f.SOSTS <> '05' Then -1

Else 0

End

Having f.SID = 'SO'

And Not (

r.LICFAC In ('CR', 'GC')

And

f.SOPNO < 38

)

And Not (

r.LICFAC = 'MU'

And

f.SOSTS <> '05'

);

|||

Why in the having clause? It is mostly used to filter the result of the "group by" clause or an aggregate function. Can you test putting that expression in the "where" clause".

AMB

|||Thanks again, Hunchback! I placed the filter in the "where" clause.