Sample SQL query to show max RAM based on Model.

This question was posted in the Usenet groups..

“I need to present a report that shows the maximum supported memory
configuration for a system type, so I would like to be able to have a column
on my report that shows the maximum supported memory, based upon the field ,
v_GS_Computer_system.model0 AS “Model”.  If model = xyz then <new column>
shows “2GB” or whatever the maximum memory is.  I have to research each
system type and determine maximum memory manually, but one I have the info, I
can plug it into my query and I hope translate it to something that
management can quickly pull up and know what they are looking at.

Also, and I probably should put this in another post, but is it possible to
combine 2 fields?  Instead of video width showing 1024 and video height
showing 768, it would be nice if I could combone them into a single column
called “Video Resolution” and have it show in the format of 1024 x 768.”

In a nutshell use the case function to provide the max RAM based on model name. see below.

select distinct
v_GS_Computer_system.Name0 AS “PC Name”
, v_GS_Computer_system.UserName0 AS “User last logged on”
, v_GS_Operating_System.caption0 AS “OS”
, v_gs_Operating_system.csdversion0 AS “SP Level”
, v_GS_Computer_system.manufacturer0 AS “Make”
, v_GS_Computer_system.model0 AS “Model”
, v_GS_x86_PC_memory.totalphysicalmemory0 AS “Total Memory”
, case v_GS_Computer_system.model0
when ‘Virtual Machine’ then ‘4 GB’
when ‘Aspire 5000’ then ‘2 GB’
else ‘Unknown’
end as ‘Max Ram’
, v_gs_processor.maxclockspeed0 AS “CPU Speed”
, cast(v_gs_video_controller.CurrentHorizontalResolution0 as Varchar) + ‘ x ‘ + cast(v_gs_video_controller.CurrentVerticalResolution0 as varchar) AS “Video Resolution”
from
v_gs_computer_system,
v_GS_x86_PC_memory,
v_GS_processor,
v_gs_video_controller,
v_GS_Operating_system

WHERE
v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
and v_GS_processor.resourceid = v_gs_computer_system.resourceid
and v_GS_video_controller.resourceid = v_gs_computer_system.resourceid
and v_GS_Operating_system.caption0 NOT like ‘%2000 Server%’
and v_GS_Operating_system.caption0 NOT like ‘%Server 2003%’

Order by
 v_gs_computer_system.name0

Leave a Reply

Your email address will not be published. Required fields are marked *