Home » Developer & Programmer » Forms » Union Operation in Forms (Oracle IDS 10g,Windows XP)
Union Operation in Forms [message #376385] Wed, 17 December 2008 03:23 Go to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Hi All !

I want to combine two tables using union operation.. I tried with the coding

 select employeeid,leavetype,fromdate,todate,numberofdays,status   from empaply
union
select employeeid,leavetype,fromdate,todate,numberofdays,status
 from
 aplybkup


This works well in the sql database window but not in the oracle forms .. In forms i got an error to add INTO clause.. so i tried with the coding ..
Table names:empaply and hod_al and aplybkup -- rest are the field names in the corresponding table ..

select employeeid,leavetype,fromdate,todate,numberofdays,status
   into :hod_al.employeeid,:hod_al.leavetype,:hod_al.fromdate,:hod_al.todate,:hod_al.numberofdays,:hod_al.status
  from empaply  
union  
 select employeeid,leavetype,fromdate,todate,numberofdays,status 
into :hod_al.employeeid,:hod_al.leavetype,:hod_al.fromdate,:hod_al.todate,:hod_al.numberofdays,:hod_al.status
  from aplybkup ;


But i got an error saying 
"Encountered the symbol INTO when 
expecting (,/,%,*,. etc)."

Please help me with a solution ..

Thanks and Regards,
varosh
[EDITED by DJM: fixed crappy formatting]

[Updated on: Wed, 07 January 2009 00:33] by Moderator

Report message to a moderator

Re: Union Operation in Forms [message #376388 is a reply to message #376385] Wed, 17 December 2008 03:31 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
surprised.. union itself works fine please specify the version of forms

Report message to a moderator

Re: Union Operation in Forms [message #376391 is a reply to message #376388] Wed, 17 December 2008 03:38 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Its Oracle Forms 10g !

Thanks and Regards,
varosh

Report message to a moderator

Re: Union Operation in Forms [message #376396 is a reply to message #376385] Wed, 17 December 2008 03:50 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
This has got nothing to do with forms and everything to do with your select.
SELECT INTO never works with multiple into's
You need to use an inline view.
Something like this:
SELECT <column list> INTO <variable list>
FROM (SELECT <column list>
      FROM table1
      UNION
      SELECT <column list>
      FROM table2);

Report message to a moderator

Re: Union Operation in Forms [message #376404 is a reply to message #376396] Wed, 17 December 2008 04:07 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, this won't work either unless the result is 1 record (or no records at all) because - you can't store many emoloyees into a single item.

I agree - cookiemonster's solution is fine if you use it in, for example, a cursor FOR loop in order to populate a form block. However, why would you do that? If such a UNION is data source for this block, create a VIEW and base the block on the view.

Though, you (varosh81) have never said what is the purpose of this SELECT statement.

Report message to a moderator

Re: Union Operation in Forms [message #376413 is a reply to message #376396] Wed, 17 December 2008 04:30 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Hi Cookiemonster and LittleFoot !

Thanks for your reply !

I tried with your coding(cookiemonster) it worked fine without error but when i run the form i got the current record alone and not all the rows that is obtained by UNION..

i got 32 records in sql database window by UNION operation but i got here only one row that is the current row alone on running the form..

So what can be done to get all the records on the form ..

Actually i have saved the entries of a form in two different tables.. With one table having the current record and the other table having the previous records..

So i want combine these two table and retrieve those records in a new form ..

Thanks and Regards,
varosh

Report message to a moderator

Re: Union Operation in Forms [message #376421 is a reply to message #376385] Wed, 17 December 2008 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
D'oh - really didn't think that through.

Varosh - is there any reason you can't use a view as Littlefoot suggested?

Report message to a moderator

Re: Union Operation in Forms [message #376432 is a reply to message #376421] Wed, 17 December 2008 06:01 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Hi Cookie Monster!

I tried using VIEW.. Bt it didnt work out since iam trying to combine two tables which have same column names ..
It gave me the error
ORA-00957 : Duplicate Column Name


Thanks and Regards,
varosh

[Updated on: Wed, 17 December 2008 06:05]

Report message to a moderator

Re: Union Operation in Forms [message #376445 is a reply to message #376385] Wed, 17 December 2008 06:33 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Show us the code of your view

Report message to a moderator

Re: Union Operation in Forms [message #376640 is a reply to message #376445] Thu, 18 December 2008 02:49 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Here is the coding which i used to create a VIEW ..
CREATE VIEW vv
AS
   SELECT a.employeeid, a.employeename, a.leavetype, a.fromdate, a.todate,
     a.numberofdays, a.reason, b.employeeid, b.employeename, b.leavetype,
     b.fromdate, b.todate, b.numberofdays, b.reason
   FROM empaply a, aplybkup b
   WHERE a.employeeid = b.employeeid

  
b.employeeid,b.employeename,b.leavetype,b.fromdate,b.todate,b.numberofdays,b.reason  

*ERROR at line 3:
ORA-00957: duplicate column name


Thanks and Regards,
varosh
[EDITED by LF: formatted the CREATE VIEW script]

[Updated on: Thu, 18 December 2008 04:32] by Moderator

Report message to a moderator

Re: Union Operation in Forms [message #376679 is a reply to message #376640] Thu, 18 December 2008 04:35 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is obvious: you can't create a view with duplicate column names! Although tables 'empaply' and 'aplybkup' contain columns with the same names, you'll have to modify some of them.

For example:
CREATE VIEW vv AS
SELECT a.employeeid empid_a, 
       b.employeeid empid_b,
FROM 
  empaply a, aplybkup b
WHERE ...


Also, next time format the code. Using the [code] tags in order to preserve formatting is useless if the code itself is unformatted.

Report message to a moderator

Previous Topic: clear_block v/s required fields validation behaviour
Next Topic: cursor error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 03 17:53:19 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.