Transfer a Text File into an Oracle Database using an Excel Macro

December 2, 2009

The following Excel macro shows how to open a text file as a database using ADO, create a table in Oracle, and then transfer the rows from the text file into the database table using bind variables.  Once the transfer finishes, a worksheet in Excel is populated with the data from the Oracle table.

Private Sub TransferRows()
    'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting
    Dim i As Integer
    Dim intResult As Integer
    Dim strSQL As String
    Dim strTable As String
    Dim strDBTable As String
    Dim snpData As ADODB.Recordset
    Dim comDataInsert As ADODB.Command

    On Error Resume Next

    strTable = Sheets("ExcelQueryofTextFile").Cells(1, 2).Value
    strTable = Right(strTable, Len(strTable) - InStrRev(strTable, "\"))
    'Set the database table name to the file name without the extension
    strDBTable = UCase(Left(strTable, InStr(strTable, ".") - 1))

    Sheets("ExcelQueryofTextFile").Range("A5:M10006").Delete Shift:=xlUp

    Set snpData = New ADODB.Recordset

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  [Source IP Address]," & vbCrLf
    strSQL = strSQL & "  [Destination IP Address]," & vbCrLf
    strSQL = strSQL & "  [Time]," & vbCrLf
    strSQL = strSQL & "  [Source Port]," & vbCrLf
    strSQL = strSQL & "  [Destination Port]," & vbCrLf
    strSQL = strSQL & "  [L3 Protocol]," & vbCrLf
    strSQL = strSQL & "  [Application Path]," & vbCrLf
    strSQL = strSQL & "  [Application Description]," & vbCrLf
    strSQL = strSQL & "  [Rule Description]" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  [" & strTable & "]" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  [Source IP Address]," & vbCrLf
    strSQL = strSQL & "  [Time]"
    snpData.Open strSQL, dbFile

    If snpData.State = 1 Then
        strSQL = "CREATE TABLE " & strDBTable & "(" & vbCrLf
        strSQL = strSQL & "  SOURCE_IP VARCHAR2(16)," & vbCrLf
        strSQL = strSQL & "  DESTINATION_IP VARCHAR2(16)," & vbCrLf
        strSQL = strSQL & "  ACCESS_TIME DATE," & vbCrLf
        strSQL = strSQL & "  SOURCE_PORT NUMBER(12)," & vbCrLf
        strSQL = strSQL & "  DESTINATION_PORT NUMBER(12)," & vbCrLf
        strSQL = strSQL & "  PROTOCOL VARCHAR2(15)," & vbCrLf
        strSQL = strSQL & "  APPLICATION_PATH VARCHAR2(100)," & vbCrLf
        strSQL = strSQL & "  APPLICATION_DESCRIPTION VARCHAR2(100)," & vbCrLf
        strSQL = strSQL & "  RULE_DESCRIPTION VARCHAR2(30))" & vbCrLf
        dbVMFG.Execute strSQL

        Set comDataInsert = New ADODB.Command
        With comDataInsert
            strSQL = "INSERT INTO " & strDBTable & "(" & vbCrLf
            strSQL = strSQL & "  SOURCE_IP," & vbCrLf
            strSQL = strSQL & "  DESTINATION_IP," & vbCrLf
            strSQL = strSQL & "  ACCESS_TIME," & vbCrLf
            strSQL = strSQL & "  SOURCE_PORT," & vbCrLf
            strSQL = strSQL & "  DESTINATION_PORT," & vbCrLf
            strSQL = strSQL & "  PROTOCOL," & vbCrLf
            strSQL = strSQL & "  APPLICATION_PATH," & vbCrLf
            strSQL = strSQL & "  APPLICATION_DESCRIPTION," & vbCrLf
            strSQL = strSQL & "  RULE_DESCRIPTION)" & vbCrLf
            strSQL = strSQL & "VALUES(" & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?)" & vbCrLf

            .Parameters.Append .CreateParameter("source_ip", adVarChar, adParamInput, 16)
            .Parameters.Append .CreateParameter("destination_ip", adVarChar, adParamInput, 16)
            .Parameters.Append .CreateParameter("access_time", adDate, adParamInput, 8 )
            .Parameters.Append .CreateParameter("source_port", adNumeric, adParamInput, 8 )
            .Parameters.Append .CreateParameter("destination_port", adNumeric, adParamInput, 8 )
            .Parameters.Append .CreateParameter("protocol", adVarChar, adParamInput, 15)
            .Parameters.Append .CreateParameter("application_path", adVarChar, adParamInput, 100)
            .Parameters.Append .CreateParameter("application_description", adVarChar, adParamInput, 100)
            .Parameters.Append .CreateParameter("rule_description", adVarChar, adParamInput, 30)

            'Set up the command properties
            .CommandText = strSQL
            .CommandType = adCmdText
            .CommandTimeout = 30
            .ActiveConnection = dbVMFG
        End With

        If Err = 0 Then
            dbVMFG.BeginTrans
            Do While Not snpData.EOF
                comDataInsert("source_ip") = snpData(0).Value
                comDataInsert("destination_ip") = snpData(1).Value
                comDataInsert("access_time") = CDate(snpData(2).Value)
                comDataInsert("source_port") = Val(snpData(3).Value)
                comDataInsert("destination_port") = Val(snpData(4).Value)
                comDataInsert("protocol") = Left(snpData(5).Value, 15)
                comDataInsert("application_path") = Left(snpData(6).Value, 100)
                comDataInsert("application_description") = Left(snpData(7).Value, 100)
                comDataInsert("rule_description") = Left(snpData(8).Value, 30)
                comDataInsert.Execute

                snpData.MoveNext
            Loop

            snpData.Close
            If Err = 0 Then
                dbVMFG.CommitTrans
            Else
                dbVMFG.RollbackTrans
            End If

            strSQL = "SELECT" & vbCrLf
            strSQL = strSQL & "  SOURCE_IP," & vbCrLf
            strSQL = strSQL & "  DESTINATION_IP," & vbCrLf
            strSQL = strSQL & "  ACCESS_TIME," & vbCrLf
            strSQL = strSQL & "  SOURCE_PORT," & vbCrLf
            strSQL = strSQL & "  DESTINATION_PORT," & vbCrLf
            strSQL = strSQL & "  PROTOCOL," & vbCrLf
            strSQL = strSQL & "  APPLICATION_PATH," & vbCrLf
            strSQL = strSQL & "  APPLICATION_DESCRIPTION," & vbCrLf
            strSQL = strSQL & "  RULE_DESCRIPTION" & vbCrLf
            strSQL = strSQL & "FROM" & vbCrLf
            strSQL = strSQL & "  " & strDBTable & vbCrLf
            strSQL = strSQL & "ORDER BY" & vbCrLf
            strSQL = strSQL & "  SOURCE_IP," & vbCrLf
            strSQL = strSQL & "  ACCESS_TIME"
            snpData.Open strSQL, dbVMFG

            If snpData.State = 1 Then
                'The fast way to place the query results into cells
                For i = 0 To snpData.Fields.Count - 1
                    Sheets("ExcelQueryofTextFile").Cells(5, i + 1).Value = snpData.Fields(i).Name
                Next i
                Sheets("ExcelQueryofTextFile").Range(Sheets("ExcelQueryofTextFile").Cells(5, 1), Sheets("ExcelQueryofTextFile").Cells(5, snpData.Fields.Count)).Font.Bold = True

                Sheets("ExcelQueryofTextFile").Range("A6").CopyFromRecordset snpData

                snpData.Close

            End If
        Else
            intResult = MsgBox("Could not create the table " & strDBTable & " in the database." & vbCrLf & Error(Err), 16, "Excel Demo")
        End If
    End If

    Sheets("ExcelQueryofTextFile").Range("A6").Select
    ActiveWindow.FreezePanes = True

    Set snpData = Nothing
    Set comDataInsert = Nothing
End Sub

Retrieve Data to Excel with a Macro using ADO

The following Excel macro shows how to connect to an Oracle database using ADO, create a new worksheet in the current work book, and then fill in the returned data using a slow method (not recommended) and a fast method.

'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting
'*** This section might be in the top portion of the user form, worksheet code, or module:
'Declare a connection object in the general section to hold the connection to the database
Dim dbVMFG As ADODB.Connection
 
'Declare a set of variables to hold the username and password for the database
Dim strUserName As String
Dim strPassword As String
Dim strDatabase As String
'***
 
Dim i as Integer
Dim intResult As Integer
Dim strSQL as String
Dim lngRow as Long
    
On Error Resume Next
 
strDatabase = "MyDB"
 
strUserName = "MyUser"
strPassword = "MyPassword"
 
'Connect to the database
'Oracle connection string
Set dbVMFG = New ADODB.Connection
dbVMFG.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
 
dbVMFG.ConnectionTimeout = 40
dbVMFG.CursorLocation = adUseClient
dbVMFG.Open
 
If (dbVMFG.State <> 1) Or (Err <> 0) Then
    intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")
Else
    strSQL = "SELECT" & VBCrLf
    strSQL = strSQL & "  ID AS RESOURCE_ID," & VBCrLf
    strSQL = strSQL & "  DESCRIPTION" & VBCrLf
    strSQL = strSQL & "FROM" & VBCrLf
    strSQL = strSQL & "  MY_TABLE" & VBCrLf
    strSQL = strSQL & "WHERE" & VBCrLf
    strSQL = strSQL & "  DESCRIPTION LIKE '%10%'" & VBCrLf
    strSQL = strSQL & "ORDER BY" & VBCrLf
    strSQL = strSQL & "  ID"
 
    'Add a new worksheet to the new workbook, add after the last sheet
    ActiveWorkbook.Sheets.Add
    ActiveWorkbook.ActiveSheet.Name = "JustATest"
        
    snpData.Open strSQL, dbVMFG
        
    If snpData.State = 1 Then
        'Slow Method------------------------------------
        lngRow = 0
        'Header Row
        For i = 0 To snpData.Fields.Count – 1
            lngRow = lngRow + 1
            ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Name
            ActiveSheet.Cells(lngRow, i + 1).Font.Bold = True
        Next i
 
        'Detail Rows
        Do While Not snpData.EOF
            lngRow = lngRow + 1
            For i = 0 To snpData.Fields.Count - 1
                ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i)
            Next i
 
            snpData.MoveNext
        Loop
        'End Slow Method------------------------------------
 
        'Fast Method----------------------------------------
        'Do not use this and the slow method!
        For i = 0 To snpData.Fields.Count - 1
            ActiveSheet.Cells(1, i + 1).Value = snpData.Fields(i).Name
        Next i
        ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, snpData.Fields.Count)).Font.Bold = True
    
        ActiveSheet.Range("A2").CopyFromRecordset snpData
            
        'Auto-fit up to 26 columns
        ActiveSheet.Columns("A:" & Chr(64 + snpData.Fields.Count)).AutoFit
        'End Fast Method----------------------------------------
 
        snpData.Close
    End If
