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.

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.

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.

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.

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 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:

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.

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.

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.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: