multi parameter

with p as ( select '01-aug-13' st_d, '31-aug-13' en_d , '2001'  Str from dual ) 
select
cr_count.cnt_item, cr_track.ti_name_a, cr_uom.uom_code,
 open_bal(p.str,cr_count.cnt_item,p.st_d) Open_Bal,
 sum(cr_count.cnt_trin) Tr_In, Sum(cr_count.cnt_rcv) Recv ,
       Sum(cr_count.cnt_dsd) DSD , Sum(cr_count.cnt_prod) Prod , Sum(cr_count.cnt_void) Void ,
            
       Sum(cr_count.cnt_trout) Tr_Out , Sum(cr_count.cnt_waste) Waste , Sum(cr_count.cnt_usage) Usage , 
       Sum(cr_count.cnt_cons) Cons , Sum(cr_count.cnt_sold) Sold ,
 (end_bal(p.str,cr_count.cnt_item,p.en_d)) End_Bal,
     
 (( open_bal(p.str,cr_count.cnt_item,p.st_d) ) +
 sum(cr_count.cnt_trin) + Sum(cr_count.cnt_rcv) + Sum(cr_count.cnt_dsd) + Sum(cr_count.cnt_prod) + Sum(cr_count.cnt_void)) Tot_In ,
 ( Sum(cr_count.cnt_trout) + Sum(cr_count.cnt_waste) + Sum(cr_count.cnt_usage)+Sum(cr_count.cnt_cons) + Sum(cr_count.cnt_sold) ) Tot_Out,
 
 (( open_bal(p.str,cr_count.cnt_item,p.st_d) ) +
 sum(cr_count.cnt_trin) + Sum(cr_count.cnt_rcv) + Sum(cr_count.cnt_dsd) + Sum(cr_count.cnt_prod) + Sum(cr_count.cnt_void)) -
 ( Sum(cr_count.cnt_trout) + Sum(cr_count.cnt_waste) + Sum(cr_count.cnt_usage)+Sum(cr_count.cnt_cons) + Sum(cr_count.cnt_sold) ) Comp_Close ,
 
 (end_bal(p.str,cr_count.cnt_item,p.en_d))-
 ((( open_bal(p.str,cr_count.cnt_item,p.st_d) ) +
 sum(cr_count.cnt_trin) + Sum(cr_count.cnt_rcv) + Sum(cr_count.cnt_dsd) + Sum(cr_count.cnt_prod) + Sum(cr_count.cnt_void)) -
 ( Sum(cr_count.cnt_trout) + Sum(cr_count.cnt_waste) + Sum(cr_count.cnt_usage)+Sum(cr_count.cnt_cons) + Sum(cr_count.cnt_sold) )) Short_Over
     
FROM cr_count, cr_track, cr_uom
WHERE (    (cr_track.ti_id = cr_count.cnt_item)
        AND (cr_uom.uom_id = cr_track.ti_puom)
      ) 
      and cnt_date BETWEEN p.st_d and p.en_d
      --and cnt_item ='20001'
      and cnt_str_id = p.str
      group by cr_count.cnt_item,cr_track.ti_name_a, cr_uom.uom_code;
      

Open in new window


what is the suitable use of the 3 parameteres above ?
it gives error that : p.str not defined , and the other 2
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.

slightwv (䄆 Netminder) Commented:
CTE doesn't work that way.

The "WITH P", more or less defines a VIEW named P.  It doesn't define variables.

So, you need to use P just like a view/table and select FROM it:
with p as ( select '01-aug-13' st_d, '31-aug-13' en_d , '2001'  Str from dual )
select st_d, en_d, str from p;

I suppose you could join P with the rest of your query but I really don't think this is what you want.

I would just use the strings in the select.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
"p" is not referenced in the from clause...

Apart from that, could this be a duplicate from this: http://www.experts-exchange.com/Database/Oracle/Q_28241600.html
0
NiceMan331Author Commented:
but they are too strings
i should every time change them in the body of the statement ?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NiceMan331Author Commented:
yes , alex
same , but different question
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
How and where will this statement be called?!
0
NiceMan331Author Commented:
it will be direct select statement  , i didn't create view for it
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
then, why don't you use parameters?!

with p as ( select to_date('&p_st_d', 'dd-mm-yy') st_d, to_date('&p_en_d', 'dd-mm-yy') en_d , '&p_str'  Str from dual ) 

Open in new window

...
0
slightwv (䄆 Netminder) Commented:
>>then, why don't you use parameters?!

That still won't work.  You still need to join 'P' with the rest of the select.
0
slightwv (䄆 Netminder) Commented:
Instead of trying to debug/understand the big picture all at once, break it down into bite-size chunks.

In the test case below, I created a table, tab1, with a single column, col1.

This represents your big select against multiple tables with all your calculations.

Then I use CTE like you did to return a list of 'parameters' but they are just column values like any other.

Then it is a simple matter to join the 'P' view with the massive select.

In my example, I use p.st_d to look up a matching row in tab1.

Once you understand this, you should be able to integrate it into your bigger select.

drop table tab1 purge;
create table tab1(col1 date);

insert into tab1 values(to_date('01-aug-13','DD-mon-YY'));
insert into tab1 values(to_date('02-aug-13','DD-mon-YY'));
commit;


with p as ( select '01-aug-13' st_d from dual)
select * from tab1,p where col1 = to_date(st_d,'DD-mon-YY')
/

Open in new window

0

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
Alexander Eßer [Alex140181]Software DeveloperCommented:
That still won't work.  You still need to join 'P' with the rest of the select.

I didn't want to post the rest of the original statement since it would have been redundant. Sure, you'd have to join "p" with the other tables ;-)

My point was to get rid of the HC'ed literals...
0
NiceMan331Author Commented:
yes
it is ok
0
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.