1 of 1 people found this helpful
this code will count the fixed drives and provide a column: (Give it a shot.)SELECT DISTINCTc.DeviceName,(select count(*) from FixedDrives as F1 where c.Computer_Idn = F1.Computer_Idn) as 'Fixed drive count'FROM Computer as c
Thanks, that was a great step for me in the right direction.
However it still does not seem to work the way I think I am trying to get it to work.
UNION ALL SELECT DISTINCT Computer.Computer_Idn, Computer.DeviceName, 0, null, null, 0, (select count(*) from FixedDrives as F1 where Computer.Computer_Idn = F1.Computer_Idn) as 'Fixed drive count', null, null FROM Computer LEFT OUTER JOIN FixedDrives ON Computer.Computer_Idn = FixedDrives.Computer_Idn UNION ALL
This is what I have as it has to follow this type of pattern.
I'm using the base report of HArdware Inventory from LDMS 9.2 in this case and modifying it, so I am keeping in line with the existing SQL code for column order and display, if tha thelps.
I'm now getting: 'Conversion failed when converting the nvarchar value 'BIOS - Copyright String' to data type int.'
The first part of the query the report uses is:
SELECT distinct Table1.* FROM ( SELECT DISTINCT Computer.Computer_Idn, Computer.DeviceName, 0 Section, null GroupInt, null GroupStr, 0 DetailNo, N'BIOS - Copyright String' DetailLabel, null IntValue, COPYRIGHT StrValue FROM Computer LEFT OUTER JOIN BIOS ON Computer.Computer_Idn = BIOS.Computer_Idn UNION ALL )
Seems I'm either overlooking something or the new code is pretty much on par with the layout.
The issue is that each sql code chunk needs to have the exact same column set. They have to be in the exact same positions, and they have to be the same cast. So you may need to recast certain data from different places. If it were me i'd probably recast everything to varchar so it's simpler that way, but your call on that one.
If you need some more help with this, i can look at it later this evening. If you haven't already please post the whole code so i can try it out on my end, even if you fix it. I'm curious about what all you're doing with it.