Home » SQL & PL/SQL » SQL & PL/SQL » Using Dual to Generate Rows - Virtual Table
Using Dual to Generate Rows - Virtual Table [message #304244] Tue, 04 March 2008 11:21 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
Employees

Name HourlyRate
John  10
Kevin 20
Anacedent 200
Migel     200


I want to manually generate this.

Would I use dual?

IE

select 'Name', 'HourlyRate'
'John',  '10'
'Kevin', '20'
'Anacedent,' 200'
'Migel',     '200' from dual


However that just displays them on one row with multiple columns.
Re: Using Dual to Generate Rows - Virtual Table [message #304246 is a reply to message #304244] Tue, 04 March 2008 11:37 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
you would do
 SELECT your values for one row FROM DUAL UNION ALL
SELECT your values for one row FROM DUAL UNION ALL
SELECT your values for one row FROM DUAL UNION ALL
SELECT your values for one row FROM DUAL UNION ALL
SELECT your values for one row FROM DUAL
Re: Using Dual to Generate Rows - Virtual Table [message #304247 is a reply to message #304246] Tue, 04 March 2008 11:46 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
That worked.

That prompted me to look into UNION ALL

http://www.w3schools.com/sql/sql_union.asp

I added Column names by doing this:

select '03678' JOB,'31-DEC-08' PERIOD from dual
union all
select '2333','31-DEC-08'from dual


Is there a different way? I don't want to add the column names , JOB and PERIOD by the top row.

Is that the only way to do it?
Re: Using Dual to Generate Rows - Virtual Table [message #304251 is a reply to message #304247] Tue, 04 March 2008 12:11 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
What exactly is it that you are trying to do?
Re: Using Dual to Generate Rows - Virtual Table [message #304253 is a reply to message #304244] Tue, 04 March 2008 12:14 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
I am doing an UPSERT using temporary data generated from a CSV.

I just appended the column name to each row and that seemed to work.
Re: Using Dual to Generate Rows - Virtual Table [message #304271 is a reply to message #304253] Tue, 04 March 2008 12:59 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Sounds to me like you actually need to look at external tables and the merge command.
Previous Topic: Call from Java Plsql Procedure with VArray as Out Parameter
Next Topic: CASE WHEN problems
Goto Forum:
  


Current Time: Fri Dec 09 15:53:44 CST 2016

Total time taken to generate the page: 0.28123 seconds