By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,106 Members | 2,679 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,106 IT Pros & Developers. It's quick & easy.

Crosstab Query and Specifying Selection Criteria

P: 4
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
Mar 1 '07 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Frank

You can place it on the criteria as follow:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
  2. SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
  3. FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
  4. ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
  5. ON DEVICE_SPECS.DEVID = Device.DEVID
  6. WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
  7. Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
  8. AND ((DEVICE_SPECS.MFR)="sun") 
  9. AND ((Device.Disposition) Like "act*")) 
  10. AND Count(DEVICE_SPECS.DEVID) > 100
  11. GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
  12. PIVOT DEVICE_SPECS.[Equipment Type];
  13.  
Mary
Mar 1 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
Try that, and if it works as you want then simply ignore my post.
If not, try :
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
  2. SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
  3. FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
  4. ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
  5. ON DEVICE_SPECS.DEVID = Device.DEVID
  6. WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
  7. Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
  8. AND ((DEVICE_SPECS.MFR)="sun") 
  9. AND ((Device.Disposition) Like "act*"))
  10. GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
  11. HAVING Count(DEVICE_SPECS.DEVID) > 100
  12. 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).
Mar 5 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try that, and if it works as you want then simply ignore my post.
If not, try :
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
  2. SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
  3. FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
  4. ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
  5. ON DEVICE_SPECS.DEVID = Device.DEVID
  6. WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
  7. Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
  8. AND ((DEVICE_SPECS.MFR)="sun") 
  9. AND ((Device.Disposition) Like "act*"))
  10. GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
  11. HAVING Count(DEVICE_SPECS.DEVID) > 100
  12. 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
Mar 6 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
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
Mar 6 '07 #5

P: 4
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
Mar 8 '07 #6

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
  2. SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
  3. FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
  4. ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
  5. ON DEVICE_SPECS.DEVID = Device.DEVID
  6. WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
  7. Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
  8. AND ((DEVICE_SPECS.MFR)="sun") 
  9. AND ((Device.Disposition) Like "act*")) 
  10. AND Count(DEVICE_SPECS.DEVID) > 100
  11. GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
  12. PIVOT DEVICE_SPECS.[Equipment Type];
  13.  
Mary
Mar 8 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 9 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
  3. SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
  4. FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
  5. ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
  6. ON DEVICE_SPECS.DEVID = Device.DEVID
  7. WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
  8. Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
  9. AND ((DEVICE_SPECS.MFR)="sun") 
  10. AND ((Device.Disposition) Like "act*")) 
  11. GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
  12. HAVING  Count(DEVICE_SPECS.DEVID) > 100
  13. PIVOT DEVICE_SPECS.[Equipment Type];
Mary
Mar 9 '07 #9

P: 4
Mary,

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

[quote]SYNTAX ERROR IN TRANSFORM STATEMENT

-Frank

Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDEVID
  3. SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
  4. FROM DEVICE_SPECS INNER JOIN (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
  5. ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
  6. ON DEVICE_SPECS.DEVID = Device.DEVID
  7. WHERE (((ACTIVE_GRID_LOCATIONS.SITE_CODE)="12h" 
  8. Or (ACTIVE_GRID_LOCATIONS.SITE_CODE)="9h") 
  9. AND ((DEVICE_SPECS.MFR)="sun") 
  10. AND ((Device.Disposition) Like "act*")) 
  11. GROUP BY DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Device.Disposition
  12. HAVING  Count(DEVICE_SPECS.DEVID) > 100
  13. PIVOT DEVICE_SPECS.[Equipment Type];
Mary
Mar 12 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary,

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

SYNTAX ERROR IN TRANSFORM STATEMENT

-Frank
Try this ...

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(DEVICE_SPECS.DEVID) AS CountOfDevID
  2. SELECT DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE, Count(DEVICE_SPECS.DEVID) AS TotalOfDevID
  3. FROM (ACTIVE_GRID_LOCATIONS INNER JOIN Device 
  4. ON ACTIVE_GRID_LOCATIONS.GRIDID = Device.GRIDID) 
  5. INNER JOIN DEVICE_SPECS 
  6. ON Device.DEVID = DEVICE_SPECS.DEVID)
  7. WHERE ACTIVE_GRID_LOCATIONS.SITE_CODE IN ("9h", "12h")
  8. AND DEVICE_SPECS.MFR="sun" 
  9. AND Device.Disposition Like "act*"
  10. GROUP BY  DEVICE_SPECS.MFR, ACTIVE_GRID_LOCATIONS.SITE_CODE
  11. HAVING  Count(DEVICE_SPECS.DEVID) > 100
  12. PIVOT DEVICE_SPECS.[Equipment Type]; 
  13.  
Mar 12 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.