none
OCI-22053: overflow error RRS feed

  • Question

  • I have a page with a gridview that calculates the number of hours worked.  It works great, except when someone works a split shift and the hours for one day is .50 hours or .25 hours. Anything over .75 hours does not cause the problem.

    Here is the grid code

    <asp:GridView ID="gvHours"
                                runat="server"
                                DataSourceID="hoursDataSource"
                                OnRowDataBound="gv_Hours_RowDataBound"
                                ShowFooter="true"
                                HeaderStyle-BackColor="#E2D0B0"
                                HeaderStyle-HorizontalAlign="Center"
                                AutoGenerateColumns="False"
                                RowStyle-ForeColor="#000000"
                                HeaderStyle-ForeColor="#000000"
                                BorderColor="Black"
                                HeaderStyle-Height="13px">
                                    <Columns>                                   
                                        <asp:TemplateField HeaderText="HOURS">                                       
                                            <ItemTemplate>
                                                <asp:Label ID="lblGVHours"
                                                runat="server"
                                                Text='<%# Bind("HOURS") %>'></asp:Label>
                                            </ItemTemplate>
                                            <ItemStyle Width="15px" />
                                            <FooterTemplate>
                                                <asp:Label ID="lblgvHoursTotal"
                                                runat="server" Text=""
                                                ForeColor="#000000"
                                                Font-Bold="true"></asp:Label>
                                            </FooterTemplate>
                                        </asp:TemplateField>
                                    </Columns>
                                    <RowStyle Font-Size="10px" ForeColor="Black" Height="25px" />
                                    <EmptyDataRowStyle Font-Size="10px" />
                                    <HeaderStyle Font-Size="10px" ForeColor="Black"
                                    BackColor="#E2D0B0" HorizontalAlign="Center" BorderStyle="None" Height="13px" />
                                    <EditRowStyle Font-Size="10px" ForeColor="Red" Height="25px" />
                                    </asp:GridView>

     

    Here is the select command for the grid

     

    hoursDataSource.SelectCommand = " " +
                        "SELECT " +
                        "HOURS, EMPLOYEE " +
                        "FROM  " +
                        "( " +
                        "SELECT (CASE " +
                            "WHEN v.HOURS < 0 " +
                                 "THEN v.HOURS + 24 " +
                            "ELSE v.HOURS " +
                            "END) HOURS, V.EMPLOYEE " +
                          "FROM " +
                          "( " +
                              "SELECT " +
                                "WORK_DATE S_D, " +
                                "EMPLOYEE, " +
                                "((TO_DATE(TO_CHAR(WORK_DATE,'MM/DD/YYYY') || ' ' || TIME_OUT_1, 'MM/DD/YYYY HH:MI AM')- " +
                                  "TO_DATE(TO_CHAR(WORK_DATE,'MM/DD/YYYY') || ' ' || TIME_IN_1, 'MM/DD/YYYY HH:MI AM')) * 24) + " +
                                  "((TO_DATE(TO_CHAR(WORK_DATE,'MM/DD/YYYY') || ' ' || TIME_OUT_2, 'MM/DD/YYYY HH:MI AM') - " +
                                  "TO_DATE(TO_CHAR(WORK_DATE,'MM/DD/YYYY') || ' ' || TIME_IN_2, 'MM/DD/YYYY HH:MI AM')) * 24) + " +
                                  "((TO_DATE(TO_CHAR(WORK_DATE,'MM/DD/YYYY') || ' ' || TIME_OUT_3, 'MM/DD/YYYY HH:MI AM') - " +
                                  "TO_DATE(TO_CHAR(WORK_DATE,'MM/DD/YYYY') || ' ' || TIME_IN_3, 'MM/DD/YYYY HH:MI AM')) * 24) + " +
                                  "((TO_DATE(TO_CHAR(WORK_DATE,'MM/DD/YYYY') || ' ' || TIME_OUT_4, 'MM/DD/YYYY HH:MI AM') - " +
                                  "TO_DATE(TO_CHAR(WORK_DATE,'MM/DD/YYYY') || ' ' || TIME_IN_4, 'MM/DD/YYYY HH:MI AM')) * 24) HOURS " +
                               "FROM FAMIS_TITO_UI   " +
                               "WHERE WORK_DATE BETWEEN " +
                                               ":STARTWEEK " +
                                           "AND :ENDWEEK  " +
                               "ORDER BY S_D " +
                          ") v " +
                        ")   " +
                        "WHERE EMPLOYEE = :EMPLOYEE ";

    the time_in_x, time_out_x, and work_date fields are varchar(2) fields and changing them to date fields will not happen, so the to_char(to_date is needed to convert them in the query.  This is an Oracle database, which does not allow CAST(     to datetime)

     

    Any suggestions

    Wednesday, May 12, 2010 6:33 PM

Answers