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

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

Comments

  • SruthiASruthiA Vertica Employee Administrator

    Hi,

     

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

     

     

     

     

    Sruthi

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file