SQL Union

[iamtrying]

Hi I am trying to derive the correct absence startTime of all exceptions (absence codes)from the following SQL statement. But, I am getting the following results with all start times beginning at 00:00 and ending 02:00X. With the following SQL statement, how can retrieve the correct "da.absTimeBegin and a."absTimeEnd"?
Thanks guys.

1000010705,,05/10/2013,00:00,02:00X,08:26,VAC
1000010705,,05/13/2013,00:00,02:00X,08:26,VAC
1000010705,,05/14/2013,00:00,02:00X,08:26,VAC
1000010705,,05/15/2013,00:00,02:00X,08:26,VAC
1000010705,,05/16/2013,00:00,02:00X,08:26,VAC
1000010705,,05/17/2013,00:00,02:00X,08:26,VAC
1000010705,,05/25/2013,08:00,11:00A,03:00,PHYO
1000010705,,05/27/2013,00:00,02:00X,08:33,H
1000010705,,07/04/2013,00:00,02:00X,08:33,H
1000010705,,08/23/2013,00:00,02:00X,08:33,SK
1000010705,,08/26/2013,00:00,02:00X,08:33,SK
1000010705,,08/27/2013,00:00,02:00X,08:33,SK

select
(e."personnelID")||','||
trim(a."runNumber") ||','||
trim(To_Char(da."opDate", 'MM/DD/YYYY' ))||','||
trim(dbo.hh_mm(nvl(da."absTimeBegin",0)))||','||
trim(dbo.hh_mma(nvl(a."absTimeEnd",0)))||','||
trim(dbo.hh_mm(nvl(da."paidTime",0)))||','||
trim(da."absPayCode")
from "dailyAbsence" da , "absence" a, "employee" e, "employeeStatus" es
----where da."opDate" in ('16-dec-13','17-dec-13')and
where da."opDate" between SYSDATE -3 AND SYSDATE +3 and
a."emp_SID" = da."emp_SID" and a."absCode" = da."absCode" and a."absDateBegin" = da."absDateBegin" and a."absTimeBegin" = da."absTimeBegin" and
e."emp_SID" = da."emp_SID" and
es."emp_SID" = da."emp_SID" and es."dateEffective" <= da."opDate" and es."dateEnd" >= da."opDate"
union all
select
(e."personnelID")||','||
(detd."runNumber") ||','||
trim(To_Char(de."opDate", 'MM/DD/YYYY' ))||','||
trim('00:00A')||','||
trim('00:00A')||','||
trim(dbo.hh_MM(detd."paidTime"))||','||
trim(detd."payType")
from "dailyEmployeeTimeDetail" detd
INNER JOIN "employee" e ON e."emp_SID"= detd."emp_SID"
INNER JOIN "employeeStatus" es ON es."emp_SID"= detd."emp_SID" and es."dateEffective" <= detd."opDate" and es."dateEnd" >= detd."opDate"
inner join "dailyEmployee" de on de."opDate" = detd."opDate" and de."emp_SID" = detd."emp_SID"
----where detd."opDate" in ('16-dec-13','17-dec-13')and
where detd."opDate" between SYSDATE -3 AND SYSDATE +3 and detd."payType" not in ('GU','PL','RP','TR','BR','CO','DT','LRF','ME','NET', 'WOT','OE','OS','PLD','PL','RP','RT','LIN','SE','SDP','SIG4','SH','SL','SO','SP','STU','TE','TRF','TRN','TS','VLT','WE','WS','WT') and detd."paySource" <>'ABAT'

 

[LKBrwnDBA]

Do not use the TRIM() function.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ChrisHunt]

Ugh, mixed case column names, what a PITA.

What does the dbo.hh_mm function do? How does it differ from TO_DATE?

Is your problem that there are rows that should appear in your output that have different start and end times, or is it that you're getting 00:00 and 02:00X instead of the values that should be showing?

Maybe it would help to add these columns to the one you're selecting, just to see what's going on:

,TO_CHAR(da."absTimeBegin",'dd/mm/yyyy hh24:mi:ss'), TO_CHAR(a."absTimeEnd",'dd/mm/yyyy hh24:mi:ss')

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[iamtrying]

Chris,

I spoke with the vendor and I was advised my query is correct. The times returned is a direct result of the default values for these absences and exceptions.

Thank you for your time.