Re: Converted sql Query to Oracle - Database Discussion Boards

Hi
I have 2 tables Table1 and Table2 as below

Table1 Table2
col1 col2 col1 col21 col31 col22 col32
1 xx 1 A 0 AA null
2 yy 1 B 0 BB null
3 zz 1 C 0 CC null
1 0 X null XX
1 0 Y null YY

now on joining 2 tables, i want the output as below

Table1.Col1 Table1.col2 Table2.col21 Table2.col31 Table2.col22 Table2.col32
1 xx A X AA XX
1 XX B Y BB YY
1 XX C null CC null

Am not sure whether join is to be used or what? Is there any way i can get the output like this. Table2 will have many other rows takking the col1 of Table1 as primary key.

Regards
Naina

I can't understand your question,where are separated data of Table1 and Table2?

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

The first table Table1 has a primary key and the foreign key is the column in the second table Table2

Now table 2 has the data like this

foreignkey col1 col2 col3 col4
1 naina 1 null null
1 karri 2 null null
1 xxxxx 3 null null
1 null null aaaa 1
1 null null bbbb 2

now i need the output to be like this

primarykey col1 col2 col3 col4
1 naina 1 aaaa 1
1 karri 2 bbbb 2

1 xxxxx 3 null null

it means that the first columns with null data should be replaced with the last columns with data for that particular primary key value

Regards
Naina

What you posted as a table is not aligned well. A Join can be done this way

Table1

ID    Name         ID_Number
==============================
1     Vuyiswa      810945212121
2     nainakarri   817454212121

Table2

ID    Name         ID_Number       Date_Added
=============================================
1     Vuyiswa      810945212121    2009-09-09
2     nainakarri   867454212121    2009-09-10
2     James        847454212121    2009-09-11
2     Daniel       837454212121    2009-09-12

Now to run a Join for this two tables you do something like this

SELECT * FROM TABLE1 T1
INNER JOIN  TABLE2 T2
ON T1.ID_NUMBER = T2.ID_NUMBER

as you can see i have used a "*" that means you have to select what ever columns you want.

Hope this Helps

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

If table2 has a primary key of col1 of table1 what is that column called in table2, is it col21? If so, then this should do it:

select Table1.Col1, Table1.col2, Table2.col21, Table2.col31, Table2.col22, Table2.col32
from Table1 inner join Table2 on Table1.Col1 = Table2.Col21

You should really read up on joins though, this[^] explains them nicely.

Hi all.....

This is my oracle statement...while executing its giving an error:

21/29 PL/SQL: ORA-00923: FROM keyword not found where expected

please help me to solve this issue.. its urgent...
create or replace PROCEDURE PEAP_SelectEmpAppraiser
(
AppraiserID IN VARCHAR2 DEFAULT NULL,
Cur_RCT1 OUT GLOBALPKG.RCT1
)
AS
BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE PEAP_SelectEmployeesByApr';

INSERT INTO PEAP_SelectEmployeesByApr
SELECT emp_code AS EmployeeID,
emp_code || ' ' || l_name || ' ' || f_name AS SurNameFirstName
FROM emp_master
WHERE (Appraiser = PEAP_SelectEmpAppraiser.AppraiserID);
Insert into PEAP_SelectEmployeesByApr
SELECT emp_code as EmployeeID ,emp_code || ' ' || l_Name || ' ' ||
f_Name AS SurNameFirstName FROM emp_master
WHERE ( Reporting_To = PEAP_SelectEmpAppraiser.AppraiserID) and
isnull ( Appraiser , '') = '' and emp_code not in
( select EmployeeID from PEAP_SelectEmployeesByApr);
OPEN Cur_RCT1 FOR
SELECT DISTINCT
EmployeeID,
SurNameFirstName
FROM PEAP_SelectEmployeesByApr;
END;
/

please help me to resolve..its very urgent...

here is a nice tool to help you do it

http://www.swissql.com/products/sql-translator/sql-converter.html?adwords26s2&gclid=CLj-vvbQ2ZsCFR2dnAodXl8wAg[^]

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

hi,

Which i send is the converted query from sql to oracle using the SwissSQL tool...

I realy dont undestand what you saying Smile | :)

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

see I have a storedprocedure in SQL 2005 and using SwissSql tool i have migrated that SP to oracle 10g.

(that is the one which i have send)

now when i execute this in oracle its giving an error the below:

PL/SQL: ORA-00923: FROM keyword not found where expected

I really dont know Oracle Syntax i have never worked with Oracle, let me ask a Friend mybe he can help. Please hold

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

I have a friend who can Help you interactively. Go to gmail and invite "Vuyiswamb" i dont have Oracle and i dont use it, i use only Microsoft SQL Smile | :)

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

ok thanks 4 ur reply..

Cool

Waiting for you in Gmail Smile | :)

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

i cant use Gmail in ofc..Pls tel him to help through ths

(anyway i hv send u the query na)

The Problem i dont have Oracle and neither he and will depend on you to run queries. he will help you debug it.

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

ok den tel me steps..

the problem is :

WHERE ( Reporting_To = PEAP_SelectEmpAppraiser.AppraiserID) and
nvl( Appraiser , '') = '' and emp_code not in
( select EmployeeID collate database_default from PEAP_SelectEmployeesByApr);

here After the employeeid from keyword is missing its saying...

here 'collate database_default' is there in between of employeeid and from keyword..

the SQL query is working with this..

but i doubt is there any specific format to be used for 'collate database_default'?

but why we need "collate database_default " here ?

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

if i remove am getting another error like :

PL/SQL: ORA-00942: table or view does not exist

not able to find out the temporary table 'PEAP_SelectEmployeesByApr'

it is Difficult to help you.but your query is not complicated. try to run this separately before using them in sp, if they can run then they can run in a Sp.

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

ya it is working separately also...

Can you post separated parts here , i will tell you where you went wrong.

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Am new to oracle...can u just help me to convert the given below sql query to oracle?

create PROCEDURE [dbo].[PEAP_SelectEmployeesByAppraiser]
( @AppraiserID varchar(12) )
AS
SET NOCOUNT ON;
Create Table #PEAP_SelectEmployeesByAppraiser_All
(EmployeeID varchar(6) null, SurNameFirstName varchar(150) )
Insert into #PEAP_SelectEmployeesByAppraiser_All
SELECT emp_code AS EmployeeID, + emp_code + ' ' + l_name + ' ' + f_name AS SurNameFirstName FROM
emp_master WHERE (Appraiser = @AppraiserID)
Insert into #PEAP_SelectEmployeesByAppraiser_All
SELECT emp_code as EmployeeID, + emp_code + ' ' + l_Name + ' ' + f_Name AS SurNameFirstName FROM emp_master
WHERE (Reporting_To = @AppraiserID) and isnull(Appraiser,'') = ''
and emp_code not in (select employeeid collate database_default from #PEAP_SelectEmployeesByAppraiser_All)
Select Distinct EmployeeID, SurNameFirstName from #PEAP_SelectEmployeesByAppraiser_All

AS i said before i dont do oracle, am not familiar with Oracle.

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

ok..thanks 4 ur kind help.