ODBC

[campbere]

I have a problem with an sql statement that uses the function to_number. The query is used with an rdo connection to an Oracle 8 database. I am using VB6.

One suggestion that was made was that the ODBC driver I am using doesn't support the to_number functionality. Does anyone know which one does? Or how to find that information out?

I am at a loss for how to solve the problem.

here is the query:
sqlCAP = "select prod_id, prod_num, item_num, " _
& " release_date, prod_status " _
& "from PRODUCTS " _
& "where prod_id = '" & RSprod_id!Issn & "' " _
& "and current_prod = 'Y' " _
& "order by to_number(prod_num) desc, " _
& "to_number(item_num) desc "

Set RSCAP = DB.OpenResultset(sqlCAP, rdOpenKeyset, rdConcurRowver)


I am able to run the exact query in Oracle and it does the job but not in vb6. Can someone please help?

 

[MatsHulten]

ADO (and I suspect, RDO to) does not support native functions for any generic DB (IE Oracle).
I suggest that you convert the query to a stored procedure and call that from VB instead.

Good Luck
-Mats

 

[campbere]

Thanks for the response. I thought about doing that, but was just frustrated that I couldn't use to_number. I kept examining what was going on and found the answer.

to_number only translates valid numbers. The example I ran in Oracle were all valid numbers, however looking at other prod_id there were ones that had - and letters in the item_num. That is why it was bombing.

I was told about the translate function and used it to change everything into valid numbers and then used the to number function.

At least this time the program ran and now I am in the process of verifying the results.

Thanks for the reply.

 

[elziko]

Oh, I knew all about un allocating memory in C but I always asumed VB did it all for you. Seems like I gotta go back and do a few adjustments! Roll On the FAQ!