PDA

View Full Version : [SOLVED] Need Help on ADODB Query, need to convert field to int and order on it



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)

Blade Hunter
01-12-2015, 03:08 PM
You are sorting on S.[Alias] first. Get rid of the order by in the first part of the union as you are sorting post union.

What DB is this? Access maybe?

brusk
01-12-2015, 03:28 PM
Yes sorting by Alias and then Row. It's all within excel, which has been very temperamental when it comes to ADODB queries. I'll try taking out the first order and see what happens, currently it all works it's just sorting Row as text instead of numberic so it's order 1, 10, 11, 2, 3, 4 etc.

brusk
01-12-2015, 03:35 PM
Look like I got it now.


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], CInt(HW.[Row]) As RowNum 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])) 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], CInt(HW.[Row]) As RowNum 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], RowNum", arResults, True, False)

Blade Hunter
01-12-2015, 03:57 PM
Cool, the other thing I was going to suggest is format rownum as a string and make it Right("000" & RowNum,4)