Oracle SQL

Greeting,

I have two txt files say txt1 and txt2.
col1 in txt1 contains a list of table names in Oracle and all those tables has Bldg field
col1 in txt2 contains a list of buidling.
I need an output shows the table names in col1(txt1) which has values IN col1(txt).

Please provide sample.

Thanks in advance.
mrongAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert SchuttSoftware EngineerCommented:
Some more info is needed on the structure of the input files. For now I have assumed "col1" is just the entire line, but I'm aware that this may very well be a wrong assumption.
Option Explicit

Const C_DATA_SOURCE = "XE"
Const C_DATA_USERNAME = "ROBERT"
Const C_DATA_PASSWORD = "XXXXXX"

Const C_FILE1 = "txt1"
Const C_FILE2 = "txt2"

Const C_FIELDNAME = "Bldg"


Dim oFSO, aList1, aList2, oConn, oRst, sTbl

Set oFSO = CreateObject("Scripting.FileSystemObject")
aList1 = GetListFromFile(C_FILE1) ' read list of tables
aList2 = GetListFromFile(C_FILE2) ' read list of buildings
Set oFSO = Nothing

Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=ORAOLEDB.ORACLE;Data Source=" & C_DATA_SOURCE & ";User ID=" & C_DATA_USERNAME & ";password=" & C_DATA_PASSWORD & ";"
oConn.Open

Set oRst = CreateObject("ADODB.RecordSet")
For Each sTbl In aList1
  If Trim(sTbl) <> "" Then 
    WScript.Echo GetResultsTable("SELECT * FROM " & sTbl & " WHERE " & C_FIELDNAME & " IN (" & SafeJoin(aList2, ",") & ")")
  End If
Next
Set oRst = Nothing

oConn.Close
Set oConn = Nothing


Function GetListFromFile(fn)
  Dim oFile
  Set oFile = oFSO.OpenTextFile(fn, 1, False)
  GetListFromFile = Split(oFile.ReadAll, vbCrLf)
  oFile.Close
  Set oFile = Nothing
End Function

Function GetResultsTable(sSQL)
  Dim oFld, blnFirst
  GetResultsTable = vbCrLf & vbCrLf & vbCrLf & "Results for query: " & sSQL & vbCrLf & vbCrLf
  oRst.Open sSQL, oConn
  blnFirst = True
  For Each oFld In oRst.Fields
    If blnFirst Then blnFirst = False Else GetResultsTable = GetResultsTable & vbTab
    GetResultsTable = GetResultsTable & oFld.Name
  Next
  GetResultsTable = GetResultsTable & vbCrLf
  While Not oRst.EOF
    blnFirst = True
    For Each oFld In oRst.Fields
      If blnFirst Then blnFirst = False Else GetResultsTable = GetResultsTable & vbTab
      GetResultsTable = GetResultsTable & oFld.Value
    Next
    GetResultsTable = GetResultsTable & vbCrLf
    oRst.MoveNext
  Wend
  oRst.Close
End Function

Function SafeJoin(a, d)
  Dim blnFirst, e
  blnFirst = True
  For Each e In a
    If Trim(e) <> "" Then
      If blnFirst Then blnFirst = False Else SafeJoin = SafeJoin & d
      If IsNumeric(e) Then ' not a good check, just for testing! much better to decide for yourself whether you need numbers or strings...
        SafeJoin = SafeJoin & e
      Else
        SafeJoin = SafeJoin & "'" & Replace(e, "'", "''") & "'"
      End If
    End If
  Next
End Function

Open in new window

0
mrongAuthor Commented:
txt1 & txt2 are already exported from Oracle database with the following sample data.
Could you pls simplify your code? It is a little hard for me to understand. Thanks.

txt1
-------
TBL1
TBL2
TBL3
xxxx

txt2
------
bldg1
bldg2
bldgx
0
Robert SchuttSoftware EngineerCommented:
I've taken out some code that's not needed now that I know the file structure and put in some more comments. Can you specify where if you need more help understanding what's going on? The objects/connection/files or the processing of the arrays and such?
Option Explicit