End If

SQL – Methods of Reformatting into Equivalent Forms 5

December 2, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

A couple of years ago someone posted in the comp.databases.oracle.database Usenet group the following challenge:

http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/fbe588a109b3de20

Here’s one of my personal favorites and generally a good interview question too.

Assume two identical tables, one named “A” the other “B” with identical column definitions. Assume that some rows in “A” are duplicated in “B” and some in “B” are duplicated in “A” but each table contains rows unique to that table.

Write a single SELECT statement that will retrieve all of the rows from table “A” that are unique to “A”, all the rows from “B” that are unique to “B” and label each retrieved row with the name of the table in which it was found.

Have fun (and yes there are multiple solutions).

This message thread, like several others, generated suggestions from several people.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EscVector offered the following solution:

CREATE TABLE A
  (    COL1 CHAR(4),
       COL2 NUMBER,
       COL3 VARCHAR2(10));

 begin
  for x in 1..10
  loop
  insert into a values ('ab'||x, x,'NONUNIQUE');
  end loop;
  end;
  /

 create table B as select * from a;

 begin
  for x in 1..10
  loop
  insert into a values ('a'||x, x,'UNIQUE');
  end loop;
  end;
  /

 begin
  for x in 1..10
  loop
  insert into b values ('b'||x, x,'UNIQUE');
  end loop;
  end;
  /

 commit;

  (select a.col1 ,a.col2 ,a.col3, 'TABA'  from a minus select
b.col1,b.col2,b.col3, 'TABA' from b )
  union
  (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
a.col1,a.col2,a.col3 ,'TABB' from a );

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I offered the following solutions:

