Re: Converted sql Query to Oracle - Database Discussion Boards

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.

Welcome

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,

I am working on a MVP project using WPF in C#. I have the task to trace every change in our Business Objects in the DataBase, that means, if someone changes a property of the Business Object and stores it to the DB, another table should log what the user has done. What is the easiest way to achieve this? Are there some events which are thrown when a table is updated? How can I handle this in my C# code, respective in the Business Object Class?

Any suggestions would be appreciated!

If it is schema changes you want to track then you would use a DDL trigger[^]

And if i only want to track changes in tables, I would have to use DML, right?

You would use normal triggers or if you are running SQL Server 2005 or later you could use the built-in auditing.

The problem is, I can't use the DB Triggers because we want to store which User (of our Application) made the changes, and the Triggers won't deliver this information.

You can get the user that made the changes by using the SUSER_SNAME() function and you can get the name of the machine that they are logged into by using the function HOST_NAME().

Thanks for the hint, but I need the user of our application, not the DB user Frown | :-(

The only way to audit changes by user of the application would be to modify your application.

Yes you are right.

Is there a possibility to get the information which field(s) were inserted/updated/deleted from the DataBase after executing the respective Stored Procedures?