| Create a temporary/transient table within a SELECT [message #640702] |
Tue, 04 August 2015 04:06  |
johnjacobt
Messages: 11
Registered: July 2010
Location: India
|
Junior Member |
|
|
Hi,
Is there an shorter way to create the transient table created by line numbers 2 till 20 in the query shown below?
SQL> SELECT * FROM
2 (SELECT '123_IN' EMP_ID, 'Rahul' Buddy FROM DUAL
3 UNION
4 SELECT '234_US', 'Steve' FROM DUAL
5 UNION
6 SELECT '345_JP', 'Akira' FROM DUAL
7 UNION
8 SELECT '238_US', 'John' FROM DUAL
9 UNION
10 SELECT '674_IN', 'Uma' FROM DUAL
11 UNION
12 SELECT '738_NW', 'Andre' FROM DUAL
13 UNION
14 SELECT '614_IL', 'Meirav' FROM DUAL
15 UNION
16 SELECT '742_UK', 'Darren' FROM DUAL
17 UNION
18 SELECT '655_US', 'Chris' FROM DUAL
19 UNION
20 SELECT '506_UK', 'Gary' FROM DUAL)
21 WHERE EMP_Name LIKE '%re%';
EMP_ID BUDDY
------ -----
738_NW Andre
742_UK Darren
My Objective:
I have the above data where Buddy (column 2) is a temporary name employees with emp_id (column 1) has selected for themselves. Column 1 is available in an HR Database. Column 2 is not available in the DB, it is manually collected in a sheet of paper every month beginning. I have a query that fetches many details about employees from the HR database. After fetching the data I manually add a column with the Buddy name against respective emp ID's. I have to do this activity on a daily basis each month. The buddy name info does not change, so I want to plug the buddy names right into the query (as a one time activity) and do a join, so that I don't have to manually insert the column every time.
The above code works for me but makes my query look very huge as I have about 20 buddy names every month. So I am looking for a shorter solution from a usability perspective.
I have noticed a somewhat similar problem and solution in Stackoverflow. But I'm not able to figure out how to use this with 2 columns.
Any help is appreciated. Thanks!
|
|
|
|
|
|
| Re: Create a temporary/transient table within a SELECT [message #640709 is a reply to message #640705] |
Tue, 04 August 2015 06:11  |
johnjacobt
Messages: 11
Registered: July 2010
Location: India
|
Junior Member |
|
|
Michel Cadot wrote on Tue, 04 August 2015 15:38
1) First, you should not fake your output, it is most likely that your post will be skipped without more reading as soon as we see you "lie" (WHERE EMP_Name LIKE '%re%')
You're right in this respect. I made last minute changes to the column name just before I submitted the post. My intention was to give a more meaningful name to the column than what I used when ran the query in SQL Plus.
Michel Cadot wrote on Tue, 04 August 2015 15:38
2) Anyway you have to enter the names, in the query, in a permanent table (using INSERT), in an Excel sheet or whatever you can think as permanent storage to be read afterwards you have to write the names.
I don't have write access to the data base, so I will not be able to insert.
Michel Cadot wrote on Tue, 04 August 2015 15:38
3) 40 lines for a query is not huge above all when they are just "select from dual" and "union all"; you will see many examples of such things just to show a demo.
The query may not be heavy as such to the system. But it would look very bulky as the remaining part of the query (to which I intend to plug these 40 lines) is already about 500 words spread over 29 lines. Hence my interest in finding a shorter way of doing it.
|
|
|
|
| Re: Create a temporary/transient table within a SELECT [message #640710 is a reply to message #640709] |
Tue, 04 August 2015 06:17 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
As I said, you have to write them somewhere.
You can put them in a file and read it using an external table.
You can put them in an Excel sheet and build your query using VBA and ODBC/OLE DB.
You can put them in a file and generate the query with a simple shell script.
...
the only limit is your imagination.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