CREATE TABLE TABLE_A (
  COL1 VARCHAR2(20),
  COL2 VARCHAR2(20),
  COL3 VARCHAR2(20));

CREATE TABLE TABLE_B (
  COL1 VARCHAR2(20),
  COL2 VARCHAR2(20),
  COL3 VARCHAR2(20));

INSERT INTO TABLE_A VALUES ('TEST1A','TEST1B','TEST1C');
INSERT INTO TABLE_A VALUES ('TEST2A','TEST2B','TEST2C');
INSERT INTO TABLE_A VALUES ('TEST3A','TEST3B','TEST3C');
INSERT INTO TABLE_A VALUES ('TEST4A','TEST4B','TEST4C');
INSERT INTO TABLE_A VALUES ('TEST5A','TEST5B','TEST5C');

INSERT INTO TABLE_B VALUES ('TEST1A','TEST1B','TEST1C');
INSERT INTO TABLE_B VALUES ('TEST2A','TEST1B','TEST1C');
INSERT INTO TABLE_B VALUES ('TEST3A','TEST3B','TEST3C');
INSERT INTO TABLE_B VALUES ('TEST5A','TEST5B','TEST5C');

SELECT DISTINCT
  NVL(A.COL1,B.COL1) COL1,
  NVL(A.COL2,B.COL2) COL2,
  NVL(A.COL3,B.COL3) COL3,
  NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE
FROM
  TABLE_A A
FULL OUTER JOIN
  TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3
WHERE
  (A.COL1 IS NULL OR B.COL1 IS NULL)
  OR (A.COL2 IS NULL OR B.COL2 IS NULL)
  OR (A.COL3 IS NULL OR B.COL3 IS NULL);

COL1    COL2    COL3    FROM_TABLE
TEST2A  TEST2B  TEST2C  TABLE A
TEST4A  TEST4B  TEST4C  TABLE A
TEST2A  TEST1B  TEST1C  TABLE B

Extra credit:
SELECT DISTINCT
  NVL(A.COL1,B.COL1) COL1,
  NVL(A.COL2,B.COL2) COL2,
  NVL(A.COL3,B.COL3) COL3,
  NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE
FROM
  TABLE_A A
FULL OUTER JOIN
  TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3
WHERE
  UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(A.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(A.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(A.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8')<> UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(B.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(B.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(B.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8');

COL1    COL2    COL3    FROM_TABLE
TEST2A  TEST2B  TEST2C  TABLE A
TEST4A  TEST4B  TEST4C  TABLE A
TEST2A  TEST1B  TEST1C  TABLE B

Here are a couple more solutions, using more than one SELECT in a SQL
statement:
SELECT
  A.COL1,
  A.COL2,
  A.COL3,
  'TABLE A' FROM_TABLE
FROM
  TABLE_A A,
  (SELECT
    A.COL1,
    A.COL2,
    A.COL3
  FROM
    TABLE_A A
  INTERSECT
  SELECT
    B.COL1,
    B.COL2,
    B.COL3
  FROM
    TABLE_B B) M
WHERE
  A.COL1=M.COL1(+)
  AND A.COL2=M.COL2(+)
  AND A.COL3=M.COL3(+)
  AND M.COL1 IS NULL
UNION ALL
SELECT
  B.COL1,
  B.COL2,
  B.COL3,
  'TABLE B' FROM_TABLE
FROM
  TABLE_B B,
  (SELECT
    A.COL1,
    A.COL2,
    A.COL3
  FROM
    TABLE_A A
  INTERSECT
  SELECT
    B.COL1,
    B.COL2,
    B.COL3
  FROM
    TABLE_B B) M
WHERE
  B.COL1=M.COL1(+)
  AND B.COL2=M.COL2(+)
  AND B.COL3=M.COL3(+)
  AND M.COL1 IS NULL;

WITH M AS (
SELECT
  A.COL1,
  A.COL2,
  A.COL3
FROM
  TABLE_A A
INTERSECT
SELECT
  B.COL1,
  B.COL2,
  B.COL3
FROM
  TABLE_B B)
SELECT
  A.COL1,
  A.COL2,
  A.COL3,
  'TABLE A' FROM_TABLE
FROM
  TABLE_A A,
  M
WHERE
  A.COL1=M.COL1(+)
  AND A.COL2=M.COL2(+)
  AND A.COL3=M.COL3(+)
  AND M.COL1 IS NULL
UNION ALL
SELECT
  B.COL1,
  B.COL2,
  B.COL3,
  'TABLE B' FROM_TABLE
FROM
  TABLE_B B,
  M
WHERE
  B.COL1=M.COL1(+)
  AND B.COL2=M.COL2(+)
  AND B.COL3=M.COL3(+)
  AND M.COL1 IS NULL;

SELECT
  A.COL1,
  A.COL2,
  A.COL3,
  'TABLE A' FROM_TABLE
FROM
  TABLE_A A
WHERE
  (A.COL1,A.COL2,A.COL3) NOT IN (
    SELECT DISTINCT
      B.COL1,
      B.COL2,
      B.COL3
    FROM
      TABLE_B B)
UNION ALL
SELECT
  B.COL1,
  B.COL2,
  B.COL3,
  'TABLE B' FROM_TABLE
FROM
  TABLE_B B
WHERE
  (B.COL1,B.COL2,B.COL3) NOT IN (
    SELECT DISTINCT
      A.COL1,
      A.COL2,
      A.COL3
    FROM
      TABLE_A A);

SELECT
  A.COL1,
  A.COL2,
  A.COL3,
  'TABLE A' FROM_TABLE
FROM
  TABLE_A A,
  (SELECT DISTINCT
    B.COL1,
    B.COL2,
    B.COL3
  FROM
    TABLE_B B) B
WHERE
  A.COL1=B.COL1(+)
  AND A.COL2=B.COL2(+)
  AND A.COL3=B.COL3(+)
  AND B.COL3 IS NULL
UNION ALL
SELECT
  B.COL1,
  B.COL2,
  B.COL3,
  'TABLE B' FROM_TABLE
FROM
  TABLE_B B,
  (SELECT DISTINCT
    A.COL1,
    A.COL2,
    A.COL3
  FROM
    TABLE_A A) A
WHERE
  B.COL1=A.COL1(+)
  AND B.COL2=A.COL2(+)
  AND B.COL3=A.COL3(+)
  AND A.COL3 IS NULL;

SELECT
  COL1,
  COL2,
  COL3,
  FROM_TABLE
FROM
(SELECT
  COL1,
  COL2,
  COL3,
  FROM_TABLE,
  COUNT(DISTINCT FROM_TABLE) OVER (PARTITION BY COL1,COL2,COL3)
NUM_TABLES
FROM
(SELECT
  A.COL1,
  A.COL2,
  A.COL3,
  'TABLE A' FROM_TABLE
FROM
  TABLE_A A
UNION ALL
SELECT
  B.COL1,
  B.COL2,
  B.COL3,
  'TABLE B' FROM_TABLE
FROM
  TABLE_B B))
WHERE
  NUM_TABLES=1;

SQL – Methods of Reformatting into Equivalent Forms 4

December 2, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

A couple of years ago someone asked in the comp.databases.oracle.misc Usenet group the following question:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/1ce4e2a5bfa5d86e

I have to limit the number of results without using ROWNUM or something like that. One hint was to use “JOIN”.

I have to select the 10 biggest persons from a table “persons”

id, firstname, lastname, size, age

WITHOUT using ROWNUM or “limitter” like this.

Do you have any idea?

This message thread, like several others, generated suggestions from several people.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I suggested the following:

Method #1:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    LAG(ID,1,-1) OVER (ORDER BY HEIGHT DESC) SIGNAL,
    HEIGHT END_HEIGHT,
    LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) START_HEIGHT
  FROM
    T1) R,
  T1
