brusk
01-12-2015, 02:41 PM
having an issue trying to get my query to order properly. Below is the working query but the problem is it's pulling the [Row] field in as text instead of an int so when it orders it it's not numerically correct. I've tried various things all of which gave me errors.
Call RunSQLQuery(ThisWorkbook.Name, "SELECT * FROM " & _ "(SELECT S.[Alias], NW.[Host], HW.[Role], NW.[HostName], HW.[OS], HW.[CPU], HW.[Memory], HW.[HD1Drive], HW.[HD1Role], HW.[HD1Size], HW.[HD1SP], HW.[HD2Drive], HW.[HD2Role], HW.[HD2Size], HW.[HD2SP], HW.[Row] FROM [" & GetTableRange("cnfTableSystems") & "] S, [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Network") & "] NW, [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Hardware") & "] HW WHERE HW.[AssetID] = S.[AssetID] AND HW.[AssetID] = NW.[AssetID] AND HW.[Role] = NW.[Role] AND CInt(HW.[Row]) = CInt(NW.[Row]) ORDER BY S.[Alias], HW.[Row]) UNION " & _
"( SELECT S.[Alias], NW.[Host], HW.[Role], NW.[HostName], HW.[OS], HW.[CPU], HW.[Memory], HW.[HD1Drive], HW.[HD1Role], HW.[HD1Size], HW.[HD1SP], HW.[HD2Drive], HW.[HD2Role], HW.[HD2Size], HW.[HD2SP], HW.[Row] FROM [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Hardware") & "] HW, [" & GetTableRange("cnfTableSystems") & "] S, [" & GetTableRange("cnfTableAVPNetwork") & "] NW WHERE HW.[AssetID] = S.[AssetID] AND HW.[AssetID] = NW.[AssetID] AND HW.[Role] = NW.[Role] AND CInt(HW.[Row]) = CInt(NW.[Row])) ORDER BY S.[Alias], HW.[Row]", arResults, True, False)
Call RunSQLQuery(ThisWorkbook.Name, "SELECT * FROM " & _ "(SELECT S.[Alias], NW.[Host], HW.[Role], NW.[HostName], HW.[OS], HW.[CPU], HW.[Memory], HW.[HD1Drive], HW.[HD1Role], HW.[HD1Size], HW.[HD1SP], HW.[HD2Drive], HW.[HD2Role], HW.[HD2Size], HW.[HD2SP], HW.[Row] FROM [" & GetTableRange("cnfTableSystems") & "] S, [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Network") & "] NW, [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Hardware") & "] HW WHERE HW.[AssetID] = S.[AssetID] AND HW.[AssetID] = NW.[AssetID] AND HW.[Role] = NW.[Role] AND CInt(HW.[Row]) = CInt(NW.[Row]) ORDER BY S.[Alias], HW.[Row]) UNION " & _
"( SELECT S.[Alias], NW.[Host], HW.[Role], NW.[HostName], HW.[OS], HW.[CPU], HW.[Memory], HW.[HD1Drive], HW.[HD1Role], HW.[HD1Size], HW.[HD1SP], HW.[HD2Drive], HW.[HD2Role], HW.[HD2Size], HW.[HD2SP], HW.[Row] FROM [" & GetTableRange("cnfTable" & arProducts(iLoop1) & "Hardware") & "] HW, [" & GetTableRange("cnfTableSystems") & "] S, [" & GetTableRange("cnfTableAVPNetwork") & "] NW WHERE HW.[AssetID] = S.[AssetID] AND HW.[AssetID] = NW.[AssetID] AND HW.[Role] = NW.[Role] AND CInt(HW.[Row]) = CInt(NW.[Row])) ORDER BY S.[Alias], HW.[Row]", arResults, True, False)