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?
 
Ivo StoykovConnect With a Mentor Commented:
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
0
 
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;
0
 
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 .
0
 
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,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.