2 conditions in select statement

hi
i have this view

create or replace view all_acc_bal as
with p as ( select 2013 yr , 12 mn from dual ) 
select 
code_trans, -op_deb op_deb, -tr_deb tr_deb,  -t_deb t_deb, -op_cr op_cr, -tr_cr tr_cr,  -t_cr t_cr,  -Bal bal, 'Alpha' Syst
FROM h2002_acc_bal t join p on  t.year = p.yr and t.period_no= p.mn
union all
select 
acc_no, op_deb, tr_deb , op_deb+ tr_deb t_deb , op_cr , tr_cr , op_cr+ tr_cr t_cr,
(op_deb+tr_deb)-(op_cr+tr_cr) Bal , 'Intacc'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn;

Open in new window


then , this select

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from all_acc_bal
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
and code_trans not in('15000','15120','15550','20000','50000','11000')
order by sum(bal) desc;

Open in new window


1st of all : could i use direct select statament without creating the view every time to change parameter values ?
2nd : when i run the last select statement , it return records having values like 15000 , 11000
inspite i select not in
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
1) yes for this view query, you can use direct select statement.

create or replace view all_acc_bal as
with p as ( select 2013 yr , 12 mn from dual )
select
code_trans, -op_deb op_deb, -tr_deb tr_deb,  -t_deb t_deb, -op_cr op_cr, -tr_cr tr_cr,  -t_cr t_cr,  -Bal bal, 'Alpha' Syst
FROM h2002_acc_bal t join p on  t.year = p.yr and t.period_no= p.mn
union all
select
acc_no, op_deb, tr_deb , op_deb+ tr_deb t_deb , op_cr , tr_cr , op_cr+ tr_cr t_cr,
(op_deb+tr_deb)-(op_cr+tr_cr) Bal , 'Intacc'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn;

try the below :

select
code_trans, -op_deb op_deb, -tr_deb tr_deb,  -t_deb t_deb, -op_cr op_cr, -tr_cr tr_cr,  -t_cr t_cr,  -Bal bal, 'Alpha' Syst
FROM h2002_acc_bal t  where t.year = 2013 and t.period_no= 12
union all
select
acc_no, op_deb, tr_deb , op_deb+ tr_deb t_deb , op_cr , tr_cr , op_cr+ tr_cr t_cr,
(op_deb+tr_deb)-(op_cr+tr_cr) Bal , 'Intacc'
FROM acc_bal f where f.year = 2013 and f.period_no= 12;
Pratima PharandeCommented:
2. Try this

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from all_acc_bal
where code_trans not in('15000','15120','15550','20000','50000','11000')
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
order by sum(bal) desc;


1 . Yes you can use direct select .
Ivo StoykovCommented:
Hi

Generally you could use your create view statement in the FROM clause.
Then you could move outer where clause into FROM clause which is better.

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from (with p as ( select 2013 yr , 12 mn from dual ) 
select 
code_trans, -op_deb op_deb, -tr_deb tr_deb,  -t_deb t_deb, -op_cr op_cr, -tr_cr tr_cr,  -t_cr t_cr,  -Bal bal, 'Alpha' Syst
FROM h2002_acc_bal t join p on  t.year = p.yr and t.period_no= p.mn
where  code_trans not in('15000','15120','15550','20000','50000','11000') -- probably here
union all
select 
acc_no, op_deb, tr_deb , op_deb+ tr_deb t_deb , op_cr , tr_cr , op_cr+ tr_cr t_cr,
(op_deb+tr_deb)-(op_cr+tr_cr) Bal , 'Intacc'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn)
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
-- and code_trans not in('15000','15120','15550','20000','50000','11000')
order by sum(bal) desc;

Open in new window

HTH

Ivo Stoykov

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naveen KumarProduction Manager / Application Support ManagerCommented:
2) Try the below. I have moved that to the where clause.

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from all_acc_bal
where code_trans not in('15000','15120','15550','20000','50000','11000')
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
order by sum(bal) desc;

If this does not give you the expected results for some reason, then you can try the below as well. I have adjusted the paranthesis in the condition.

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from all_acc_bal
group by code_trans
having ( ( sum(bal) > = 1 or sum(bal) < = -1 )
and code_trans not in('15000','15120','15550','20000','50000','11000') )
order by sum(bal) desc;

Thanks,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.