Order of execution on a Cursor

[CIMTEET]

I have a cursor with 5 'OR' statements in the where clause. I am looking for 5 different data values. But I need to know that I am going to receive one specific one first. Say if field 5 of the 'OR' statements is the one I know I need first before I can evaluate any of the other ones. Or if field five does not exist I don't want to post process anything in that specific record return. The cursor is below. It works fine. All field names returned are part of one message. The message is id'd by the p_msg_id. I just want to know that if I need on of the five fields first, how would I go about arranging my cursor? I hope this makes sense.

CURSOR crs_Get_field_data(p_ses_id NUMBER, p_msg_id NUMBER,p_umi NUMBER,fp1 NUMBER,fp2 NUMBER,fp3 NUMBER,fp4 NUMBER,fp5 NUMBER) IS
SELECT vmd.field_name, vmd.field_data ,vmd.recorded_time ,vmd.unique_msg_id, vmd.position
FROM vw_message_data vmd
WHERE (vmd.position = fp1 OR vmd.position = fp2 OR vmd.position = fp3 OR vmd.position = fp4 OR vmd.position = fp5) AND vmd.unique_msg_id = p_umi AND vmd.msg_id = p_msg_id AND vmd.ses_id = p_ses_id
ORDER BY vmd.recorded_time,vmd.unique_msg_id,vmd.position

 

[SantaMufasa]

I hope this makes sense.

Actually, I need a clarifying example. Please post some sample data that clearly exemplify the different logic paths you want to follow, depending upon data values.


What does this mean:

I need to know that I am going to receive one specific one first. Say if field 5 of the 'OR' statements is the one I know I need first before I can evaluate any of the other ones. Or if field five does not exist I don't want to post process anything in that specific record return.

What does "first" mean? What does this mean: "if field five does not exist"?...NULL?...field is not defined?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

This is an extention to the external table problem we were working earlier.

Input text file will read on one line:
msg_name,field_name field_name field_name ,000

the view I am looking up have field that carry all these values. I said that because field_name is not actually a field_name in the table but a field_name in the message. Field_name is the field name in the table, along with msg_name and field_data. The code will take this input text and search for the field names needed based on the message name and session id that the user is prompted for when the code is executed. Now this part works. Its the next phase that birthed the question.

Input text:
msg_name,field_name=field_option field_name field_name,000

I only want the cursor to return a record if field_name is equal to field_option. In the for loop then I will check for the first field name and see if its equal to the field option and set a boolean that would allow or disallow the printing of the rest of the fields.

There are more than 1600 msg_names and God knows how many field_names with there enumerations. So this I guess is flexible automation at it finest or worst. I'll find out when I demo it Thursday. I was glad to hear from you again.

Greg

 

[CIMTEET]

Correction::
I only want the cursor to return a record if field_name is equal to field_option.

This statement is incorrect. I want to post process the code to where the first field returned is the one I used to set a boolean to decide whether or not I will print the rest of them.

 

[SantaMufasa]

CIMTEET, Perhaps it is my own foggy-headedness, but I still don't follow (without sample data...perhaps I'm very "right-brained"). But, despite my not understanding clearly, my impression is that since you are doing this in PL/SQL, you have the luxury of dealing procedurally with the logic, handling specific situations with "if...then...else...end if" structures rather than trying to rely fully on the "WHERE" logic of the cursor-defining SELECT statement.

Let us know how this turns out.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

Your right and hence my correction I posted under my reply to your reply uh er.. yeah. But I am returning 3 fields per message, which should only return one of them at a time in the for loop. Is there a way to guarantee which one is first?

 

[CIMTEET]

I suppose I could store them all in a record variable and then search for the equal...but how do I know its the last return from the cursor? Is there an end of buffer option when monitoring a cursor from a for loop?

 

[CIMTEET]

Do you mean sample code or data returned from the cursor?

 

[SantaMufasa]

Again, CIMTEET, since we have no specific data to which to refer, I currently have no response for your questions, "Is there a way to guarantee which one is first?" and "How do I know its the last return from the cursor?". My first reaction is that we guarantee which is first via some form of an "ORDER BY" clause.

If the data you are wanting to sort are on the same row instead of in the same column on different rows, then we can probably use some form of the GREATEST() or LEAST() functions.

But, again, I cannot offer a definitive answer until you post sample data, that are representative of all possible scenarios, along with specifications that describe how you want Oracle/SQL/PL/SQL to deal with those data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Turkbear]

Hi,
In addition please clarify what you mean by

returning 3 fields per message

What 'message' are you referring to?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CIMTEET]

Its difficult to explain. Then me think on that SantaMufasa. There are certain aspects I cannot expose but would need to, like the schema. I know that limits you in helping me. I apologize, if I can think of a way of bringing more to light I will.