' enter your database SID, user and password here

Const C_DATA_SOURCE = "XE"
Const C_DATA_USERNAME = "ROBERT"
Const C_DATA_PASSWORD = "XXXXXX"

Const C_FILE1 = "txt1"
Const C_FILE2 = "txt2"

Const C_FIELDNAME = "Bldg"


Dim oFSO, aList1, aList2, oConn, oRst, sTbl

' read the 2 files into arrays in memory

Set oFSO = CreateObject("Scripting.FileSystemObject")
aList1 = GetListFromFile(C_FILE1) ' read list of tables
aList2 = GetListFromFile(C_FILE2) ' read list of buildings
Set oFSO = Nothing

' connect to the database

Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=ORAOLEDB.ORACLE;Data Source=" & C_DATA_SOURCE & ";User ID=" & C_DATA_USERNAME & ";password=" & C_DATA_PASSWORD & ";"
oConn.Open

' Get the results...

Set oRst = CreateObject("ADODB.RecordSet")

' ... for each table in the list

For Each sTbl In aList1
  If Trim(sTbl) <> "" Then ' ignore empty lines

    ' build SQL string, for example: SELECT * FROM TBL1 WHERE Bldg IN (1,2,3)

    WScript.Echo GetResultsTable("SELECT * FROM " & sTbl & " WHERE " & C_FIELDNAME & " IN (" & SafeJoin(aList2, ",") & ")")

  End If
Next

' close and clean up

Set oRst = Nothing

oConn.Close
Set oConn = Nothing


' read list of text values from file, skipping the first two lines because format is:
'
' txt1
' -------
' TBL1
' TBL2
' TBL3

Function GetListFromFile(fn)
  Dim oFile
  Set oFile = oFSO.OpenTextFile(fn, 1, False)
  oFile.ReadLine
  oFile.ReadLine
  ' the next line returns an array containing all values from the file
  GetListFromFile = Split(oFile.ReadAll, vbCrLf)
  oFile.Close
  Set oFile = Nothing
End Function

Function GetResultsTable(sSQL)
  Dim oFld, blnFirst

  ' start results with a heading line

  GetResultsTable = vbCrLf & vbCrLf & vbCrLf & "Results for query: " & sSQL & vbCrLf & vbCrLf

  ' the actual query to the database!

  oRst.Open sSQL, oConn

  ' show a header line
  blnFirst = True
  For Each oFld In oRst.Fields
    If blnFirst Then blnFirst = False Else GetResultsTable = GetResultsTable & vbTab
    GetResultsTable = GetResultsTable & oFld.Name
  Next
  GetResultsTable = GetResultsTable & vbCrLf

  ' show each record's values

  While Not oRst.EOF
    blnFirst = True
    For Each oFld In oRst.Fields
      If blnFirst Then blnFirst = False Else GetResultsTable = GetResultsTable & vbTab
      GetResultsTable = GetResultsTable & oFld.Value
    Next
    GetResultsTable = GetResultsTable & vbCrLf
    oRst.MoveNext
  Wend

  ' close recordset, but don't clean up here, will be re-used for every table in the list for efficiency
  oRst.Close
End Function


' replacement function for Join() because we need to skip empty values to avoid SQL errors

Function SafeJoin(a, d)
  Dim blnFirst, e
  blnFirst = True
  For Each e In a
    If Trim(e) <> "" Then
      If blnFirst Then blnFirst = False Else SafeJoin = SafeJoin & d
      If IsNumeric(e) Then ' not a good check, just for testing! much better to decide for yourself whether you need numbers or strings...
        SafeJoin = SafeJoin & e
      Else
        SafeJoin = SafeJoin & "'" & Replace(e, "'", "''") & "'"
      End If
    End If
  Next
End Function

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mrongAuthor Commented:
robert_schutt,

Thank you for your code. Where does your code put the output file? Can you put it in a select.sql file?

Thanks.
0
mrongAuthor Commented:
I just need to pass the values in from txt1 and txt2 and generate select.sql.

