471,319 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

Crosstab Query and Specifying Selection Criteria

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
10 5836
MMcCarthy
14,534 Expert Mod 8TB
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
32,405 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
32,405 Expert Mod 16PB
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
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
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
32,405 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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.

Similar topics

3 posts views Thread by Susan | last post: by
1 post views Thread by Nathan Bloomfield | last post: by
2 posts views Thread by Sherman H. | last post: by
4 posts views Thread by Judy | last post: by
2 posts views Thread by deejayquai | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.