1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Convert stored procedure from oracle to mysql

Discussion in 'General' started by KristyStevens, Jun 22, 2019.

  1. KristyStevens

    KristyStevens Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Minesota
    Hi All,

    I want immediate help to convert below stored procedure from oracle to mysql migration project. please help me out.

    mostly the issue with how to achieve start with .... connect by and start with ...connect by prior.

    Code (SQL):
    delimiter //
    CREATE PROCEDURE SP_Recon_Limit(IN p_le_short_name VARCHAR(4000),
    IN p_br_short_name VARCHAR(4000)
    )
    BEGIN
    DECLARE v_txn_exists INTEGER DEFAULT 1;
    DECLARE v_parent_facility INTEGER;
    DECLARE v_facility_nt facility_nt;
    DECLARE CONNECT BY
    DECLARE c_facility cursor FOR
    SELECT DISTINCT a.ADV_FACI_ID,
    a.CURRENCY_ID,
    a.OBLIGOR_ID,
    a.ADV_FACI_AMOUNT,
    a.ADV_FACI_NAME,
    CASE (SELECT 1 FROM m_obligor WHERE obligor_id = a.obligor_id AND reference_id LIKE 'H%') WHEN 1 THEN 'Y' ELSE 'N' END AS IS_DUMMY,
    (CASE WHEN EXISTS(SELECT 1 FROM t_advance_facility WHERE parent_advf_id = a.ADV_FACI_ID) THEN 'Y' ELSE 'N' END) AS IS_MULTIENTITY
    FROM t_advance_facility a
    WHERE a.valid_flag = 'T'
    AND a.parent_advf_id IS NULL
    AND a.LEGAL_ENTITY_BRANCH_ID IN (SELECT sk_legal_entity_branch_id FROM r_legal_entity_branch
    WHERE LEGAL_ENTITY_ID = (SELECT sk_legal_entity_id FROM m_legal_entity WHERE legal_entity_shortname = p_le_short_name)
    AND BRANCH_ID = (SELECT branch_id FROM m_branch WHERE branch_shortname = p_br_short_name))
    AND (a.ADV_FACI_STATUS IN (6,21,16,26,27,30,43)
    OR (a.ADV_FACI_STATUS IN (9, 8, 11, 7, 24, 25) AND a.ADV_FACI_AMOUNT > 0)) prior
    a.adv_faci_id = a.parent_advf_id;  
    cursor c_sub_facility(f_id IN NUMBER) IS
    SELECT ADV_FACI_ID,
    CURRENCY_ID,
    OBLIGOR_ID,
    ADV_FACI_AMOUNT,
    ADV_FACI_NAME
    FROM t_advance_facility
    WHERE parent_advf_id = f_id;  
    BEGIN
    DELETE FROM dbs_recon_limit;
    DECLARE v_facility cursor FOR c_facility LOOP
    BEGIN
    SAVEPOINT start_transaction;
    SELECT ADV_FACI_ID bulk collect INTO v_facility_nt FROM t_advance_facility
    START WITH ADV_FACI_ID = v_facility.ADV_FACI_ID
    CONNECT BY prior adv_faci_id = parent_advf_id;
    END SP_Recon_Limit;
    //
    delimiter;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,684
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We are not paid support technicians we are a volunteer group who gets to issues as we can. Dedmanding immediage assistsance is NOT the way to get help here. If and whensomeone who CAN help has time assistance will be provided.