"No more spool space error"

Database
N/A

"No more spool space error"

Hello friends,

I have a query that is running longer than expected and is eventually spooling out. The query below is yielding results when passed on with a value (ex: '2015-11-26') instead of using the column "ORD_DT". But when i revert it back to column ORD_DT the query is taking forever to return results. Any idea as to what might have gone wrong?

SEL A.ORD_PLACE_DTTM, A.VEND_PROD_ORD_KY, CAST(A.ORD_PLACE_DTTM AS DATE) AS ORD_DT,

CASE WHEN SC1.DAY_OF_WEEK NOT IN ('1', '7') AND VC1.HOL_IND IS NULL THEN 

     CASE WHEN SUBSTR(CAST(A.ORD_PLACE_DTTM AS VARCHAR(30)),12,2) BETWEEN 07 AND 16 THEN CAST(A.ORD_PLACE_DTTM AS VARCHAR(30)) 

                        WHEN SUBSTR(CAST(A.ORD_PLACE_DTTM AS VARCHAR(30)),12,2) <07 THEN CAST(CAST(A.ORD_PLACE_DTTM AS DATE) AS VARCHAR(30))||' '|| '07:00:00.000000'

                         ELSE (SELECT CAST (CAST(MIN(B.CALENDAR_DATE) AS DATE) AS VARCHAR(30))||' '|| '07:00:00.000000' FROM

                                       (SELECT SC.CALENDAR_DATE, SC.DAY_OF_WEEK, VC.HOL_IND

                                          FROM SYS_CALENDAR.CALENDAR SC

                                          LEFT JOIN DATABASENAME.V_HOL VC ON SC.CALENDAR_DATE = VC.CAL_DT

                                          AND SC.DAY_OF_WEEK NOT IN ('1', '7') AND VC.HOL_IND IS NULL

                                       ) B WHERE B.CALENDAR_DATE > ORD_DT)

                             END

                          ELSE (SELECT CAST(CAST(MIN(B.CALENDAR_DATE) AS DATE) AS VARCHAR(30))||' '|| '07:00:00.000000' FROM

                                                      ( SELECT SC.CALENDAR_DATE , SC.DAY_OF_WEEK, VC.HOL_IND

                                                         FROM SYS_CALENDAR.CALENDAR SC

                                                         LEFT JOIN DATABASENAME.VE_HOL VC ON SC.CALENDAR_DATE = VC.CAL_DT

                                                         AND SC.DAY_OF_WEEK NOT IN ('1', '7') AND VC.HOL_IND IS NULL

                                                       ) B WHERE B.CALENDAR_DATE> ORD_DT)

            END AS NEW_DTTM

FROM DATABASENAME.VPO A

LEFT JOIN SYS_CALENDAR.CALENDAR SC1 ON SC1.CALENDAR_DATE = CAST(A.ORD_PLACE_DTTM AS DATE)

LEFT JOIN DATABASENAME.VE_HOL VC1 ON SC1.CALENDAR_DATE = VC1.CAL_DT

In the above code, if i pass on a date value to the ORD_DT in the condition WHERE B.CALENDAR_DATE > ORD_DT then the query returns the result in a couple of seconds which doesn't seem to be the case when it is left as ORD_DT.

Just to give you a brief back ground, i'm trying to calculate the business day for an order. If the order happens to be placed on a holiday or a weekend then it needs to be flipped to the next business day. If the order is placed before 7 AM on a particular day (ex: 2015-11-24 04:30:07.000000) then the order time needs to be flipped to 2015-11-24 07:00:00.000000. If the order is placed after 5PM then the order date needs to be flipped to the next business day followed by 07:00:00.000000.

This thing has been bugging me for a while now.

Any thoughts? I'd appreciate your help on this.

Thanks,

Monish.