Select Count(*) from within a Powerbuilder Script 
Author Message
 Select Count(*) from within a Powerbuilder Script

We are running a powerbuilder script and it is not completing correctly.  Can the
SELECT COUNT(*) be executed before the transaction is committed.   See code below:

Boolean lb_ok
Long ll_wr_num , ll_row , ll_fac_id , ll_rowcount
Int li_count
String ls_find
String ls_env_ind
String ls_func = "uf_update_wr_facility"
String ls_transition = "A",  ls_copy_only = "B", ls_no_edit = " "
String ls_cur_owner_ind , ls_cur_environ_ind

lb_ok = TRUE
ll_wr_num = Long( iw_parent.is_key )
ls_find = "cc_env_ind <> ' '"

ll_rowcount = iw_parent.dw_fac_master.RowCount( )

CHOOSE CASE ll_rowcount
CASE IS > 0
        ll_row = iw_parent.dw_fac_master.Find( ls_find, 1, ll_rowcount )
CASE 0
CASE -1
        f_system_error( 999, "RowCount failed", &
                this.ClassName(), "", ls_func, 0 )
END CHOOSE

DO WHILE ll_row > 0 AND lb_ok
        ll_fac_id  = &
                iw_parent.dw_fac_master.GetItemNumber(ll_row, "fac_id")
        ls_env_ind =&
                iw_parent.dw_fac_master.GetItemString(ll_row, "cc_env_ind")

        if IsNull( ll_wr_num ) or ll_wr_num = 0 then
                f_system_error ( 999, "Work Request Number is Null or 0.",&
                        iw_parent.ClassName() , This.ClassName(),
'uf_update_wr_facility', 0)
        end if

        if IsNull( ll_fac_id ) or ll_fac_id = 0 then
                f_system_error ( 999, "Fac Id is Null or 0.",&
                        iw_parent.ClassName() , This.ClassName(),
'uf_update_wr_facility', 0)
        end if

        if ls_env_ind <> ls_transition and &
                ls_env_ind <> ls_copy_only and &
                ls_env_ind <> ls_no_edit then
                f_system_error ( 999, "Invalid environment indicator. " + ls_env_ind
,&
                        iw_parent.ClassName() , This.ClassName(),
'uf_update_wr_facility', 0)
        end if

        SELECT  OWNER_IND,
                                ENVIRONMENT_IND
        INTO            :ls_cur_owner_ind ,
                        :ls_cur_environ_ind
        FROM            dbo.WORK_REQST_FACILITY
        WHERE   WORK_REQ_NUM = :ll_wr_num
                  AND   FAC_ID = :ll_fac_id
        USING SQLCA;

        if sqlca.SQLCode <> 0 then
                f_db_error ( sqlca.SQLCode , SQLCA.SQLerrText, This.ClassName(),&
                        'Select of cur owner ind failed.' )
        end if

        if IsNull( ls_cur_owner_ind ) then
                INSERT INTO dbo.WORK_REQST_FACILITY
                (       WORK_REQ_NUM,
                        FAC_ID,
                        OWNER_IND,
                        ENVIRONMENT_IND )
                VALUES
                (       :ll_wr_num,
                        :ll_fac_id,
                        "Y",
                        :ls_env_ind )
                USING SQLCA;    

                if sqlca.SQLCode <> 0 then
                        f_db_error ( sqlca.SQLCode , SQLCA.SQLerrText, &
                                This.ClassName(),       'Insert into Work Request.'
)
                end if

        elseif ( ls_cur_owner_ind = "N" ) then
                if ( Trim( ls_cur_environ_ind )  = Trim( ls_no_edit ) ) then
                        UPDATE dbo.WORK_REQST_FACILITY
                        SET     OWNER_IND = "Y",
                                        ENVIRONMENT_IND = :ls_env_ind
                        WHERE   WORK_REQ_NUM = :ll_wr_num
                                  AND   FAC_ID = :ll_fac_id
                        USING SQLCA;

                        if sqlca.SQLCode <> 0 then
                                f_db_error ( sqlca.SQLCode , SQLCA.SQLerrText, &
                                        This.ClassName() , 'Update of work request
facility.' )
                        end if

                else
                        f_system_error ( 999, "Invalid environment indicator. " +
ls_cur_environ_ind ,&
                                iw_parent.ClassName() , This.ClassName(),
'uf_update_wr_facility', 0)
                end if
        elseif ( ls_cur_owner_ind = "Y" ) then
                if (    ls_cur_environ_ind = ls_transition or&
                                ls_cur_environ_ind = ls_copy_only or &
                                Trim( ls_cur_environ_ind ) = Trim( ls_no_edit ) )
then
                        UPDATE dbo.WORK_REQST_FACILITY
                        SET     ENVIRONMENT_IND = :ls_env_ind
                        WHERE   WORK_REQ_NUM = :ll_wr_num
                          AND   FAC_ID = :ll_fac_id
                        USING SQLCA ;

                        if sqlca.SQLCode <> 0 then
                                f_db_error ( sqlca.SQLCode , SQLCA.SQLerrText, &
                                        This.ClassName() , 'Update of work request
facility.' )
                        end if

                else
                        f_system_error ( 999, "Invalid environment indicator. " +
ls_cur_owner_ind ,&
                                iw_parent.ClassName() , This.ClassName(),
'uf_update_wr_facility', 0)
                end if
        else
                f_system_error ( 999, "Invalid current owner indicator. " +
ls_env_ind ,&
                        iw_parent.ClassName() , This.ClassName(),
'uf_update_wr_facility', 0)
        end if

        SELECT COUNT(*)
        INTO :li_count
        FROM dbo.WORK_REQST_FACILITY
        WHERE   FAC_ID = :ll_fac_id
                  AND   OWNER_IND = "Y"
        USING SQLCA;

        if sqlca.SQLCode <> 0 then
                f_db_error ( sqlca.SQLCode , SQLCA.SQLerrText, &
                        This.ClassName() , 'Select count of work request facility.'
)
        end if



Fri, 17 Jul 1998 03:00:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. bcp from within a powerbuilder script/ or a stored procedure

2. Powerbuilder - counting script

3. SELECT COUNT(*) or SELECT COUNT(id)

4. select count(1) vs select count(*)

5. Re select count(1) vs select count(*)

6. DTS - Running a script from within a script

7. How to run a SQL script from within a SQL script

8. Executing sql scripts within a script (?)

9. SQL-DMO (Active script within job step) Problems with Script statement

10. Running Scripts from within Scripts

11. Calling Other SQL scripts from within a Script

12. using SELECT COUNT(*) to count distinct records


 
Powered by phpBB® Forum Software