Get AccountItems That Has Max Revenue For Each Account

Aug 22, 2010

I been working on this for days but I just cannot get it working...I have 3 tables:

1- Account
2- AccountItems
3- AccountInfo

Account has many AccountItems and has only 1 AccountInfo.So the point is to get the Account tems that has the max revenue for each Account. The field for revenue is AccountItems.Revenue..So here's what I did

Select Account.Name, Account.Telno, AccountItems.Item, AccountInfo.FirstName, AccountInfo.LastName
where Account.ID = AccountItems.ID
AND AccountItems.ID = AccountInfo.ID
AND ????
From Account, AccountInfo, AccountItems;

This is where I'm stucked on...I don't know what to do in order to grab the AccountItems.Item that has the MAX AccountItems.Revenue.

View 2 Replies


ADVERTISEMENT

Sys Account Locked And System Account Is Already Deleted

Jun 5, 2013

In event of sys account locked and system account is already deleted also no other user is having dba privilege, how to unlock the sys user??

View 5 Replies View Related

Server Utilities :: How To Write Control File To Load Data Into Revenue Table

Aug 16, 2011

I have a table revenue

create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)

and i have data in a file like below

Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678

i want to load it into the table in the following way.

Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.

View 2 Replies View Related

PL/SQL :: Tables Of Hr Account

Jun 1, 2013

here is my current Oracle version:

select * from v$version where banner like 'Oracle%'

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production   
1 row selected.

My need is to pass test cases based on Oracle XE tables (hr account). tell me where to get the structure of mentioned tables and its data (without downloading and installing Oracle XE if possible).

View 1 Replies View Related

Read Only Access Account For OEM?

Jun 3, 2013

What privilege needs to give the user for ready only access for OEM for limited instances not all instances.

Purpose: our application dba team just want monitor the specific databases that why they asked to create read only access account for OEM for limited databases.

View 2 Replies View Related

SQL & PL/SQL :: First Created Contact To Account?

Jun 3, 2011

I'm trying to write a query where I grab the first contact created of an account. I've tried everything but I can't get this to work.

Here is the normal query we used to get the contacts from an account:

select
con.row_id,
con.created,
con.fst_name,
con.last_name,
con.email_addr
from s_contact con
join s_org_ext org
on org.row_id = con.pr_dept_ou_id

I've tried different variation of aggregate functions, but its not working.

View 5 Replies View Related

SQL & PL/SQL :: Display All Account Code

Dec 8, 2012

I want to display all account Code but i required this account PFKHR01010101010001 to be fixed to have Credit_Amount.

My Structure is

Finance_TRansactions
------------------------------

D_No Char(12);
Account_Code Char(19);
D_Date Date;
DEbit_Amount Number;
Credit_Amount Number;
[code]...

ORA-01427: single-row subquery returns more than one row

View 5 Replies View Related

Oracle Linux Account

Oct 22, 2012

I have installed oracle enterprise linux 6.2 on my laptop, and I installed the database 11gR2 on it, know the user account I used to log in with (os level) is not accessible any more :

[URL].........

if I remove the account and recreate it does it will cause problems to the database?

View 5 Replies View Related

SQL & PL/SQL :: Hierarchical Query For Chart Of Account

Aug 19, 2013

I want Hierarchical Query..I have Table of chart of account

CREATE TABLE COA
(
ACCOUNT_CODE CHAR(19),
ACCOUNT_TITLE VARCHAR2(70),
ACC_TYPE Char(1),
PARENT_CODE CHAR(19)
)
[code]....

View 2 Replies View Related

Server Administration :: DBMS_JOB Using Sys Account

Jun 17, 2010

I have to change the execution of a job, I have the sys access.The job is in another user, say scott.how to use dbms_change using sys account to change the execution of a job in Scott user..

View 9 Replies View Related

Security :: Oracle Account Limitation

Oct 25, 2011

there is urge to limit account usability in oracle.

let's say i have request to create user HR1, and additional information is that this account should be available for using till 31-dec-2011. is there possibility to set this validity during creation?

View 1 Replies View Related

How User Xyz Account Expired Within A Month

Aug 22, 2013

Created user xyz on July 20,2013 with defualt profile (oracle version 11.2.0) and PASSWORD_LIFE_TIME is 180 for default profile. Immediate my friend have assigned new profile APSC to user xyz where profile APSC has PASSWORD_LIFE_TIME = UNLIMITED.

we surprised, user account has expired within a month. How user xyz account can expired within a month????

Note: User account status was open when account created and assigned to profile APSC.

View 4 Replies View Related

Temporarily Lock Account After So Many Bad Logins?

Jan 19, 2009

I'm not a DBA, nor do i play one on TV.

Is there a way to only temporarily lock an account after so many bad logins? I'm being told by my DBA that there is not, and I'm just shocked that Oracle wouldn't build in that functionality.

View 4 Replies View Related

SQL & PL/SQL :: Create Trigger On (account) Table?

Nov 23, 2008

I gave this a shot on my own, and failed, so I am putting the question to you:

I am looking to create a trigger on the "Account" table

CREATE TABLE Account(Acct_no number, BillAmt number, OpenDate Date, SSN number,
Primary Key (Acct_no),
Foreign Key(SSN) references ResponsibleParty(SSN));

which will update the BillAmt field whenever one of the account's respective records is inserted/updated/deleted from the following tables:

CREATE TABLE Plan(Plan_id number, Cost number, Minutes number,
primary key(Plan_id));
CREATE TABLE Feature(Feature_id number, Description varchar(20), Cost number,
primary key(Feature_id));

These tables are linked through the following table:

CREATE TABLE Phone_Line(MTN number, cStart Date, cEnd Date,
rDate Date, Status char, ESN number, Acct_no number, Plan_id number,
primary key(MTN),
foreign key(ESN) references Equiptment(ESN), foreign key( Acct_no) references Account(Acct_no),
foreign key(Plan_id) references Plan(Plan_id));

View 26 Replies View Related

User Account Will Be Locked In A Period Of One Day After

Sep 16, 2013

"ALTER PROFILE profile_name LIMITPASSWORD_LOCK_TIME 1"means the user account will be locked in a period of one day after FAILED_LOGIN_ATTEMPTS has gone over limit.How to set PASSWORD_LOCK_TIME less or more than one day (like not full days)?

View 2 Replies View Related

Expired Account Need To Open Without Password Reset?

Aug 14, 2013

Account XYZ has expired and we don't know the password of account xyz and don't want reset the password also. How can open account XYZ without change the password?

View 1 Replies View Related

Know User Account Locked Time In HH:MM:SS Format?

May 30, 2011

How we know a user account locked time in HH:MM:SS format ?

View 1 Replies View Related

Security :: User Account Locked Error

Oct 3, 2012

we have a prod application which use oracle database 11g as backend and .NET technology as front end . There is a user TESTUSER in database.

Issue is that we can login in to the application, but when we try to login in database through toad or Db visualizer then it gives an error of 'user account locked'.

View 5 Replies View Related

Security :: See Who Or When Password Was Changed For SYS Or SYSTEM Account?

Nov 10, 2010

Is there a way to see who or when password was changed for SYS or SYSTEM account?

View 4 Replies View Related

SQL & PL/SQL :: Hierarchical Query To Display Chart Of Account?

Apr 25, 2013

I want Hierarical query to display my Chart_Of_Account. I want to make a tree Form in 6i error i am getting is connect by nocycle prior account_code=parent_code
*
ERROR at line 4:
ORA-00920: invalid relational operator

Table

Create Table Chart_Of_Account (Account_Code Char(19), Account_Title varchar2(70), Parent_Code Char(19))
insert into Chart_Of_Account ('DGHOA01010101000001','TEST','DGHOA01010100000000')
insert into Chart_Of_Account ('DGHOA01010101000002','TEST1','DGHOA01010100000000')
insert into Chart_Of_Account ('DGHOA01010101000003','TEST2','DGHOA01010100000000')

