change view into stored proc w/ parameter - HOW?

[LMCRYER]

I am using Oracle81. I have the following view which pulls in all of the data that we need:

CREATE OR REPLACE FORCE VIEW MRPOWNER.CYCLE_COUNT_3
(TRANSACTION_CODE, SUBCODE, PART_NBR, PART_DESC, PART_CLS4,
ABC_CODE, LAST_COUNT_DATE, STATUS, CINCOM_QTY_ON_HAND)
AS
SELECT
'INVADJ' AS transaction_code,
'B' AS subcode,
vw_part.part_nbr,
vw_part.part_desc,
vw_part.part_cls4,
vw_part.abc_code,
vw_invd.last_count_date,
vw_invd.status,
vw_invd.CINCOM_QTY_ON_HAND
FROM
CSIOWNER.part_elk vw_part,
mrpowner.vw_invd_cycle_061004 vw_invd
WHERE
vw_invd.part_nbr = vw_part.part_nbr;

Now what I need to do is make this into a procedure so that it can have a parameter. I would like to be able to query it by using Crystal Reports and pass the value for the parameter back to the procedure to further define the WHERE statement like this:

WHERE (LAST_COUNT_DATE < ?)

I know how to do this on the SQL side but dont know how to make this work in Oracle. Can someone please guide me?




LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[SantaMufasa]

Lisa,

I'm certain we can help you, but first, could you help me better understand a couple of issues:
1) If you VIEW is doing precisely what you want, why are you wanting to turn it into a procedure? What is there about Crystal Reports that prevents your reference to/use of a VIEW, but a procedure is fine?
2) Since Oracle procedure parameters typically deal with one-dimensional parameter lists (versus the behaviour of a VIEW which typically produces a two-dimensional result set), the two (PROCEDURE versus VIEW) do not, at first glance appear to be "interchangeable" from a results perspective. Could you please confirm what you want the procedure to do that would resemble a VIEW?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:21 (16Jun04) UTC (aka "GMT" and "Zulu"), 16:21 (16Jun04) Mountain Time)

 

[carp]

Like SantaMufasa, I am puzzled by the request. Why not just query the view and include the WHERE clause?

While a procedure can be created to replicate the behavior of the view, it's going to be ugly and probably slower (not to mention harder to interface with!).

 

[LMCRYER]

Well, I was thinking that a view already has a WHERE statement built into it. When Crystal reports taps the view, it THEN applies the WHERE statement in CRYSTAL (it doesnt push the criteria back to the server, and restrict the result set coming over from the server side - Crystal actually taps the view, and then does the "heavy work" of reducing the dataset further within its own processing.

I've done this a ton of times in SQL, and it works very well - if you build in a parameter in the stored procedure, Crystal recognizes it and will allow the user to enter a value which is then passed to the stored proc at runtime, therefor keeping the "heavy work" on the SQL server and not on the Crystal side -- which has a huge impact on speed and performance.

I'm having a difficult time moving from the regular SQL mindset over to ORACLE...I really just want to be able to have some kind of placeholder declared, and allow the users to pass the value they want and sneak it up into the WHERE statement. I am hoping this will further reduce the performance / speed.

Likewise, this data set will be tapped from within VB, and again will need to have a parameter entered.

If a stored procedure is not a good option, any ideas would be most welcome!

FYI - Crystal Reports Dev v9, Crystal Enterprise v9, Oracle 8i, VB6

Help!?!?



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[LMCRYER]

A little more background info is in order. We are in Maryland, our Oracle server is at the corporate site in Utah. All of our taps to Oracle go over the wire - meaning they're slowed down a bit.

The goal is to be able to do as much as we possiby can on the server and only bring over what we really need (reduce the data set, make the tap easier, reduce traffic, and keep it clean). The problem is that we need to be able to tell it what we want the cutoff for the "last count date" to be at the time of the tap.

In SQL you declare it, and use it in the WHERE statement on the stored proc, and then at runtime tap it like this:
("BLAH BLAH BLAH", '01-JAN-1900') (I dont remember the exact syntax on it and dont have it here, but does this make sense?)

Please tell me what other info I should provide.
Thanks!


LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[sem]

You should utilize REF CURSOR:

create package CYCLE_COUNT_3_pack
is
type rc is REF CURSOR;
procedure getData(pLastCountDate in date, c out rc);
end;

create package body CYCLE_COUNT_3_pack
is
procedure getData(pLastCountDate in date, c out rc)
is
begin
open c for select * from CYCLE_COUNT_3 where LAST_COUNT_DATE < pLastCountDate;
end;
end;


Regards, Dima

http://seminihin.narod.ru

 

[carp]

While you can have a WHERE clause in the query that defines your view, you can also impose an additional WHERE clause in your query. Oracle will merge the queries to create a composite query. In other words, if you have a view:

CREATE OR REPLACE VIEW travel_expenses_FY04 AS
SELECT * FROM travel_expenses
WHERE to_char(expense_date,'YYYY') = '2004';

and you want to generate a report on your expenses, there's nothing to stop you from sending in a query

SELECT * FROM travel_expenses_FY04
WHERE traveler = 'LMCRYER';

Under the hood, Oracle will merge the queries and produce the dataset from

SELECT * FROM travel_expenses
WHERE to_char(expense_date,'YYYY') = '2004'
  AND traveler = 'LMCRYER';

On the other hand, if you still want to do this as a stored procedure, then I would recommend using Dima's approach.

 

[sem]

Carp, I didn't work with Crystal, but heard that it doesn't allow creating queries at all: its GUI designer allows only to choose table/view/procedure. From these 3 only procedure can be parametrized.

Regards, Dima

http://seminihin.narod.ru

 

[Turkbear]

Hi,
Nope..Crystal can and does use parameters and selection criteria for Views, Tables and SPs(that have REF CURSORS).

By using Crystal's View Sql command, you can see exactly what is being passed to Oracle and what will be handled 'locally' by Crystal..In 8.5 and earlier, you could even edit that Sql to refine it..In v9 and above you can create the report directly from a Sql statement.

 

[LMCRYER]

Yes, you're right that Crystal can do its own additions to the WHERE statement, and so can VB, but its all in when the event happens, I guess. I want it all to happen on the oracle side, so that theres less coming over which would mean it will be faster, is this correct?

More specifically - will doing it in a procedure as stated above by CARP be faster than doing an additional WHERE statement on the VB or Crystal sides ?

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[carp]

But a query on a view DOES all occur on the Oracle side doesn't it? As far as I know, all your interface is doing is creating the query (e.g., "SELECT * FROM my_view WHERE some_condition_exists"). So you would just be passing the character string that represents the query (typically a few hundred bytes at most). Oracle accepts the query, merges it with the view, processes the request, and returns the data. Crystal's job is to send the query and format/present the results.

 

[LMCRYER]

I didnt think it worked that way, becuase of the way Crystal processes data and stuff, but I am going to test it and find out! I'll let ya'll know for future use. Thanks, guys!

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[SantaMufasa]

By the way, Lisa, as one last point of order in Oracle: It has to do with your signature line:

select * from Management where proj_mgmt_skills <> NULL

0 records returned.

No matter what your data look like, this code (In Oracle) will ALWAYS return 0 rows. If you change the code to:

select * from Management where proj_mgmt_skills [b]=[/b] NULL

0 records returned.

...you will still ALWAYS experience "0 rows selected."

If you want to check for NULL contents of any field, in Oracle, you must use the relational expressions "IS NULL" or "IS NOT NULL" to return a result set whose count > 0.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:51 (17Jun04) UTC (aka "GMT" and "Zulu"), 11:51 (17Jun04) Mountain Time)

 