WHERE
  R.SIGNAL=-1
  AND T1.HEIGHT BETWEEN R.START_HEIGHT AND R.END_HEIGHT;

Method #2:
SELECT
  T1.ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    ID,
    PERCENT_RANK() OVER (ORDER BY T1.HEIGHT DESC) POSITION,
    TR.TOTAL_ROWS
  FROM
    (SELECT
      COUNT(*) TOTAL_ROWS
    FROM
      T1) TR,
    T1) TR,
  T1
WHERE
  TR.POSITION<=(10/TR.TOTAL_ROWS)
  AND TR.ID=T1.ID;

Method #3:
SELECT
  R.ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    ID,
    ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION
  FROM
    T1) R,
  T1
WHERE
  R.POSITION<=10
  AND R.ID=T1.ID;
The results of the above methods look something like this:
ID FIRSTNAME  LASTNAME   HEIGHT     AGE
---------------------------------------
 2 SUSAN      SMITH          65      20
 3 DOROTHY    SMITH          62      21
 4 JOHN       SMITH          72      35
 5 DAVID      SMITH          73      34
 7 ROBERT     SMITH          76      45
10 SUSAN      JOHNSON        65.5    20
11 DOROTHY    JOHNSON        62.5    21
12 JOHN       JOHNSON        72.5    35
13 DAVID      JOHNSON        73.5    34
15 ROBERT     JOHNSON        79      45

Method #4:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE,
    (FIRST_VALUE(HEIGHT) OVER (ORDER BY HEIGHT DESC))*HEIGHT MY_PROD,
    MAX_PROD
  FROM
    (SELECT
      MAX(PROD) MAX_PROD
    FROM
      (SELECT
        HEIGHT*LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) PROD
      FROM
        T1)),
    T1)
WHERE
  MY_PROD>=MAX_PROD;

Method #5:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  T1
MINUS
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1) ROW_PERCENT,
    (COUNT(*) OVER (PARTITION BY 1)-10)/COUNT(*) OVER (PARTITION BY 1) CUT_OFF,
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1)
WHERE
  ROW_PERCENT<=CUT_OFF;

Method #6:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1) ROW_PERCENT,
    (COUNT(*) OVER (PARTITION BY 1)-10)/COUNT(*) OVER (PARTITION BY 1) CUT_OFF,
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1)
WHERE
  ROW_PERCENT>CUT_OFF;

Method #7:
SELECT
  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE
FROM
  (SELECT
    MAX(COUNTER) COUNTER
  FROM
    (SELECT
      LEVEL COUNTER
    FROM
      DUAL
    CONNECT BY
      LEVEL<=10)) C,
 (SELECT
    RANK() OVER (ORDER BY HEIGHT DESC) RANKING,
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1) T1
WHERE
  T1.RANKING<=C.COUNTER;

Method #8:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
 (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1
  ORDER BY
    HEIGHT DESC)
WHERE
  ROWNUM<=10;

Method #9:
SELECT
  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE
FROM
  (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE,
    COUNT(*) OVER (ORDER BY HEIGHT DESC) POSITION
  FROM
    T1) T1,
  (SELECT
    LEVEL COUNTER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10) C
WHERE
  T1.POSITION=C.COUNTER;

Method #10:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  T1
WHERE
  HEIGHT>
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
)))))))))));

Method #13 (loosely inspired by the above SQL statement):
SELECT
  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE
FROM
  T1,
  (SELECT
    T1.HEIGHT,
    COUNT(*) RANKING
  FROM
    T1,
    T1 T2
  WHERE
    T1.HEIGHT<=T2.HEIGHT
  GROUP BY
    T1.HEIGHT
  HAVING
    COUNT(*) BETWEEN 1 AND 10) T2
WHERE
  T1.HEIGHT=T2.HEIGHT;

Method #14 (a slight modification of Method #13):
SELECT
  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE
FROM
  T1,
  (SELECT
    T1.HEIGHT,
    COUNT(*) OVER (PARTITION BY 1) TOTAL_COUNT,
    COUNT(*) RANKING
  FROM
    T1,
    T1 T2
  WHERE
    T1.HEIGHT>T2.HEIGHT
  GROUP BY
    T1.HEIGHT) T2
