Home » SQL & PL/SQL » SQL & PL/SQL » Check and Process for ATLEAST 1 entry in table (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
Check and Process for ATLEAST 1 entry in table [message #330521] Mon, 30 June 2008 06:35 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi,

I need to check

IFthere is ATLEAST one record in a table
then do THIS_STMT1;
ELSE do THIS_STMT2;
END IF;

i am currently using this piece of code shown below (PLEASE LOOK AT PROJ_COUNT), but i have heard that this may have performance effect, so is there any other way to achieve this ?

Procedure P_LOAD_WORKBOOK
IS

    v_cnt_temp_ass_rn       PLS_INTEGER;        -- Counter for Assigned Activities
    PROJ_COUNT              PLS_INTEGER;
    v_error_text            VARCHAR2(2000);     -- Variable for error logging

BEGIN

    begin
            select count (distinct Project_Name) into PROJ_COUNT
            from ADM.DUP_MEMO_PROJECT
            where time_stamp = v_session_id
            order by Project_Name;
    end;



                    ADM.PKG_MEMO10.P_LOG_ERRORS(null,'BEGIN Loading workbook...',null,null,null,sysdate);

                    Excel_Document := ExcelDocumentType();
                    -- Open the document
                    Excel_Document.documentOpen;
                    -- Define Styles
                    Excel_Document.stylesOpen;
                    -- Include Default Style
                    Excel_Document.defaultStyle;
                    -- Add Custom Styles

                    /* Style for Column Header Row */
                    Excel_Document.createStyle(   p_style_id =>'ColumnHeader',
                                                p_font     =>'Times New Roman',
                                                p_ffamily  =>'Roman',
                                                p_fsize    =>'10',
                                                p_bold     =>'Y',
                                                p_underline =>'Single',
                                                p_align_horizontal=>'Center',
                                                p_align_vertical=>'Bottom');
                    -- Close Styles
                    Excel_Document.stylesClose;

    if (PROJ_COUNT) > 0
    then

        for i in (          select distinct Project_Name from ADM.DUP_MEMO_PROJECT
                            where time_stamp = v_session_id
                            order by Project_Name )
        loop
                    Excel_Document.worksheetOpen(i.Project_Name);

                    Excel_Document.defineColumn(p_index=>'1',p_width=>30);
                    Excel_Document.defineColumn(p_index=>'2',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'3',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'4',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'5',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'6',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'7',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'8',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'9',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'10',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'11',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'12',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'13',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'14',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'15',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'16',p_width=>44);

                    Excel_Document.rowOpen;

                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'PROJECT NAME');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'PROJECT DESCRIPTION');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY NAME');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY DESCRIPTION');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'DUPONT NUMBER');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'START DATE');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'END DATE');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY DUE DATE');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY STATUS');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY OBS');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'FUNCTIONAL MANAGER');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'RESPONSIBLE PERSON');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'RESOURCE NAME');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'TEST SUBST CODES');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'COA LINKS');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'SIF LINK');
                    Excel_Document.rowClose;
            for c1_rec in ( select * from ADM.DUP_MEMO_PROJECT
                            where time_stamp = v_session_id
                            and Project_Name = i.Project_Name
                            order by Memo_type)
            loop
                   Excel_Document.rowOpen;
                   Excel_Document.addCell(p_data=>c1_rec.Project_Name);
                   Excel_Document.addCell(p_data=>c1_rec.Project_Description);
                   Excel_Document.addCell(p_data=>c1_rec.Activity_Name);
                   Excel_Document.addCell(p_data=>c1_rec.Activity_Description);
                   Excel_Document.addCell(p_data=>c1_rec.Study_no);
                   Excel_Document.addCell(p_data=>c1_rec.Start_Date);
                   Excel_Document.addCell(p_data=>c1_rec.End_Date);
                   Excel_Document.addCell(p_data=>c1_rec.Reg_Due_Date);
                   Excel_Document.addCell(p_data=>c1_rec.act_res_status);
                   Excel_Document.addCell(p_data=>c1_rec.OBS);
                   Excel_Document.addCell(p_data=>c1_rec.FUNC_MGR);
                   Excel_Document.addCell(p_data=>c1_rec.UC04);
                   Excel_Document.addCell(p_data=>c1_rec.RN);
                   Excel_Document.addCell(p_data=>c1_rec.uc06);
                   Excel_Document.addCell(p_data=>c1_rec.coa_link);
                   Excel_Document.addCell(p_data=>c1_rec.sif_link);
                   Excel_Document.rowClose;

                    if c1_rec.act_res_status = 'ASSIGNED' then
                        v_cnt_temp_ass_rn                   := 1 + temp_ass_rn.count;
                        temp_ass_rn(v_cnt_temp_ass_rn).proj := c1_rec.project_name;
                        temp_ass_rn(v_cnt_temp_ass_rn).act  := c1_rec.activity_name;

                        ADM.PKG_MEMO10.P_LOG_ERRORS(null,
                        'Assigned Activity Info: '||temp_ass_rn(v_cnt_temp_ass_rn).proj||' '
                        ||temp_ass_rn(v_cnt_temp_ass_rn).act, null, null, null, sysdate);

                    else null;
                    end if;
            end loop;
                    Excel_Document.worksheetClose;
        end loop;
                    Excel_Document.documentClose;
    else
                    Excel_Document.worksheetOpen('NO PROJECTS');

                    Excel_Document.defineColumn(p_index=>'1',p_width=>30);
                    Excel_Document.defineColumn(p_index=>'2',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'3',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'4',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'5',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'6',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'7',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'8',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'9',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'10',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'11',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'12',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'13',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'14',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'15',p_width=>44);
                    Excel_Document.defineColumn(p_index=>'16',p_width=>44);

                    Excel_Document.rowOpen;

                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'PROJECT NAME');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'PROJECT DESCRIPTION');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY NAME');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY DESCRIPTION');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'DUPONT NUMBER');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'START DATE');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'END DATE');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY DUE DATE');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY STATUS');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'ACTIVITY OBS');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'FUNCTIONAL MANAGER');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'RESPONSIBLE PERSON');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'RESOURCE NAME');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'TEST SUBST CODES');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'COA LINKS');
                    Excel_Document.addCell(p_style=>'ColumnHeader',p_data=>'SIF LINK');
                    Excel_Document.rowClose;
                    Excel_Document.worksheetClose;
                    Excel_Document.documentClose;
    end if;

                ADM.PKG_MEMO10.P_LOG_ERRORS(null,'Finish Loading workbook',null,null,null,sysdate);
    Exception When Others then
        v_error_text := 'Error in P_LOAD_WORKBOOK: '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
