Crosstab Query and Specifying Selection Criteria

[hobbson]

I have a basic crosstab queries that counts the models of each type of server device in a data center. What I'm trying to do is restrict the crosstab query from returning counts for devices that are less than a specifc number....say 100 for exampe.

So I want my query to display server models where there are 101 or more.

Here is SQL statement.

TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) ON DEVICE_SPECS.DEVID = Device.DEVID
WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") AND ((DEVICE_SPECS.MFR)="sun") AND ((Device.Disposition) Like "act*"))
GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
PIVOT DEVICE_SPECS.[Equipment Type];

How can I do this? I can't place the condition on the Criteria line so I feel stuck and stumped.

Thanks in Advance,
-Frank

[MMcCarthy]

Frank

You can place it on the criteria as follow:

TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
ON DEVICE_SPECS.DEVID = Device.DEVID
WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
AND ((DEVICE_SPECS.MFR)="sun") 
AND ((Device.Disposition) Like "act*")) 
AND Count(DEVICE_SPECS.DEVID) > 100
GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
PIVOT DEVICE_SPECS.[Equipment Type];
 

Mary

[NeoPa]

Try that, and if it works as you want then simply ignore my post.
If not, try :

TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
ON DEVICE_SPECS.DEVID = Device.DEVID
WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
AND ((DEVICE_SPECS.MFR)="sun") 
AND ((Device.Disposition) Like "act*"))
GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
HAVING Count(DEVICE_SPECS.DEVID) > 100
PIVOT DEVICE_SPECS.[Equipment Type]

As the Count() results cannot be available when the incoming records are initially processed, this must be done in the HAVING clause which is processed AFTER the GROUP BY clause.
It can be put in the Criteria box as Mary says, but when translated to SQL it should end up in the HAVING clause if entered properly (I'm pretty sure).

[MMcCarthy]

Try that, and if it works as you want then simply ignore my post.
If not, try :

TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
ON DEVICE_SPECS.DEVID = Device.DEVID
WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
AND ((DEVICE_SPECS.MFR)="sun") 
AND ((Device.Disposition) Like "act*"))
GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
HAVING Count(DEVICE_SPECS.DEVID) > 100
PIVOT DEVICE_SPECS.[Equipment Type]

As the Count() results cannot be available when the incoming records are initially processed, this must be done in the HAVING clause which is processed AFTER the GROUP BY clause.
It can be put in the Criteria box as Mary says, but when translated to SQL it should end up in the HAVING clause if entered properly (I'm pretty sure).

I think you are right Ade (Everybody has to be at least once) :p

[NeoPa]

So I make up for a couple of million of those poor souls who never managed it even the once then.
...and you're let off the hook :D

[hobbson]

NeoPa,

When I copy your SQL statement over the existing SQL statements for the query I get a "Syntax error in TRANSFORM statement" reply.

Thanks,
-Frank

[hobbson]

Mary,

Thanks very much for the assistance. When I copied your SQL statement over the existing statement, I got the following error.

Cannot have aggregate function in WHERE clause ((ACTIVE_GRID_LOCATIONS.SITE_CODE="12h" Or ACTIVE_GRID_LOCATIONS.SITE_CODE="9h") And DEVICE_SPECS.MFR="sun" And Device.Disposition Like "act*" And Count(DEVICE_SPECS.DEVID)>100).

Any suggestions?

Thanks,
-Frank

Frank

You can place it on the criteria as follow:

TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
ON DEVICE_SPECS.DEVID = Device.DEVID
WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
AND ((DEVICE_SPECS.MFR)="sun") 
AND ((Device.Disposition) Like "act*")) 
AND Count(DEVICE_SPECS.DEVID) > 100
GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
PIVOT DEVICE_SPECS.[Equipment Type];
 

Mary

[NeoPa]

NeoPa,

When I copy your SQL statement over the existing SQL statements for the query I get a "Syntax error in TRANSFORM statement" reply.

Thanks,
-Frank

I'll have to leave this one to Mary I'm afraid Frank.
My TRANSFORM skills are almost non-existent. I simply suggested a change to the original string in a more general area. Sorry.

[MMcCarthy]

Try this ...

 
TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
ON DEVICE_SPECS.DEVID = Device.DEVID
WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
AND ((DEVICE_SPECS.MFR)="sun") 
AND ((Device.Disposition) Like "act*")) 
GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
HAVING  Count(DEVICE_SPECS.DEVID) > 100
PIVOT DEVICE_SPECS.[Equipment Type];

Mary

[hobbson]

Mary,

Thanks again but now I'm getting the TRANSFORM error.

[quote]SYNTAX ERROR IN TRANSFORM STATEMENT

-Frank

Try this ...

 
TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
ON DEVICE_SPECS.DEVID = Device.DEVID
WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
AND ((DEVICE_SPECS.MFR)="sun") 
AND ((Device.Disposition) Like "act*")) 
GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
HAVING  Count(DEVICE_SPECS.DEVID) > 100
PIVOT DEVICE_SPECS.[Equipment Type];

Mary

[MMcCarthy]

Mary,

Thanks again but now I'm getting the TRANSFORM error.

SYNTAX ERROR IN TRANSFORM STATEMENT

-Frank

Try this ...

TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDevID
SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Count(DEVICE_SPECS.DEVID) AS TotalOfDevID
FROM (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
INNER JOIN DEVICE_SPECS 
ON Device.DEVID = DEVICE_SPECS.DEVID)
WHERE ACTIVE_GRID_LOCATIONS.SITE_CODE IN ("9h", "12h")
AND DEVICE_SPECS.MFR="sun" 
AND Device.Disposition Like "act*"
GROUP BY  DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
HAVING  Count(DEVICE_SPECS.DEVID) > 100
PIVOT DEVICE_SPECS.[Equipment Type];