working with an array 1

[onressy]

Hi everyone, i have manages to build an array from a user selecting some inventory. The array is of the inventoryIDs. I can iterate thru the InventoryID like so:
For intArrIndex=0 to UBound(arrInventoryIDs)
response.write " InventoryID = " & arrInventoryIDs(intArrIndex)
Next

In the inventory table, where InventoryIDs are stored, i need to compare the Price and extraPersonCharge for each InventoryID in the array. If either one is different, then for each InventoryID that is different i need to response.write a message.

I'm still an array beginner so any help would be appreciated, thanks!

 

[onressy]

I forgot this is what i'm working with:

	strInventoryIDs = Replace(strInventoryIDs,"inv","")
	arrInventoryIDs = Split(strInventoryIDs,"~")

	strSQL = "Select InventoryID" _
			& "Max(RateValue) AS RateValue, " _
			& "Min(ExtraPersonCharge) AS ExtraPersonCharge, " _
			& "WHERE"

	For intArrIndex=0 to UBound(arrInventoryIDs)
		If intArrIndex <> 0 Then
			strSQL = strSQL & " OR"
		End If
		strSQL = strSQL & " InventoryID = " & arrInventoryIDs(intArrIndex)

		response.write " InventoryID = " & arrInventoryIDs(intArrIndex)
	
	Next

 

[ogri]

Hi

First thing, using IN is probably easier that the multiple IF statements.

For example:-

    strInventoryIDs = Replace(strInventoryIDs,"inv","")
    arrInventoryIDs = Split(strInventoryIDs,"~")

    strSQL = "Select InventoryID" _
            & "Max(RateValue) AS RateValue, " _
            & "Min(ExtraPersonCharge) AS ExtraPersonCharge, " _
            & "WHERE InventoryID IN ("

    For intArrIndex=0 to UBound(arrInventoryIDs)
        If intArrIndex <> 0 Then
            strSQL = strSQL & arrInventoryIDs(intArrIndex)
        else
            strSQL = strSQL & ","& arrInventoryIDs(intArrIndex)
        End If    
        strSQL = strSQL & ")"
    Next

However, I am a bit lost on what you are trying to do exactly. Are you trying to get all the inventory IDs where either the RateValue or the ExtraPersonCharge is not the same as the max Ratevalue or ExtraPersonCharge for any inventory id on the table? If so I think the easiest thing to do is use a subselect to get the max values of each of those and check against them in the SQL, so you only bring back the ones you are interested in.

All the best

Keith

 

[onressy]

What i'm trying to do is compare the db values pulled from each InventoryID. If they are the same then response.write these InventoryID are the same else response.write these inventoryID are unlike not other. Does that make sense?

thanks

 

[ogri]

Hi

Think you want something like this then.

    strInventoryIDs = Replace(strInventoryIDs,"inv","")
    arrInventoryIDs = Split(strInventoryIDs,"~")

    strSQL = "Select InventoryID" _
            & "decode(RateValue,ExtraPersonCharge,'Same','Different') " _
            & "WHERE InventoryID IN ("

    For intArrIndex=0 to UBound(arrInventoryIDs)
        If intArrIndex <> 0 Then
            strSQL = strSQL & arrInventoryIDs(intArrIndex)
        else
            strSQL = strSQL & ","& arrInventoryIDs(intArrIndex)
        End If    
        strSQL = strSQL & ")"
    Next

Then just loop through the rows returned by the cursor and put out the inventory ID and the message (change the messages to something more meaningful).

This assumes Oracle SQL and that Inventory ID is the unique key of the table. Not sure what database you are using. If SQL Server then I think you can use "select case" to do a similar job to decode.

All the best

Keith

 

[Tarwn]

Or better yet you can get rid of the loop altogether (which has an error in the prev post - end paran needs to be outside loop):

 strInventoryIDs = Replace(strInventoryIDs,"inv","")
 strInventoryIDs = Replace(strInventoryIDs,"~",",")

    strSQL = "Select InventoryID" _
            & "decode(RateValue,ExtraPersonCharge,'Same','Different') " _
            & "WHERE InventoryID IN (" & strInventoryIDs & ")"

-T

 

[onressy]

cheers to both, thanks Tarwn for the effecientcy
</now if only i could spell>

 

[Tarwn]

No problem, it's like efficiency with a bit extra.
Huh, that made more sense before I typed it, now it seems to be a bit backwards, ah well