Forum Stats

  • 3,781,586 Users
  • 2,254,530 Discussions
  • 7,879,762 Comments

Discussions

PL/SQL Equivalent for the below piece of VB Code

User_FRTCM
User_FRTCM Member Posts: 116 Blue Ribbon
edited Apr 29, 2014 10:09AM in SQL & PL/SQL

Hi,

Please help me in finding the PL SQL equivalent for below VB code :-

IIf(InStr(1, "1234567890", Mid(rSt1(1), 1, 1)) > 0, Mid(rSt1(1), 1, 7), Mid(rSt1(1), 1, 8)))

My Attempt:-

DECODE((INSTR(1, 1234567890, SUBSTR(SPSN_ECMCODE10_REC1.ECM_SC_OPTION, 1, 1)) > 0,

SUBSTR(SPSN_ECMCODE10_REC1.ECM_SC_OPTION, 1, 7),

SUBSTR(SPSN_ECMCODE10_REC1.ECM_SC_OPTION, 1, 8))

Thanks

Mrinal

User_FRTCM

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    If don't know VB. So if you can explain in plain English what you are trying to accomplish, I can help you with a suitable PL/SQL solution.

  • GregV
    GregV Member Posts: 3,075 Gold Crown

    Hi,

    DECODE cannot be used in a PL/SQL instruction (only in SQL DML statements).

    you can write something like:

    IF LTRIM(ECM_SC_OPTION, '0123456789') <> ECM_SC_OPTION
    THEN
        v_str := SUBSTR(ECM_SC_OPTION, 1, 7);
    ELSE
        v_str := SUBSTR(ECM_SC_OPTION, 1, 8));
    END IF;
    
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown

    Maybe (heard of VB only)

    case when instr('1234567890',substr(something,1,1)) > 0

         then substr(something,1,7)

         else substr(something,1,8)

    end


    Regards

    Etbin

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon

    I have to use the above for following statement:-

    SQL_STMT:='INSERT INTO '||L_V_TABLE||'(ECMPARTNUMBER, ECMCODEBASE, ECMCODEREV, ENGMONSYSTEM) VALUES (:1,:2,:3,:4)';

    EXECUTE IMMEDIATE SQL_STMT USING PARTNO,SPSN_ECMCODE10_REC1.ECMCODE17,SPSN_ECMCODE10_REC1.ECMCODE92,

    DECODE((INSTR(1, 1234567890, SUBSTR(SPSN_ECMCODE10_REC1.ECM_SC_OPTION, 1, 1)) > 0, SPSN_ECMCODE10_REC1.ECM_SC_OPTION, SPSN_ECMCODE10_REC1.ECM_SC_OPTION);

    So I cannot write it in 2-3 steps, it has to be a calculated value either here directly or after being passed to a parameter.

  • GregV
    GregV Member Posts: 3,075 Gold Crown

    You should have given this detail in your original post itself. Try something as:

    SQL_STMT:='INSERT INTO '||L_V_TABLE||'(ECMPARTNUMBER, ECMCODEBASE, ECMCODEREV, ENGMONSYSTEM) VALUES (:1,:2,:3,:4)';

    EXECUTE IMMEDIATE SQL_STMT USING PARTNO,SPSN_ECMCODE10_REC1.ECMCODE17,SPSN_ECMCODE10_REC1.ECMCODE92,

    CASE WHEN LTRIM(SPSN_ECMCODE10_REC1.ECM_SC_OPTION, '0123456789') <> SPSN_ECMCODE10_REC1.ECM_SC_OPTION

         THEN SUBSTR(SPSN_ECMCODE10_REC1.ECM_SC_OPTION, 1, 7)

         ELSE SUBSTR(SPSN_ECMCODE10_REC1.ECM_SC_OPTION, 1, 8)

    END    

    );

  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Apr 29, 2014 9:12AM

    The semantic is:

    if the first character of rSt1(1) is in 0-9 then return the first seven characters of rSt1(1) else the eight first characters.


    Therefore:


    select
    case when regexp_like ('01234', '^\d') then
    substr('01234', 1, 4)
    else
    substr('01234', 1, 5)
    end r
    from dual
    
    
    

    Message was edited by: chris227 Had to correct the returns

  • EdStevens
    EdStevens Member Posts: 28,604 Gold Crown

    You really should not put your email address in a public forum.  Even now every web crawler on the planet is harvesting your email address.  You exepect to soon see your inbox flooded with offers for products for uh, enhancement of certain anatomical parts, and requests to help launder money out of Nigeria.

    User_FRTCM
  • kendenny
    kendenny Member Posts: 1,269

    This will never work

    INSTR(1, 1234567890, ...
    That says to find the string '1234567890' inside the string '1' which will never happen and will always be 0.

    I'm not sure what you were trying to do with INSTR but that's not the way to do it.

  • padders
    padders Member Posts: 1,060 Silver Trophy

    Check parameters of INSTR in PL/SQL they are not the same as VB (e.g. first parameter in PL/SQL is not 'start').

    User_FRTCM
This discussion has been closed.