WHERE
  (T2.TOTAL_COUNT-T2.RANKING+1) BETWEEN 1 AND 10
  AND T2.HEIGHT=T1.HEIGHT;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Dieter Noeth suggested the following:

SELECT t1.*
FROM t1 JOIN t1 AS t2 ON t1.HEIGHT <= t2.HEIGHT
GROUP BY t1.ID, t1.FIRSTNAME,t1.LASTNAME, t1.HEIGHT, t1.AGE
HAVING COUNT(*) <= 10

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Gerard H. Pille suggested the following:

select * from T1
 where height >= (
    select MIN(height) from T1 c1
      where 10 > (select count(*) from T1 c2 where c2.height >
c1.height)
    );

SQL – Methods of Reformatting into Equivalent Forms 3

December 2, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

A couple of years ago someone asked in the comp.databases.oracle.misc Usenet group the following question:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/3572ee04308f6a27

How can I get a specific character count in a string (i.e : string is 56222, and I am looking for ‘2’ occurance when i do :
select charcount(‘56222’) should return : 3 )

This message thread, like several others, generated suggestions from several people.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Anurag Varma suggested the following:

ORA92> select length('56222') - length(replace('56222','2')) from dual;

LENGTH('56222')-LENGTH(REPLACE
------------------------------
                             3

10GR2> select length(regexp_replace('56222','[^2]','')) from dual;

LENGTH(REGEXP_REPLACE('56222','[^2]',''))
-----------------------------------------
                                        3

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DA Morgan suggested the following:

SELECT LENGTH(TRANSLATE('56222', '2013456789', '2')) FROM dual;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I then offered the following:

SELECT
  SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
FROM
  DUAL
CONNECT BY
  LEVEL<20;

SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
-------------
9

SELECT
  SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))
FROM
  DUAL
CONNECT BY
  LEVEL<255;

SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))
------------------
9

SELECT
  SUM(
    CASE WHEN INSTR('562225622256222','2',1,ROWNUM)>0 THEN
      1
    ELSE
      0
    END
    )
FROM
  DUAL
CONNECT BY
  LEVEL<20;

SELECT
  COUNT(
    CASE WHEN INSTR('562225622256222','2',1,ROWNUM)>0 THEN
      1
    ELSE
      NULL
    END
    )
FROM
  DUAL
CONNECT BY
  LEVEL<20;

SUM(CASEWHENINSTR('562225622256222','2',1,ROWNUM)>0THEN1ELSE0END)
9

COUNT(CASEWHENINSTR('562225622256222','2',1,ROWNUM)>0THEN1ELSENULLEND)
9

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko offered the following to test the performance of each method:

SQL> declare
   2  s number;
   3  c number;
   4  begin
   5
   6          s:=dbms_utility.get_time;
   7          for i in 1..100000 loop
   8          SELECT SUM(DECODE(SUBSTR('562225622256222',ROWNUM,1),'2',1,0))
   9          into c
  10          FROM DUAL CONNECT BY LEVEL<=15;
  11          end loop;
  12          s := dbms_utility.get_time -s;
  13          dbms_output.put_line('SUBSTR/DECODE/CONNECT BY time: '||trunc(s/100));
  14
  15          s:=dbms_utility.get_time;
  16          for i in 1..100000 loop
  17          SELECT  SUM(SIGN(INSTR('562225622256222','2',1,ROWNUM)))
  18          into c
  19          FROM DUAL CONNECT BY LEVEL<=15;
  20          end loop;
  21          s := dbms_utility.get_time -s;
  22          dbms_output.put_line('SIGN/INSTR/CONNECT BY time: '|| trunc(s/100));
  23
  24          s:=dbms_utility.get_time;
  25          for i in 1..100000 loop
  26          select length('562225622256222') - length(replace('562225622256222','2'))
  27          into c
  28          from dual;
  29          end loop;
  30          s := dbms_utility.get_time -s;
  31          dbms_output.put_line('LENGTH/REPLACE time: '|| trunc(s/100));
  32
  33          s:=dbms_utility.get_time;
  34          for i in 1..100000 loop
  35          select length(regexp_replace('562225622256222','[^2]',''))
  36          into c
  37          from dual;
  38          end loop;
  39          s := dbms_utility.get_time -s;
  40          dbms_output.put_line('REGEXP_REPLACE time: '|| trunc(s/100));
  41  end;
  42  /
SUBSTR/DECODE/CONNECT BY time: 17
SIGN/INSTR/CONNECT BY time: 18
LENGTH/REPLACE time: 13
REGEXP_REPLACE time: 13

SQL – Methods of Reformatting into Equivalent Forms 2

December 2, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

A couple of years ago someone asked in the comp.databases.oracle.misc Usenet group the following question:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/576ea61b1a93469b

I need to write a query that returns only the students that have read all books by an author.  I have these tables set up so far….

create table Books (
        BookTitle varchar2(20) PRIMARY KEY,
        author varchar2(20)
);

create table BookCamp (
        MemberName varchar2(20),
        BookTitle varchar2(20),
        CONSTRAINT fk_BookTitle
          FOREIGN KEY (BookTitle)
          REFERENCES Books(BookTitle)
);

insert into Books values ('Psycho', 'Brian');
insert into Books values ('Happy Rotter', 'Rocksteady');
insert into Books values ('Goblet', 'J.K Rowling');
insert into Books values ('Prisoner', 'J.K Rowling');
insert into BookCamp values ('Bob', 'Psycho');
insert into BookCamp values ('Chuck', 'Goblet');
insert into BookCamp values ('Chuck', 'Prisoner');
insert into BookCamp values ('Mike', 'Psycho');
insert into BookCamp values ('Mike', 'Goblet');
insert into BookCamp values ('Mike', 'Prisoner');
insert into BookCamp values ('Mary', 'Goblet');

So basically, if I inputted “J.K Rowling” the names “Chuck” and “Mike” should come up.  If the author is “Brian” then the names “Bob” and “Mike” should come up.  I’ve tried several things like… select membername from BookCamp where BookTitle in(select BookTitle from Books where (author = ‘J.K Rowling’)); but this obviously isn’t quite there….Any Help?