[BJCooperIT]

Leave it to you to debug a signature Dave!

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[Turkbear]

Hi,
NULLs in Oracle are almost metaphysical..

They are not equal to anything, not even themselves, and are not <> to anything either...

The best description of NULL I have found is that it means 'undeterminable', so something either is or is not determinable, hence the need to use IS and IS NOT in the test.

Just some Oracle musings...

 

[SantaMufasa]

I'm sorry to speak up again, but to concur with Turkbear, I'll offer my "musing" on Oracle's treatment of NULLs: Similar to Turkbear's "undeterminable", I say "unknown". Now,
Q. Is an UNKNOWN value = to 'A'?
A. Unknown; neither TRUE nor FALSE.

Q. Is an UNKNOWN value <> to 'A'?
A. Unknown; neither TRUE nor FALSE.

Q. Is an UNKNOWN value = to an UNKNOWN value?
A. Unknown; neither TRUE nor FALSE.

Q. Is an UNKNOWN value <> to an UNKNOWN value?
A. Unknown; neither TRUE nor FALSE.

And here is the clincher:

Q. Is an UNKNOWN value an UNKNOWN value?
A. True !!! (which is why we ask, "...WHERE <exp> IS NULL...", meaning, "IS <exp> an UNKNOWN value?")

Okay, everyone, back to work.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:20 (17Jun04) UTC (aka "GMT" and "Zulu"), 12:20 (17Jun04) Mountain Time)

 

[carp]

It's Data Nirvana!

But back to the topic at hand:
Lisa -
Are you saying that Crystal bypasses the Oracle server and goes to the tables? Or that Crystal figures out the execution plan? Or what? Because the only way I can see for Crystal to get data out of Oracle is to send in a query and await a response.

But - like Dima - I don't work with Crystal, so I am completely open to an educational experience!

 

[LMCRYER]

First things first - I am NEVER going to learn Oracle, I am going to have to stay in my nice little familiar SQL world forever, I guess!

Secondly - Crystal works differently. If you know how to write SQL correctly, it passes the SELECT statement over and it waits for a reply, and returns data - end of story.

BUT - if you add in anything that is not SQL (by using Crystal syntax in your selection criteria) what happens is that the data goes thorugh 2 "passes" - first it sends off as much of the query as it can and pushes it to the server, which then spits out a dataset. When THAT hits Crystal, it does further processing and reading of data to get to what you really want - which is not good!

Thats what I'm trying to avoid, by just putting the whole darn select statement inside the original query...

I have it working in SQL but those tables are DTS'd in overnight, and so the data in there is always one day behind...not nice, but do-able til I figure this thing out!

PS. -- Another little weird quirk in Oracle (for me, anyway) - why the HECK cant I do a NOT IN (select...subquery? OH NO, I had to do a != -- totally messed up my world!!





LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[SantaMufasa]

Lisa,

"NOT IN" works fine in Oracle:

select 'Lisa, NOT IN works fine' from dual where 1 not in (select 2 from dual);

Lisa, NOT IN works fine

1 row selected.

What is your code not doing that you expect it to do?

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:46 (17Jun04) UTC (aka "GMT" and "Zulu"), 14:46 (17Jun04) Mountain Time)

 

[LMCRYER]

This is what I had to do to get it to work -
all it needs to do is make sure that the COST_ELEM is NOT IN this group.
PLGR.COST_ELEM !='2FEE' AND
PLGR.COST_ELEM !='2FEN' AND
PLGR.COST_ELEM !='2FEP' AND
PLGR.COST_ELEM !='PCOM'

(muttering) I need some serious Oracle training - can yall recommend a good trainer? And we need a consultant to get in here too and put out some of the immediate fires!



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned