This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,967 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

PL SQL equivalent for mentioned VB code

User_FRTCM
User_FRTCM Member Posts: 116 Blue Ribbon
edited Apr 3, 2014 5:56AM in SQL & PL/SQL

Hi,

I have converted a part of the below code from VB to SQL logic, could I get some more help on the rest. Could I please get the conversion for below :-

If InStr(str1, ",") = 0 Then

                k = Len(LTrim(RTrim(str1)))

                If (k < 4) Then

                ' space for other chars

                    ln = "INSERT INTO ATION (ATION,CODES) VALUES('" & rSt1(1) & "', '" & Left(str1, 4)  & Space(4 - k) & ",    ,    ,    ,')"

                Else

                ' take only 4chars

                    ln = "INSERT INTO ATION (ATION,CODES) VALUES('" & rSt1(1) & "', '" & Left(str1, 4) & ",    ,    ,    ,')"

                End If

Converted part :-

If InStr(V_CODES, ',') = 0 Then

                k := LENGTH(LTrim(RTrim(V_CODES)));

                If (k < 4) Then

                  --space for other chars

                  INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, SUBSTR(V_CODES, 4) || RPAD(4 - k) & ",    ,    ,    ,')

                Else

                ' take only 4chars

                 INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, 'Left(str1, 4)  & ",    ,    ,    ,')"

                End If

Thanks, I just want to be sure about the code. Help & advice appreciated

Manik
«1

