Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle Table and Excel data column join (Read 6175 times)
lewisc3
YaBB Newbies
*




Posts: 1
Oracle Table and Excel data column join
19.10.05 at 22:18:14
 
Can one join on a whole column so it would be like a 2 table join based on a common element? I have a bunch of rows in Excel containing IDs and test scores and I want to join them by ID to an Oracle table which has IDs and names. Given Excel data like:
A      B
992299      94
991199      85
...
 
Where A = ID and B = score, I would like to join to an oracle table which
has
ID      FName      LName
992299      John      Doe
991199      Sue      Smith
...
 
To get the following result in Excel:
A      B      C      D
992299      94      John      Doe
991199      85      Sue      Smith
...
 
Would something like:
SELECT ora_name_table.fname,
ora_name_table.lname
FROM ora_name_table
WHERE ora_name_table.ID = A:A
 
Work?
 
If we can get the above kind of join to work we will be able to join non-Oracle and Oracle data. I know we can join such using Oracle’s EXTERNAL TABLES, or we could use SQLLOADER, or we could use Oracle 10g’s XQuery, but if SQL*XL can do the same, it would be much easier for both the IT staff and our users.
 
You have a nice product. If we can get it to work for our reporting, I am going to recommend it to other schools. Especially if we can get it to work with Microsoft’s Share Point Services (WSS and not SPS, the portal version).  That way we can use WSS to store and arrange our queries, SQL*XL to perform the queries against our database, and such as Excel to format, analyze, and display our queries. Your XML looks promising too.
 
Thanks,
 
Chris
Back to top
 
« Last Edit: 11.11.05 at 10:48:23 by Gerrit-Jan Linker »  
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle Table and Excel data column join
Reply #1 - 20.10.05 at 16:16:17
 
I would use SQL*XL's SQLQueryValue worksheet function to tackle this. You can use normal SQL as well. I will explain that below.
 
To use SQL*XL's SQLQueryValue worksheet function in your situation type:
 
                 A            B       C
1           992299      94       =sqlxl.xla!SQLQueryValue('select username from yourtable where id=' & A1 & ' and score=' & B1)
2           991199      85       =sqlxl.xla!SQLQueryValue('select username from yourtable where id=' & A2 & ' and score=' & B2)
 
Of course you just add the formula in C1 only and then use copy and paste to copy the formula to C2 or any other cell below that you wish to use too.
 
You can also use plain SQL to do this. Example:
                 A            B       C
1           992299      94       =concatenate('select username from yourtable where id=' , A1 , ' and score=' , B1 , ' to D1;')
2           991199      85       =concatenate('select username from yourtable where id=' , A2 , ' and score=' , B2 , ' to D2;')
 
Now select C1:C2 and open SQL*XL's SQL dialog. SQL*XL will take the SQL from the worksheet. Switch off the print headings option and say Ok for all.
 
Notes:
In this last method note the semi colon to end each statement. If you choose that SQL*XL should regard the whole block as one SQL statement this is essential for SQL*XL to be able to successfully separate the messages again. You can also use the option to regard each cell as a separate statement.
Also in the last method I have put the targets D1 and D2 hardcoded as a string in the formula. If you copy it down it will not change. If you require this to be dynamic use the technique proposed in forum article using the Address function:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1129652283/1#1
 
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1