how to join 2 query into one in EXCEL ORACLE CONNECTION

Question

I have 2 query.
I am trying to join them so I just write export from one instead of manually joining them in excel.

(SELECT 
        b.OUT_NO, 
        a.ACCNO, 
        a.BILL_ACCNO, 
        a.NAME, 
        a.HOUSE_NO, 
        a.STREET, 
        a.HOUSE_NO2, 
        a.ZIP, 
        a.ID, 
        b.TIME_STAMP, 
        b.REST_DATE, 
        c.RESTORED_TIME, 
        b.OUT_CMNT
   FROM brook.account a, 
        brook.problem b, 
        brook.history c
  WHERE c.OUT_NO = b.OUT_NO 
    AND a.ID = c.ID 
    AND (  (a.NAME Is Not Null) 
       AND (a.DISC Is Null) 
       AND (b.TIME_STAMP>?) 
       AND (c.RESTORED_TIME<?))
)

and

(SELECT 
       b.OUT_NO, 
       a.ACCNO, 
       a.BILL_ACCNO, 
       a.NAME, 
       a.HOUSE_NO, 
       a.STREET, 
       a.HOUSE_NO2, 
       a.ZIP, 
       a.ID, 
       b.TIME_STAMP, 
       b.REST_DATE, 
       c.RESTORED_TIME, 
       b.OUT_CMNT
  FROM brook.account a, 
       brook.problem b, 
       brook.history c
 WHERE c.OUTAGE_NO = b.OUTAGE_NO 
   AND a.ID = c.ID 
   AND (   (a.NAME Is Not Null) 
       AND (a.DISC Is Null) 
       AND (b.TIME_STAMP > ? And b.TIME_STAMP < ?) 
       AND (c.RESTORED_TIME > ? And c.RESTORED_TIME < ?)
       )
)

How can I join these 2? into 1, I tried UNION ALL but I get ora-01847 day of month must be between 1 and last day of month ERROR.

? are the parameter, it is linked to cells on spreadsheet. format of excel data parameter. 11/04/2013 00:00:00

Thanks

score 0 · Answer 1 · answered Nov 16 '13 at 11:53

Error is about a date format, not about union.
If you pass cell values as string parameters Oracle tries to convert it to dates to comapre with columns of date or timestamp values in table columns. To do this conversion Oracle uses it's internal default date representation format wich is not mm/dd/yyyy hh24:mi:ss in your case.

There are 2 possibilities to fix a situation:

Pass parameters with date type to query and convert values to dates before passing it to Oracle. Check examples on MSDN and description of CreateParameter and Parameters.Append methods.
Convert values to dates in query with to_date Oracle function.

Change conditions in query from

   AND (b.TIME_STAMP>?) 
   AND (c.RESTORED_TIME<?))

and

   AND (b.TIME_STAMP > ? And b.TIME_STAMP < ?) 
   AND (c.RESTORED_TIME > ? And c.RESTORED_TIME < ?)

to

   AND (b.TIME_STAMP > to_date(?,'mm/dd/yyyy hh24:mi:ss') ) 
   AND (c.RESTORED_TIME < to_date(?,'mm/dd/yyyy hh24:mi:ss') ))

and

   AND (
        b.TIME_STAMP > to_date(?,'mm/dd/yyyy hh24:mi:ss') 
        And 
        b.TIME_STAMP < to_date(?,'mm/dd/yyyy hh24:mi:ss')
       ) 
   AND (
        c.RESTORED_TIME > to_date(?,'mm/dd/yyyy hh24:mi:ss') 
        And 
        c.RESTORED_TIME < to_date(?,'mm/dd/yyyy hh24:mi:ss')
       )

how to join 2 query into one in EXCEL ORACLE CONNECTION

1 Answers1