PL SQL equivalent for mentioned VB code

User_FRTCM · Apr 3, 2014 2:35AM

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

Nimish Garg · Apr 3, 2014 2:38AM

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 · Apr 3, 2014 2:44AM

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 · Apr 3, 2014 2:48AM

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.

Etbin · Apr 3, 2014 2:56AM

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_CODES	THE_NEED
12345678	1234
__12345_	1234
__12_	12__
1	1___

Regards

Etbin

User_FRTCM · Apr 3, 2014 3:36AM

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 · Apr 3, 2014 3:50AM

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

User_FRTCM · Apr 3, 2014 4:34AM

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

Paul Horth · Apr 3, 2014 4:52AM

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 · Apr 3, 2014 4:56AM

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 · Apr 3, 2014 5:17AM

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

CODES	THE_CODE	WITH_NULLS
1,12,123,1234,12345,123456	1___	1___
1,12,123,1234,12345,123456	12__	12__
1,12,123,1234,12345,123456	123_	123_
1,12,123,1234,12345,123456	1234	1234
1,12,123,1234,12345,123456	1234	1234
1,12,123,1234,12345,123456	1234	1234
1,___,__123456,12	1___	1___
1,___,__123456,12	-	____
1,___,__123456,12	1234	1234
1,___,__123456,12	12__	12__
12345678	1234	1234
__123,,12_	123_	123_
__123,,12_	12__	12__
__123,,12_	-	____
__12345_,12,___123	1234	1234
__12345_,12,___123	12__	12__
__12345_,12,___123	123_	123_
-	-	____

Regards

Etbin

Forum Stats

Quick Links

Categories

PL SQL equivalent for mentioned VB code

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

PL SQL equivalent for mentioned VB code

Answers