This message thread, like several others, generated suggestions from several people.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko suggested the following:

SQL> SELECT DISTINCT membername
   2  FROM bookcamp bc
   3  WHERE NOT EXISTS (
   4    SELECT NULL
   5    FROM bookcamp bc1
   6    PARTITION BY (membername)
   7    RIGHT OUTER JOIN books b
   8    ON (bc1.booktitle=b.booktitle)
   9    WHERE b.author='J.K Rowling'
  10    AND bc.membername=bc1.membername AND bc1.booktitle IS NULL)
  11  /

MEMBERNAME
--------------------
Chuck
Mike

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I then offered the following:

Maxim has provided a solution that produces the desired list.  Let’s see if we can develop another method to solve this problem.  First, a simple experiment using the analytical version of COUNT:

SELECT
  B.BOOKTITLE,
  B.AUTHOR,
  COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) NUM_BOOKS
FROM
  BOOKS B
WHERE
  B.AUTHOR='J.K Rowling';

BOOKTITLE            AUTHOR                NUM_BOOKS
-------------------- -------------------- ----------
Prisoner             J.K Rowling                   2
Goblet               J.K Rowling                   2

Not too impressive yet, but let’s add in the second table:

SELECT
  BC.MEMBERNAME,
  B.BOOKTITLE,
  B.AUTHOR,
  COUNT(DISTINCT B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) AUTHOR_NUM_BOOKS,
  COUNT(BC.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME, B.AUTHOR) MEMBER_NUM_BOOKS
FROM
  BOOKS B,
  BOOKCAMP BC
WHERE
  B.AUTHOR='J.K Rowling'
  AND B.BOOKTITLE=BC.BOOKTITLE(+);

Note that I changed the original COUNT(B.BOOKTITLE) to COUNT(DISTINCT B.BOOKTITLE) and changed the alias to AUTHOR_NUM_BOOKS:

MEMBERNAME           AUTHOR_NUM_BOOKS MEMBER_NUM_BOOKS
-------------------- ---------------- ----------------
Chuck                               2                2
Mary                                2                1
Mike                                2                2
Mike                                2                2
Chuck                               2                2

Now, we need a way to first eliminate all rows where AUTHOR_NUM_BOOKS is not equal to MEMBER_NUM_BOOKS, and then return a list of names without duplicates.  This can be accomplished by sliding the above SQL statement into an inline view:

SELECT DISTINCT
  MEMBERNAME
FROM
  (SELECT
    BC.MEMBERNAME,
    COUNT(DISTINCT B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR)
AUTHOR_NUM_BOOKS,
    COUNT(BC.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME, B.AUTHOR)
MEMBER_NUM_BOOKS
  FROM
    BOOKS B,
    BOOKCAMP BC
  WHERE
    B.AUTHOR='J.K Rowling'
    AND B.BOOKTITLE=BC.BOOKTITLE(+))
WHERE
  AUTHOR_NUM_BOOKS=MEMBER_NUM_BOOKS;

MEMBERNAME
--------------------
Chuck
Mike

Let’s try again, this time without analytical functions.  First, let’s find out how many of the author’s books were read by each membername:

SELECT
  BC.MEMBERNAME,
  B.AUTHOR,
  COUNT(*) MEMBER_NUM_BOOKS
FROM
  BOOKS B,
  BOOKCAMP BC
WHERE
  B.AUTHOR='J.K Rowling'
  AND BC.BOOKTITLE=B.BOOKTITLE
GROUP BY
  BC.MEMBERNAME,
  B.AUTHOR;

MEMBERNAME           AUTHOR               MEMBER_NUM_BOOKS
-------------------- -------------------- ----------------
Mike                 J.K Rowling                         2
Chuck                J.K Rowling                         2
Mary                 J.K Rowling                         1

Now, let’s determine the number of books written by each author:

SELECT
  AUTHOR,
  COUNT(*) AUTHOR_NUM_BOOKS
FROM
  BOOKS
GROUP BY
  AUTHOR;

AUTHOR               AUTHOR_NUM_BOOKS
-------------------- ----------------
Rocksteady                          1
Brian                               1
J.K Rowling                         2

Let’s put each into an inline view and pull out the membernames of interest:

SELECT DISTINCT
  BC.MEMBERNAME
FROM
  (SELECT
    BC.MEMBERNAME,
    B.AUTHOR,
    COUNT(*) MEMBER_NUM_BOOKS
  FROM
    BOOKS B,
    BOOKCAMP BC
  WHERE
    B.AUTHOR='J.K Rowling'
    AND BC.BOOKTITLE=B.BOOKTITLE
  GROUP BY
    BC.MEMBERNAME,
    B.AUTHOR) BC,
  (SELECT
    AUTHOR,
    COUNT(*) AUTHOR_NUM_BOOKS
  FROM
    BOOKS
  GROUP BY
    AUTHOR) B
WHERE
  B.AUTHOR=BC.AUTHOR
  AND B.AUTHOR_NUM_BOOKS=BC.MEMBER_NUM_BOOKS;

MEMBERNAME
--------------------
Chuck
Mike

Let’s try one more time, A simple starting point:

SELECT
  BC.MEMBERNAME,
  COUNT(BC.BOOKTITLE)
FROM
  BOOKS B,
  BOOKCAMP BC
WHERE
  B.AUTHOR='J.K Rowling'
  AND B.BOOKTITLE=BC.BOOKTITLE(+)
GROUP BY
  BC.MEMBERNAME;

MEMBERNAME           COUNT(BC.BOOKTITLE)
-------------------- -------------------
Chuck                                  2
Mary                                   1
Mike                                   2

[The above does not need to be an outer join]
Now, let’s add an inline view to retrieve the total number of books written by the author:

SELECT
  BC.MEMBERNAME,
  COUNT(BC.BOOKTITLE) MEMBER_NUM_BOOKS,
  NB.AUTHOR_NUM_BOOKS
FROM
  BOOKS B,
  BOOKCAMP BC,
  (SELECT
    COUNT(*) AUTHOR_NUM_BOOKS
  FROM
    BOOKS
  WHERE
    AUTHOR='J.K Rowling') NB
