to_number

[campbere]

I have an sql statement that I have run in Oracle but has problems in visual basic. I have narrowed the problem down to the to_number function. Anyone have any idea why I can't use this in VB? Is there something else I can use?

The reason I am using it is because my column item_num is a Varchar2, when I do an order by on it, it doesn't always sort correctly. I have had a 9 become before an 11. So I used to_number(item_num) in Oracle and it corrected the problem. However in VB it doesnt do anything.

Here is the sql. I am using rdo

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 "

 

[RayMan]

What does the sql statement return in VB? Can I see your
openresult statement and how you reference the variable
in your code?
to_number converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.

Example 1
UPDATE emp SET sal = sal +
TO_NUMBER('100.00', '9G999D99')
WHERE ename = 'BLAKE';

RayMan

 

[campbere]

There is nothing being returned by the visual basic statement when I have the to_number in the order by statement. If I remove it then I get my results.

This is the OpenResultset statement:

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

After the results are loaded into the RSCAP resultset,

I then evaluate row by row for a prod_id and run several tests against it.

Is this enough information? I am just baffled why I can run the query in Oracle 8, but when I run it in VB it blows up.

Is there maybe a better way of addressing by order by problem? Both prod_num and item_num are varchar2. I just thought converting them to a number data type would fix the order by problem, and it does in Oracle.

 

[RayMan]

you might try
sqlCAP = "select prod_id, to_number(prod_num)," _
& "to_number(item_num),release_date, prod_status " _
& "from PRODUCTS " _
& "where prod_id = '" & RSprod_id!Issn & "' " _
& "and current_prod = 'Y' " _
& "order by 2 desc, " _
& "3 desc "

Might fix it, might not!

RayMan

 

[campbere]

Still no luck. Thanks for the help though.