Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to compare varchar with number

599546Nov 28 2007 — edited Jun 24 2010
i am doing one report for that am passing receipt number(from , to) as parameters
if i compare for that table i am getting output.

select receipt_num from rcv_shipment_headers rsh
where to_number(rsh.receipt_num) between nvl(:P_RECEIPT_FROM,rsh.receipt_num) and nvl(:P_RECEIPT_TO,rsh.receipt_num)

but when i am using in below query it is getting error "Invalid Number"

select rsh.receipt_num
, sum(rs.quantity)
, rs.unit_of_measure
, rct.transaction_date "Date of Entry"
, pov.vendor_name
, msi.segment1 "Item Code"
, msi.description "Item Description"
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_vendors pov
, mtl_system_items_b msi
, rcv_supply rs
where rsh.shipment_header_id=rct.shipment_header_id
and rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_line_id=rct.shipment_line_id
and rsh.vendor_id=pov.vendor_id
and rsl.item_id=msi.inventory_item_id
and msi.organization_id=106
and rsl.shipment_line_id=rct.shipment_line_id
and rct.transaction_id=rs.rcv_transaction_id
and to_number(rsh.receipt_num) between nvl(:P_RECEIPT_FROM,rsh.receipt_num) and nvl(:P_RECEIPT_TO,rsh.receipt_num)
and rct.organization_id=nvl(:P_ORG_ID,rct.organization_id)
and rsh.vendor_id=nvl(:P_Vendor_Name,rsh.vendor_id)
and rct.inspection_status_code='NOT INSPECTED'
group by rsh.receipt_num
, rs.unit_of_measure
, rct.transaction_date
, pov.vendor_name
, msi.segment1
, msi.description

plese help to solve

Thanks & Regards
Adina
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2010
Added on Nov 28 2007
16 comments
7,351 views