WHERE
  B.AUTHOR='J.K Rowling'
  AND B.BOOKTITLE=BC.BOOKTITLE(+)
GROUP BY
  BC.MEMBERNAME,
  NB.AUTHOR_NUM_BOOKS;

MEMBERNAME           MEMBER_NUM_BOOKS AUTHOR_NUM_BOOKS
-------------------- ---------------- ----------------
Chuck                               2                2
Mike                                2                2
Mary                                1                2

The final clean up is accomplished with a HAVING clause:

 SELECT
  BC.MEMBERNAME
FROM
  BOOKS B,
  BOOKCAMP BC,
  (SELECT
    COUNT(*) AUTHOR_NUM_BOOKS
  FROM
    BOOKS
  WHERE
    AUTHOR='J.K Rowling') NB
WHERE
  B.AUTHOR='J.K Rowling'
  AND B.BOOKTITLE=BC.BOOKTITLE(+)
GROUP BY
  BC.MEMBERNAME,
  NB.AUTHOR_NUM_BOOKS
HAVING
  COUNT(BC.BOOKTITLE)=NB.AUTHOR_NUM_BOOKS;

MEMBERNAME
--------------------
Chuck
Mike

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko then suggested the following:

Just for fun, yet another one:

SELECT MEMBERNAME
FROM   (SELECT B.MEMBERNAME,CAST(COLLECT(booktitle) AS
SYS.dbms_debug_vc2coll) BOOKLIST
         FROM   BOOKCAMP B
         GROUP  BY MEMBERNAME) M,
        (SELECT AUTHOR,CAST(COLLECT(booktitle) AS
SYS.dbms_debug_vc2coll) BOOKLIST
         FROM   BOOKS B
         GROUP  BY AUTHOR) A
WHERE  A.BOOKLIST SUBMULTISET OF M.BOOKLIST
AND    AUTHOR = 'J.K Rowling'
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I then offered the following:

Let’s see if there is another way – caution, this might be inefficient:
The starting point:

SELECT
  B.AUTHOR,
  B.BOOKTITLE,
  ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE)
BOOK_NUM,
  COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
FROM
  BOOKS B
WHERE
  AUTHOR='J.K Rowling';

AUTHOR               BOOKTITLE              BOOK_NUM BOOK_COUNT
-------------------- -------------------- ---------- ----------
J.K Rowling          Goblet                        1          2
J.K Rowling          Prisoner                      2          2

Now, let’s put the book list into a comma separated list:

SELECT
  SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
FROM
  (SELECT
    B.AUTHOR,
    B.BOOKTITLE,
    ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE) BOOK_NUM,
    COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
  FROM
    BOOKS B
  WHERE
    B.AUTHOR='J.K Rowling')
WHERE
  BOOK_NUM=BOOK_COUNT
CONNECT BY PRIOR
  BOOK_NUM=BOOK_NUM-1
START WITH
  BOOK_NUM=1;

BOOK_LIST
---------------
Goblet,Prisoner

We are now half way done.  Prepare to do the same with the BOOKCAMP
table:

SELECT
  BC.MEMBERNAME,
  BC.BOOKTITLE,
  ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY B.BOOKTITLE) BOOK_NUM,
  COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR) BOOK_COUNT
FROM
  BOOKS B,
  BOOKCAMP BC
WHERE
  B.AUTHOR='J.K Rowling'
  AND B.BOOKTITLE=BC.BOOKTITLE;

MEMBERNAME           BOOKTITLE              BOOK_NUM BOOK_COUNT
-------------------- -------------------- ---------- ----------
Chuck                Goblet                        1          2
Chuck                Prisoner                      2          2
Mary                 Goblet                        1          1
Mike                 Goblet                        1          2
Mike                 Prisoner                      2          2

Generate a comma separated list for each MEMBERNAME:

SELECT
  MEMBERNAME,
  SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
FROM
  (SELECT
    BC.MEMBERNAME,
    BC.BOOKTITLE,
    ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY B.BOOKTITLE) BOOK_NUM,
    COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR) BOOK_COUNT
  FROM
    BOOKS B,
    BOOKCAMP BC
  WHERE
    B.AUTHOR='J.K Rowling'
    AND B.BOOKTITLE=BC.BOOKTITLE)
WHERE
  BOOK_NUM=BOOK_COUNT
CONNECT BY PRIOR
  (MEMBERNAME||TO_CHAR(BOOK_NUM))=(MEMBERNAME||TO_CHAR(BOOK_NUM-1))
START WITH
  BOOK_NUM=1;

MEMBERNAME  BOOK_LIST
--------------------
Chuck       Goblet,Prisoner
Mary        Goblet
Mike        Goblet,Prisoner

Now, let’s put it all together to see where the author book list matches the MEMBERNAME book lists:

SELECT
  BC.MEMBERNAME
FROM
(SELECT
  SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
FROM
  (SELECT
    B.AUTHOR,
    B.BOOKTITLE,
    ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE) BOOK_NUM,
    COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
  FROM
    BOOKS B
  WHERE
    B.AUTHOR='J.K Rowling')
WHERE
  BOOK_NUM=BOOK_COUNT
CONNECT BY PRIOR
  BOOK_NUM=BOOK_NUM-1
START WITH
  BOOK_NUM=1) B,
(SELECT
  MEMBERNAME,
  SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
FROM
  (SELECT
    BC.MEMBERNAME,
    BC.BOOKTITLE,
    ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY B.BOOKTITLE) BOOK_NUM,
    COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR) BOOK_COUNT
  FROM
    BOOKS B,
    BOOKCAMP BC
  WHERE
    B.AUTHOR='J.K Rowling'
    AND B.BOOKTITLE=BC.BOOKTITLE)
WHERE
  BOOK_NUM=BOOK_COUNT
CONNECT BY PRIOR
  (MEMBERNAME||TO_CHAR(BOOK_NUM))=(MEMBERNAME||TO_CHAR(BOOK_NUM-1))
START WITH
  BOOK_NUM=1) BC
WHERE
  B.BOOK_LIST=BC.BOOK_LIST;

MEMBERNAME
--------------------
Chuck
Mike

