oracle error help 2

[cLFlaVA]

Hi all, kinda new to in-depth oracle code (i'm a sql server guy). what am i missing here? i get this error:

encountered the symbol "THEN" when expecting one of the following: :=.(%;

        -- get approval date
        if (curRequest.request_type_id = 30134) then
            select t.last_update_date into dtApproveDate 
              from kwfl_step_transactions t join kwfl_workflow_steps s
                on t.workflow_step_id = s.workflow_step_id
             where t.instance_source_id = curRequest.request_id
               and t.instance_source_type_code = 'IR'
               and s.sort_order = 41
               and t.status = 'COMPLETE'
               and t.visible_result_value = 'Approve';

[red]        elseif (curRequest.request_type_id = 30026) then[/red]
        
            select t.last_update_date into dtApproveDate 
              from kwfl_step_transactions t join kwfl_workflow_steps s
                on t.workflow_step_id = s.workflow_step_id
             where t.instance_source_id = curRequest.request_id
               and t.instance_source_type_code = 'IR'
               and (s.sort_order = 6
                    and t.status = 'COMPLETE'
                    and t.visible_result_value = 'Complete')
                or (s.sort_order = 1
                    and t.status = 'COMPLETE'
                    and t.visible_result_value = 'Resolve');

        else
            select t.last_update_date into dtApproveDate 
              from kwfl_step_transactions t join kwfl_workflow_steps s
                on t.workflow_step_id = s.workflow_step_id
             where t.instance_source_id = curRequest.request_id
               and t.instance_source_type_code = 'IR'
               and s.sort_order = 10
               and t.status = 'COMPLETE'
               and t.visible_result_value = 'Yes';

        end if;

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[Thargy]

clf,

in the row you have highlighted in red, try elsif instead of elseif. You have a surplus 'e' in there.

Also, because you have wisely chosen the column names of "workflow_step_id" you can take advantage of the ANSI standard join... using... notation.

http://www.adp-gmbh.ch/ora/sql/join/ansi_inner_join.html

refers.

Welcome to the wonderful world of Oracle.

Regards

Tharg

Grinding away at things Oracular

 

[cLFlaVA]

thanks tharg, i will try this when i get back to work. why do you suggest using the "using" concept? is it faster?

thanks again.



*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[Thargy]

clfalve,

I'm not sure if it's faster.

I use it for several reasons

a) it's ANSI standard, so likely to be more portable
b) it's easier to read (less text on screen) and I therefore find it easier to understand
c) it's definitely much easier to read if you use multiple join columns, so again (IMHO) easier to understand.

Probably this is nothing more than a foible of mine, so please feel free to blow a raspberry in my direction.

Regards

Tharg.

Grinding away at things Oracular

 

[cLFlaVA]

Tharg,

thanks for your help so far. the elsif change and an invalid table name correction has gotten me far. now, the next issue i'm facing is this:

            select t.last_update_date into dtApproveDate
              from kwfl_step_transactions t join kwfl_workflow_steps s
                on t.workflow_step_id = s.workflow_step_id
             where t.instance_source_id = curRequest.request_id
               and t.instance_source_type_code = 'IR'
               and ((s.sort_order = 6
                    and t.status = 'COMPLETE'
                    and t.visible_result_value = 'Complete')
                or (s.sort_order = 1
                    and t.status = 'COMPLETE'
                    and t.visible_result_value = 'Resolve'));

i am getting an error when i run this, claiming "no data found". what i'd like is for a null value to be returned if there is no data.

do you know of a way i can achieve this?

thanks a lot for your help.

Cory



*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SantaMufasa]

If you folks don't mind my jumping into the adventure, the way that I handle that type of situation is to build a "Block in a Block":

Declare
    dtApproveDate date;
Begin
    ....
    Begin
        select t.last_update_date into dtApproveDate
              from kwfl_step_transactions t join kwfl_workflow_steps s
                on t.workflow_step_id = s.workflow_step_id
             where t.instance_source_id = curRequest.request_id
               and t.instance_source_type_code = 'IR'
               and ((s.sort_order = 6
                    and t.status = 'COMPLETE'
                    and t.visible_result_value = 'Complete')
                or (s.sort_order = 1
                    and t.status = 'COMPLETE'
                    and t.visible_result_value = 'Resolve'));
    Exception
        When no_data_found then
            dtApproveDate := null;
    End;
    <perhaps more code here>;
End;
/

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cLFlaVA]

thanks to both of you! i try to help on TT as much as i can (but not in this forum, obviously). it's great to know that i can rely on the site for help as much as other people often rely on me.

thanks again,

cory



*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[Thargy]

cory, Santa,

sorry for not posting a follow up, but I'm really under the hammer at work right now.

Santa, thanks for wading in and helping, much obliged.

T

Grinding away at things Oracular