Answers

  • Nimish Garg
    Nimish Garg Sr. Database Developer @ Gartner Noida, IndiaMember Posts: 3,185 Gold Trophy
    edited Apr 3, 2014 2:39AM

    Try [not tested]

    >> INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, SUBSTR(V_CODES, 4) || RPAD(4 - k) & ",    ,    ,    ,')

    to

    INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, SUBSTR(V_CODES, 4) || RPAD(4 - k) || ',    ,    ,    ,')


    >> INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, Left(str1, 4)  & ",    ,    ,    ,')

    to

    INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, RPAD(str1, 4)  || ',    ,    ,    ,')


  • SKP
    SKP BangaloreMember Posts: 844 Gold Badge
    edited Apr 3, 2014 2:53AM

    LENGTH(LTrim(RTrim(V_CODES)));

    Replace with

    LENGTH(Trim(V_CODES));

    --------------

    ' take only 4chars

                     INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, 'Left(str1, 4)  & ",    ,    ,    ,')"

    Replace with

    INSERT INTO ATION (ATION,CODES) VALUES(V_ATION, 'substr(rpad((str1,4,', '),1,4))"

  • Karthick2003
    Karthick2003 IndiaMember Posts: 13,711 Bronze Badge

    Its not a very good idea to do conversion code-by-code basis. VB and PL/SQL are two very different programming language. And they both handle SQL very differently. I would suggest you tell us what you want to implement in your code in plain English and we could help you write PL/SQL code for that.

    Manik
  • Etbin
    Etbin SloveniaMember Posts: 8,968 Gold Crown

    Maybe (underline used instead of space)

    with

    test_values as

    (select '12345678' v_codes from dual union all

    select '__12345_' from  dual union all

    select '__12_' from  dual union all

    select '1' from  dual

    )

    select v_codes,

           rpad(substr(trim(both '_' from v_codes),1,4),4,'_') the_need

      from test_values

    V_CODESTHE_NEED
    123456781234
    __12345_1234
    __12_12__
    11___

    Regards

    Etbin

    Etbin
  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon

    Here is the entire logic, now help me convert this into PL SQL.

    Set rSt1 = locdbs.OpenRecordset("SELECT ltrim(rtrim(CODES)),ATION FROM T_A_L", DB_OPEN_DYNASET) -- Can be dumped into a cursor
        Debug.Print "AN" -- DBMS OUTPUT
        Do Until rSt1.EOF -- WHILE CURSOR%FOUND LOOP
            tmpstr = ""
            count = 1
            str1 = rSt1(0) -- FETCH OUTPUT OF CURSOR INTO A VARIABLE
            If InStr(str1, ",") = 0 Then
                    k = Len(LTrim(RTrim(str1)))
                    If (k < 4) Then
                    ' space for other chars
                        ln = "INSERT INTO ATION (ATION,CODES) VALUES('" & rSt1(1) & "', '" & Left(str1, 4) _
                        & Space(4 - k) & ",    ,    ,    ,')"
                    Else
                    ' take only 4chars
                        ln = "INSERT INTO ATION (ATION,CODES) VALUES('" & rSt1(1) & "', '" & Left(str1, 4) _
                        & ",    ,    ,    ,')"
                    End If
                   
                    Accessdb.Execute ln
           
            'If "'" is present in the value then do the manipulation below
           
            Else
            ' take the chars four by four and append into tmpstr
                Do Until InStr(str1, ",") = 0
                    str2 = LTrim(RTrim(Left(Mid(str1, 1, (InStr(str1, ",") - 1)), 4)))
                    If Len(tmpstr) = 0 Then
                      If (Len(str2) = 3) Then
                            tmpstr = str2 & " "
                      ElseIf (Len(str2) = 2) Then
                            tmpstr = str2 & "  "
                      Else
                        tmpstr = str2
                      End If
                    Else
                        If (Len(str2) = 3) Then
                            tmpstr = tmpstr & "," & str2 & " "
                        ElseIf (Len(str2) = 2) Then
                            tmpstr = tmpstr & "," & str2 & "  "
                        Else
                            tmpstr = tmpstr & "," & str2
                        End If
                    End If
                    str3 = Mid(str1, (InStr(str1, ",") + 1), (Len(str1) - InStr(str1, ",")))
                    str1 = str3
                    count = count + 1
                    If InStr(str1, ",") = 0 Then
                        If (Len(LTrim(RTrim(str1))) = 2) Then
                            str3 = LTrim(RTrim(str1)) & "  "
                        ElseIf (Len(LTrim(RTrim(str1))) = 3) Then
                            str3 = LTrim(RTrim(str1)) & " "
                        Else
                            str3 = str1
                        End If
                        str1 = str3
                        tmpstr = tmpstr & "," & Left(str1, 4) & ","
                        If count = 2 Then
                            tmpstr = tmpstr & "    ,    ,"
                        ElseIf count = 3 Then
                            tmpstr = tmpstr & "    ,"
                        End If
                       
                        ln = "INSERT INTO ATION(ATION,CODES) VALUES('" _
                            & rSt1(1) & "', '" & tmpstr & "')"
                        Accessdb.Execute ln
                    End If
                Loop
            End If
           
            rSt1.MoveNext
        Loop


    apquery = "SELECT DISTINCT (ATION) FROM ATION"

    Set ATION = Accessdb.OpenRecordset(apquery, DB_OPEN_DYNASET)

    Do Until ATION.EOF
           

            If (StrComp(prdid, "CAE") = 0) Then
                apnotes = "SELECT NOT_F_NAME, NOT_NOTE_VALUE FROM T_N WHERE " _
                & " NOT_I_NUMBER LIKE '" & ATION(0) & "%'" & " AND NOT_PRD_ID = 'ENI'"
            ElseIf (StrComp(prdid, "CAS") = 0) Then
                apnotes = "SELECT NOT_F_NAME, NOT_NOTE_VALUE FROM T_N WHERE " _
                & " NOT_I_NUMBER LIKE '" & ATION(0) & "%'" & " AND NOT_PRD_ID = 'P1I'"
            ElseIf (StrComp(prdid, "CAP") = 0) Then
                apnotes = "SELECT NOT_F_NAME, NOT_NOTE_VALUE FROM T_N WHERE " _
                & " NOT_I_NUMBER LIKE '" & ATION(0) & "%'" & " AND NOT_PRD_ID = 'P2I'"
            Else
                apnotes = "SELECT NOT_F_NAME, NOT_NOTE_VALUE FROM T_N WHERE " _
                & " NOT_I_NUMBER LIKE '" & ATION(0) & "%'" & " AND NOT_PRD_ID = '" & prdid & "'"
            End If
           
            Set apnotevalues = OracleConn.OpenRecordset(apnotes, DB_OPEN_DYNASET)
          
            Do Until apnotevalues.EOF
                If (IsNull(apnotevalues(0)) = False And Mid(apnotevalues(0), 1, 1) <> " ") Then
                    If InStr(apnotevalues(0), "CODES") = 0 Then
                   
                    la = ""

                        If InStr(apnotevalues(0), "COMMENT") = 0 Then
                            la = "UPDATE ATION SET " & LTrim(RTrim(apnotevalues(0))) & " = '" & LTrim(RTrim(apnotevalues(1))) & "' " _
                                & " WHERE ATION = '" & Mid(ATION(0), 1, 7) & "'"
                        Else
                            la = "UPDATE ATION SET " & LTrim(RTrim(apnotevalues(0))) & " = '" & LTrim(RTrim(apnotevalues(1))) & "' " _
                                & " WHERE ATION = '" & Mid(ATION(0), 1, 7) & "'"
                        End If

                    Accessdb.Execute la

                   End If
                End If
            apnotevalues.MoveNext
            Loop
            apnotevalues.Close
    ATION.MoveNext
    Loop
    ATION.Close

  • Karthick2003
    Karthick2003 IndiaMember Posts: 13,711 Bronze Badge

    This is VB code. First step would be to Reverse engineer it and transform it into a Design document.

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon


    This is not so intense Karthick, can you help me in converting the major functional parts of the above code.

  • Paul  Horth
    Paul Horth London, UKMember Posts: 3,402 Gold Trophy
    99ab9d4f-b078-43b9-8b7d-63f49b16f665 wrote:
    
    
    This is not so intense Karthick, can you help me in converting the major functional parts of the above code.

    Karthick is right - don't just convert. I suspect you have more than just this VB code to convert.

    You will end up with an Oracle system running VB - an epic fail project.

    Find out the business requirements and get someone who knows Oracle to write correct, efficient code for Oracle.

  • Karthick2003
    Karthick2003 IndiaMember Posts: 13,711 Bronze Badge
    99ab9d4f-b078-43b9-8b7d-63f49b16f665 wrote:
    
    
    This is not so intense Karthick, can you help me in converting the major functional parts of the above code.

    Your entire code can be converted into single INSERT INTO .. SELECT statement. But there is lot of string manipulations are being done. Explain in plain English what its all about. To infer it myself from your VB code that too after having a good lunch, sorry cant do it

  • Etbin
    Etbin SloveniaMember Posts: 8,968 Gold Crown

    Not knowing VB I can only guess

    with

    t_a_l as

    (select '12345678' codes from dual union all

    select '__12345_,12,___123' from  dual union all

    select '__123,,12_' from  dual union all

    select '1,___,__123456,12' from dual union all

    select '' from dual union all

    select '1,12,123,1234,12345,123456' from dual

    )

    select codes,

           rpad(substr(trim(both '_' from regexp_substr(codes,'[^,]+',1,level)),1,4),4,'_') the_code,

           rpad(nvl(substr(trim(both '_' from regexp_substr(codes,'[^,]+',1,level)),1,4),'_'),4,'_') with_nulls

      from t_a_l

    connect by level <= regexp_count(codes,',') + 1

           and prior codes = codes

           and prior sys_guid() is not null

    CODESTHE_CODEWITH_NULLS
    1,12,123,1234,12345,1234561___1___
    1,12,123,1234,12345,12345612__12__
    1,12,123,1234,12345,123456123_123_
    1,12,123,1234,12345,12345612341234
    1,12,123,1234,12345,12345612341234
    1,12,123,1234,12345,12345612341234
    1,___,__123456,121___1___
    1,___,__123456,12-____
    1,___,__123456,1212341234
    1,___,__123456,1212__12__
    1234567812341234
    __123,,12_123_123_
    __123,,12_12__12__
    __123,,12_-____
    __12345_,12,___12312341234
    __12345_,12,___12312__12__
    __12345_,12,___123123_123_
    --____

    Regards

    Etbin

This discussion has been closed.