Oddly, the above executes much faster than the CAST(COLLECT(booktitle) AS SYS.dbms_debug_vc2coll) solution.  Maybe the dataset size should be increased, and the OP should post the performance results of each method to see how the first two solutions compare with the others.  I think that it would be interesting to see if the CAST(COLLECT(booktitle) AS SYS.dbms_debug_vc2coll) method scales better than the other methods.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko then suggested the following:

This ties to the problem of set comparisons in sql, which i believe ( i don’t mean multiset operations) can’t be effectively solved in pure sql.
Yet one approach (borrowed from
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1…)

SELECT DISTINCT MEMBERNAME
FROM   (SELECT BC.*,
                B.*,
                SUM(B_RNK) OVER(PARTITION BY MEMBERNAME, AUTHOR) M_RNK
         FROM   BOOKCAMP BC,
                (SELECT B.*, SUM(B_RNK) OVER(PARTITION BY AUTHOR) A_RNK
                 FROM   (SELECT B.*,
                                POWER(2,
                                      DENSE_RANK() OVER(ORDER BY BOOKTITLE) - 1) B_RNK
                         FROM   BOOKS B) B) B
         WHERE  BC.BOOKTITLE = B.BOOKTITLE)
WHERE  AUTHOR = 'J.K Rowling'
AND    A_RNK = M_RNK
/

however, it’ll have its limitations too ( and on really big sets – bigger than 1000 members) – i think, all suggested solutions will not perform very well. For middle sized sets ( where the complete resultsets will fit into pga) – the best performance i saw until now ( for similar tasks) – has the model clause.

SQL – Methods of Reformatting into Equivalent Forms 1

December 2, 2009

(Forward to the Next Post in the Series)

A couple of years ago someone asked in the comp.databases.oracle.misc Usenet group the following question:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/24827610d4e40a0d

I’ve tried SQL to come up with the result below but am having no luck. Please, can someone help me!!!!!:)  Believe me, any and all help will be greatly appreciated.

What I need is the task_ids that have actv_code.short_name like ‘%FIN’ where actv_code.code = ‘Lead Craft’ and no actv_code.code that equals Outage Code

 

 

task table
task_id  task_code
  1         W123456
  2         07146566
  3         07146567
  4         06230001
  5         06123321
  6         06496334
  7         W642121
  8         05462111

actv_code table
task_id  code                 short_name
   1       Outage Code         R16
   4       Outage Code         R15
   6       Outage Code         R16
   1       Lead Craft          ZFM
   5       Lead Craft          EFIN
   6       Lead Craft          MFIN
   7       Lead Craft          IFIN
   8       Outage Code         R16
   8       Lead Craft          MFIN

Result Set
task_id  task_code
   5        06123321
   7        W642121 

 

This message thread, like several others, generated suggestions from several people. 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Michel Cadot suggested the following:

Just write it as you explain it:

select task_id
from actv_code a
where short_name like '%FIN'
  and code = 'Lead Craft'
  and not exists (select null from actv_code b where b.task_id = a.task_id and b.code = 'Outage Code')
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I then offered the following:

Let’s start out with the table definitions and insert statements:

CREATE TABLE TASK(
  TASK_ID NUMBER(10),
  TASK_CODE VARCHAR2(10));
CREATE TABLE ACTV_CODE(
  TASK_ID NUMBER(10),
  CODE VARCHAR2(20),
  SHORT_NAME VARCHAR2(10));
INSERT INTO TASK VALUES (1,'W123456');
INSERT INTO TASK VALUES (2,'07146566');
INSERT INTO TASK VALUES (3,'07146567');
INSERT INTO TASK VALUES (4,'06230001');
INSERT INTO TASK VALUES (5,'06123321');
INSERT INTO TASK VALUES (6,'06496334');
INSERT INTO TASK VALUES (7,'W642121');
INSERT INTO TASK VALUES (8,'05462111');
COMMIT;

INSERT INTO ACTV_CODE VALUES (1,'Outage Code','R16');
INSERT INTO ACTV_CODE VALUES (4,'Outage Code','R15');
INSERT INTO ACTV_CODE VALUES (6,'Outage Code','R16');
INSERT INTO ACTV_CODE VALUES (1,'Lead Craft','ZFM');
INSERT INTO ACTV_CODE VALUES (5,'Lead Craft','EFIN');
INSERT INTO ACTV_CODE VALUES (6,'Lead Craft','MFIN');
INSERT INTO ACTV_CODE VALUES (7,'Lead Craft','IFIN');
INSERT INTO ACTV_CODE VALUES (8,'Outage Code','R16');
INSERT INTO ACTV_CODE VALUES (8,'Lead Craft','MFIN');
COMMIT;

Let’s determine the list TASK_IDs that you definitely do not want:

SELECT DISTINCT
  TASK_ID
FROM
  ACTV_CODE
WHERE
  CODE='Outage Code';

We can use that list to exclude specific rows from the query results like this:

SELECT
  T.TASK_ID,
  T.TASK_CODE
FROM
  TASK T,
  ACTV_CODE AC,
  (SELECT DISTINCT
    TASK_ID
  FROM
    ACTV_CODE
  WHERE
    CODE='Outage Code') ACN
WHERE
  T.TASK_ID=ACN.TASK_ID(+)
  AND ACN.TASK_ID IS NULL
  AND T.TASK_ID=AC.TASK_ID
  AND AC.SHORT_NAME LIKE '%FIN';

We basically created an outer join between the list of records and the list of records that we do not want (T.TASK_ID=ACN.TASK_ID(+)), and then specified that the record should not be in those records that we do not want (AND ACN.TASK_ID IS NULL).

   TASK_ID TASK_CODE
========== ==========
         7 W642121
         5 06123321

You may need to make minor adjustments to the above SQL statement.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Chris L. offered the following:

SELECT * FROM task WHERE task_id IN
(
  SELECT task_id FROM actv_code WHERE short_name LIKE '%FIN'
  AND code='Lead Craft'
  MINUS
  SELECT task_id FROM actv_code WHERE code='Outage Code'
);

Though I’d do something about that “short_name ends with FIN” filter,
and I’d try and generate a table with codes (Lead Craft, Outage Code,
etc) and use the ID’s not the descriptions.