END;
Re: Check and Process for ATLEAST 1 entry in table [message #330525 is a reply to message #330521] Mon, 30 June 2008 06:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you not notice that you have an awful lot of duplicate code?
Only stick the stuff that DIFFERS in the if-then-else part.

Why do you count the distinct rows? You don't care about duplicates. It will only force Oracle to do more work.
Even so, add a stopkey, so your count-query will return 0 or 1.
Re: Check and Process for ATLEAST 1 entry in table [message #330530 is a reply to message #330521] Mon, 30 June 2008 06:53 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
You can user ROWNUM = 1 in the WHERE condition of the SELECT query.

[Updated on: Mon, 30 June 2008 06:54]

Report message to a moderator

Re: Check and Process for ATLEAST 1 entry in table [message #330531 is a reply to message #330525] Mon, 30 June 2008 06:55 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
[quote
Why do you count the distinct rows? You don't care about duplicates. It will only force Oracle to do more work.
Even so, add a stopkey, so your count-query will return 0 or 1.
[/quote]

1. So, I guess
select count(*) into Proj_count
would do , right ?

2.
add a stopkey
Please can you throw some light about how to implement a
stopkey
Re: Check and Process for ATLEAST 1 entry in table [message #330533 is a reply to message #330530] Mon, 30 June 2008 06:57 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Quote:
You can user ROWNUM = 1 in the WHERE condition of the SELECT query.


Can you Please give an example for the same
Re: Check and Process for ATLEAST 1 entry in table [message #330536 is a reply to message #330533] Mon, 30 June 2008 07:00 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Is it that hard to find an example.

Try google perhaps ?

link
Re: Check and Process for ATLEAST 1 entry in table [message #330537 is a reply to message #330533] Mon, 30 June 2008 07:01 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
SELECT 1 
INTO PROJ_COUNT
FROM ADM.DUP_MEMO_PROJECT
WHERE time_stamp = v_session_id
AND ROWNUM = 1;
icon6.gif  Re: Check and Process for ATLEAST 1 entry in table [message #330543 is a reply to message #330536] Mon, 30 June 2008 07:04 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Thanks buddy Smile
Re: Check and Process for ATLEAST 1 entry in table [message #330545 is a reply to message #330543] Mon, 30 June 2008 07:11 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I may seem strange, but i got his error when i replaced this code

begin
            select count (distinct Project_Name) into PROJ_COUNT
            from ADM.DUP_MEMO_PROJECT
            where time_stamp = v_session_id
            order by Project_Name;
    end;


WITH THIS one suggested in the previous post

    begin
/*            select count (distinct Project_Name) into PROJ_COUNT
            from ADM.DUP_MEMO_PROJECT
            where time_stamp = v_session_id
            order by Project_Name;*/
            SELECT 1 INTO PROJ_COUNT
            FROM ADM.DUP_MEMO_PROJECT
            WHERE time_stamp = v_session_id
            AND ROWNUM = 1;
    end;


I got this error below


5:39:10 PM  Execution failed: ORA-30625: method dispatch on NULL SELF argument is disallowed
Re: Check and Process for ATLEAST 1 entry in table [message #330547 is a reply to message #330545] Mon, 30 June 2008 07:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select 1 into
and
select count(*) into

are very much different. The first one can raise a no_data_found exception; it will NOT select a zero if there are no rows found!
Re: Check and Process for ATLEAST 1 entry in table [message #330553 is a reply to message #330547] Mon, 30 June 2008 07:40 Go to previous message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Yeah,
I mentioned this because I wanted to tell that
NO_DATA_FOUND can be used to perform the statements in the ELSE part of the query.
BEGIN
   SELECT ...
   
   statements in the IF part.
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      statements in the ELSE part.
END;
Previous Topic: CONNECT BY in a graph
Next Topic: Select statement to return values within range
Goto Forum:
  


Current Time: Sat Dec 03 01:20:47 CST 2016

Total time taken to generate the page: 0.08909 seconds