using parameter inside select statement

how are you using the query?

e.g. it's possible to use parameters in sqlplus, but if you are using it some other way you might need a stored procedure.

by the way I hope all those tables have the exact same field definitions
and why are you unioning table4 twice? (line 9)

hi partlepaul
i'm using this query via sql developer
all tables having same definisions
table4 by mistale only
thanx

then the neat use of subquery factorization (with ... as) by angelIII should suit

sorry for all
i forget to tell that the above code is the body of creating a view

create view aaa as
select * from table1 where code=1512
union all
select * from table2 where code=1512
union all
select * from table3 where code=1512
union all
select * from table4 where code=1512

Select allOpen in new window

angell
where i should put the 1st raw of your code

yes correct
it works well
thanx

>>is the body of creating a view
then you do not need a "parameter"


usage


otherwise you do need a stored procedure or "table function"

partuell
sorry it is my mistake
this will not work , because the field name is different in the all tables
the parameter is necessary
but please accept my full thanx

>>this will not work , because the field name is different in the all tables
mmmmmmmmmmmmmmmmm

then how does the union work?
>>all tables having same definisions

I don't mind at all, happy that you have a solution, but:
a. I did ask how you were using it
b. if the tables were the same

I can only work with the information you provide.




usage


by the way, do NOT expect select * to be "dynamic" in a view, it is NOT dynamic.
and in fact you really should not use "select *" in production code

you correct
i apologize for my wrong information
i know how much you always helping me
but again , i like the parameter , why ? because the transactions is toooo much
with parameter , will save time of creating the view

I might use a pipelined table function in these cases, but I don't know the final purpose. not sure how a "with as" is going to assist inside that view.

ok
here i have to explain more
the purpose of creating this view is to make an audit result between 2 tables
all_trans   and   all_trans_month
here is my original syntax

create or replace view check_acc as
with p as ( select '1512' code from dual ) 
select tr_code,tran_no,user,sum(debit) h_deb,sum(credit) h_cred,0 g_deb,0 g_cred FROM all_trans t join p on t.code_trans = p.code
where tr_code not in(10,12,11,50,23,43,90,91,15,66)
group by tr_code,tran_no,user
union all
select tr_code,max(tran_no),user,sum(debit) h_deb,sum(credit) h_cred,0 g_deb,0 g_cred FROM all_trans j join p on j.code_trans = p.code
where  tr_code  in(10,12,11,50,23,43,90,91,15,66)
group by tr_code,user
union all
select '20',tran_no,' ',0,0,sum(debit),sum(credit) from all_trans_month k join p on k.acc_no = p.code
group by tran_no
order by 2;

Select allOpen in new window

then i have to derive the differences in the following query

select max(tr_code),tran_no,max(user),sum(h_deb),sum(h_cred),sum(g_deb),sum(g_cred) from check_acc
group by tran_no
having sum(h_deb)<>sum(g_deb) or sum(h_cred)<>sum(g_cred);

Select allOpen in new window

looks like a stored procedure to me, then you would get a "real parameter". It also appears like something being used for an reconciliation report.

anyway, best of luck.

shall i open a new question and you can smooth the code for me please

that is up to you; and while I would suggest either a table function or stored procedure for this I might not personally propose the code - there are many here you can. The choice of technique is really best determined by the "full story" i.e. the ultimate use of the output.

I would suggest providing (raw) sample data and expected results using real table and field names (i.e. don't try to simplify the details  {like "table1"} :)

also, if the final output is achieved through a reporting tool identify that tool in the question too (& it's also a good idea to mention the version of Oracle).

ok
i will post new question with real data & syntax
thanx

partuel
i posted new question
https://www.experts-exchange.com/questions/28219641/Reconsiling-transactions-Of-2-Tables.htmlhere

soryy all
what if i have more than one parameter
how will the syntax be ?