Results 1 to 2 of 2

Thread: In Operator

  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: In Operator

    Hi All,

    I have a query contains a field transaction_id and datatype for this field is number.

    in where condition of below query i have like operator .
    In this case if i give transaction_id like '00412552' i am not getting data.

    Suppose if i give transaction_id like 00412552 then i am getting data.

    i think it is not taking string type for in operator.
    Please suggest what is the problem.

    SELECT DISTINCT crt.carton_no,
    crt.shipment_ref_no,
    crt.put_away_location,
    crt.item_category,
    crt.total_quantity
    FROM carton crt
    RIGHT OUTER JOIN pick_list pl ON(crt.carton_no=pl.carton_no AND crt.shipment_ref_no =pl.shipment_ref_no)
    WHERE -- pl.transacton_id is null or pl.transaction_id=0 and
    -- crt.status like 'P' and
    pl.transaction_id like '00412552'

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can't store a numeric field with leading zeros
    you can only use leading zero's when you format a numeric column appropriately

    also you cannot use like with numeric datatype columns

    so either convert the datatype to text/string AND pad data with leadign zeros
    OR
    use
    Code:
    pl.transaction_id = 412552
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •