User-defined Scalar Function AESDecrypt produced fewer output rows (0) than input rows (505)

Arunesh

Hi Team,

 

when i m trying to run a sql whch have a join of table and view , i got the following below error 

 

User-defined Scalar Function AESDecrypt produced fewer output rows (0) than input rows (505)

 Post analysis i observed something , if i run sql like mentioned in below screen shot(error query) it throws an error but if i do replace where with a join  query(working query) it  works .Please assist me so that i can avoid the same condition in near future.

 

error query :-

 JOIN vw_vendor_contact vc
    ON LOWER(vc.vendor_code) = LOWER(dpv.code)
  JOIN vw_seller_pii_account a
    ON LOWER(a.seller_code) = dpv.code
WHERE fso.subo_date_verified BETWEEN to_char(TIMESTAMPADD(DAY,-7,CURRENT_TIMESTAMP), 'YYYYMMDD000000')
    AND to_char(TIMESTAMPADD(DAY,-1,CURRENT_TIMESTAMP), 'YYYYMMDD235959')
    AND fso.primary_suborder_id = 0
    AND fso.suborder_status_code <> 'TBR'
    AND vc.contact_type = 4
  and vc.mobile not in ('9888888888','9111111111','8688888888','9999999990','9999999900','9999999998','9499999999','8888888889','8111111111','9999999991','7222222222','8999999999','9899999999','9111111118','9999999994','9777777777','9255555555','8888888887','8888888899','9888888899','8888888880','8909999999','9822222222','7899999999','9111111116','7888788888','9999999099','9677777777','9999999995','8896666666','8876666666','9990000000')

 

Working query

 

FROM analytics_finance.sn_del_ven1 s,dwh.f_suborders_oms fso

  JOIN dwh.d_product_vendor dpv

    ON dpv.vendor_sid = fso.vendor_sid

  JOIN vw_vendor_contact vc

    ON LOWER(vc.vendor_code) = LOWER(dpv.code)

  JOIN vw_seller_pii_account a

    ON LOWER(a.seller_code) = dpv.code

     join (select distinct mobile from vw_vendor_contact where mobile not in ('9888888888','9111111111','8688888888','9999999990','9999999900','9999999998','9499999999','8888888889','8111111111','9999999991','7222222222','8999999999','9899999999','9111111118','9999999994','9777777777','9255555555','8888888887','8888888899','9888888899','8888888880','8909999999','9822222222','7899999999','9111111116','7888788888','9999999099','9677777777','9999999995','8896666666','8876666666','9990000000')

) x on x.mobile=vc.mobile

WHERE fso.subo_date_verified BETWEEN to_char(TIMESTAMPADD(DAY,-7,CURRENT_TIMESTAMP), 'YYYYMMDD000000')

    AND to_char(TIMESTAMPADD(DAY,-1,CURRENT_TIMESTAMP), 'YYYYMMDD235959')

    AND fso.primary_suborder_id = 0

SruthiA

Hi,

 

   R u sure that key used for encryption and decryption is the same?

 

 

 

 

Sruthi