Thanks.
0
Robert SchuttSoftware EngineerCommented:
The script writes on "standard output", redirection can be used in a command prompt like this:
C:\Temp> cscript //nologo tst.vbs >select.sql

assuming you have saved the code in a file "tst.vbs" like me.

Note that the output now contains text, sql statements and output, I'm getting the feeling that you just want the sql statements?
0
Robert SchuttSoftware EngineerCommented:
If that's the case then it all does indeed become a lot simpler:
Option Explicit

Const C_FILE1 = "txt1"
Const C_FILE2 = "txt2"

Const C_OUTPUT = "select.sql"

Const C_FIELDNAME = "Bldg"


Dim oFSO, aList1, aList2, sTbl, oFileOut

' read the 2 files into arrays in memory

Set oFSO = CreateObject("Scripting.FileSystemObject")
aList1 = GetListFromFile(C_FILE1) ' read list of tables
aList2 = GetListFromFile(C_FILE2) ' read list of buildings

Set oFileOut = oFSO.OpenTextFile(C_OUTPUT, 2, True)

For Each sTbl In aList1
  If Trim(sTbl) <> "" Then ' ignore empty lines

    ' build SQL string, for example: SELECT * FROM TBL1 WHERE Bldg IN (1,2,3)

    oFileOut.WriteLine "SELECT * FROM " & sTbl & " WHERE " & C_FIELDNAME & " IN (" & SafeJoin(aList2, ",") & ")"

  End If
Next

' close and clean up

oFileOut.Close
Set oFileOut = Nothing

Set oFSO = Nothing


' read list of text values from file, skipping the first two lines because format is:
'
' txt1
' -------
' TBL1
' TBL2
' TBL3

Function GetListFromFile(fn)
  Dim oFile
  Set oFile = oFSO.OpenTextFile(fn, 1, False)
  oFile.ReadLine
  oFile.ReadLine
  ' the next line returns an array containing all values from the file
  GetListFromFile = Split(oFile.ReadAll, vbCrLf)
  oFile.Close
  Set oFile = Nothing
End Function


' replacement function for Join() because we need to skip empty values to avoid SQL errors

Function SafeJoin(a, d)
  Dim blnFirst, e
  blnFirst = True
  For Each e In a
    If Trim(e) <> "" Then
      If blnFirst Then blnFirst = False Else SafeJoin = SafeJoin & d
      If IsNumeric(e) Then ' not a good check, just for testing! much better to decide for yourself whether you need numbers or strings...
        SafeJoin = SafeJoin & e
      Else
        SafeJoin = SafeJoin & "'" & Replace(e, "'", "''") & "'"
      End If
    End If
  Next
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrongAuthor Commented:
Thanks robert_schutt!
0
mrongAuthor Commented:
robert_schutt,

How to add single quote to the bldg like the following?
SELECT * FROM TBL1 WHERE Bldg IN ('1','2','3')
0
Robert SchuttSoftware EngineerCommented:
Replace line 67 thru 71 with:
      'If IsNumeric(e) Then ' not a good check, just for testing! much better to decide for yourself whether you need numbers or strings...
        'SafeJoin = SafeJoin & e
      'Else
        SafeJoin = SafeJoin & "'" & Replace(e, "'", "''") & "'"
      'End If

Open in new window

The part for numeric input is now commented out and fields are all treated as strings.
0
mrongAuthor Commented:
robert_schutt,
Thanks and that adds quote to the bldg.

Also, when I look at the output file I realized it skipped the top two rows of data in both txt1 & txt2. So it actually start the 3rd row in bot txt1 & txt2.

Sorry, I should check it before closed this question.

Thanks.
0
Robert SchuttSoftware EngineerCommented:
it skipped the top two rows of data in both txt1 & txt2
Yes, that was on purpose because from your description I concluded that the first 2 lines are the header (and dashed line) that is not real data.

But if you need all lines just remove (or comment out) the extra ReadLine calls on lines 50 and 51.
0
mrongAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.