select -1,level,account_code||' - '||ACCOUNT_Title,'NULL',to_char(ACCOUNT_CODE)
connect_by_iscycle from chart_of_account
start with Substr(account_code,13,7)='0000000'
connect by nocycle prior account_code=parent_code

View 3 Replies View Related

Database User Account Lock And Expire

Jun 28, 2012

I have altered an user account and set the account status as expired.When I tried to connect with that user, oracle prompted for changing password.

But, after I changed the password, I got an error message as ora-01017 invalid username/password logon denied password unchangedWhat is the cause and solution for it? I am using 11g database and 10g client.

View 15 Replies View Related

XE :: Oracle 10G Express Edition Account Locked

Aug 16, 2012

My Oracle 10G Expression Edition Account locked gets locked, where to raise the request to unlocking my account.as my user name:system

View 4 Replies View Related

How To Find Length Of User Account Password In A Database

Jun 13, 2013

how to find length of the user account password in a database?

View 1 Replies View Related

Mandatory To Create A Local System Administrator Account?

May 25, 2012

Is it possible to install Oracle SOA 11 in a Windows2008R2 as a power user or it is mandatory to create a Local system administrator account?

View 2 Replies View Related

SQL & PL/SQL :: Unable To Send Mails Through Oracle 9i (using Gmail Account)?

Feb 23, 2007

I am succeeded to send mail (through my comapany mail server) using following pl/sql. But, i am unable to send mails using the same coding, when i connected to smtp.gmail.com. i received the following error :

ERROR at line 1:
ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first
y78sm463148pyg
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98

[code]...

Then I received the following error message:
ERROR at line 1:
ORA-29278: SMTP transient error: 454 TLS not available due to temporary reason
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96

[code]...

my demo_mail package body coded as

FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection

[code]...

View 2 Replies View Related

Security :: ORA-20007 - Change User Account Password

Jul 16, 2011

When i try to change the user account password, i get following error.

alter user bala
identified by Ju4hlsd2;

ERROR at line 1:
ORA-20178: ORA-20176: ORA-28003: password verification for the specified password failed
ORA-20007: Password cannot consist of sequences of 3+ characters from the userid

how to set the password based on the error.

View 5 Replies View Related

Getting Oracle Odbc Ora-28000 Account Locked Error?

Sep 15, 2012

I am getting Oracle odbc ora-28000 account locked error

View 1 Replies View Related

Application Express :: Forgot Password / Account Locked?

Sep 29, 2013

I am trying to login to the web version of application express [URL]... and get a message that my login is incorrect.  I have verified that the ID and workspace are correct, but I do not remember what i set my password to the first time i logged in (account was created by my instructor).  There is no "forgot password" or "reset password" link that shows on the page. 

View 0 Replies View Related

Trigger - Delete Account Through A Java Interface Using JDBC - Get Error?

Dec 10, 2012

Here are the relevant tables to the triggers I'm having trouble with.

create table profile
(
userID number(10),
uname varchar2(64),
email varchar2(32),
password varchar2(32),
date_of_birth date,

[code]...

Here's the triggers I currently have that doesn't work.

create or replace trigger DropAccount
after delete on profile
FOR EACH ROW
BEGIN
delete from groupMembership where (userID = :old.userID);
END;

The DropAccount trigger's purpose is to delete all the records from groupMembership that contains the userID of the person deleting his/her own account. It appears to work when I run the following statements in sqlplus:

delete from profile where (userID = '1');
select * from groupMembership where (userID = '1');

But when I try to delete an account through a java interface using JDBC I get the following error:

Machine Error: java.sql.SQLException: ORA-04091: table *****.GROUPMEMBERSHIP is mutating, trigger/function may not see it
ORA-06512: at "*****.DROPACCOUNT", line 2
ORA-04088: error during execution of trigger '*****.DROPACCOUNT'

The java function basically just executes a delete statement.

View 1 Replies View Related

Server Administration :: Generate Statspack Report Without Perfstat Account?

Feb 7, 2013

I have only select_catalog_role in my database.

Is it possible to generate statspack report without having access to perfstat account?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved