Good afternoon trying to work this query. There are operation codes, but sometimes they are blank and I want to check another piece of the record to determine what operation I should put it under.
Here is the statement that works, then the one I want to get working. The area in question is in BOLD.
SELECT case cargot
when 'G' then 'GENERAL CARGO' when 'C' then 'CONTAINER'
when 'V' then 'AUTO' when 'S' then 'STEEL'
when ' ' then 'GENERAL CARGO' end as CargoType,
case ILA_OPERATION_CODE
when 'S' then 'VESSEL' else 'TERMINAL' end as Function,
Substr(RJOBDE,1,15) as JobDesc,
dec(sum(REG_HRS),11,2) as reghrs, dec(sum(REG_HRS*RCDERA),11,2) as
regpay FROM prlibrf/timecd t inner join paylibrf/PRLRTCD p on
t.ila_rate_code = RRATCD inner join paylibrf/PRPJBDE on
T.ILA_JOB_CODE = RJOBCD inner join ships on SHIP = TSHIP
WHERE CO_CODE = 'GRT' and PAY_TYPE = 'I'
and PAY_DATE between 20160101 and 20161231 and PAY_DATE between
RRATFR and RRATTO and ILA_OPERATION_CODE not in(' ','0','1') and
REG_HRS <> 0
GROUP BY cargot, ILA_OPERATION_CODE ,T.ILA_JOB_CODE ,RJOBDE
ORDER BY cargot, function , T.ILA_JOB_CODE
And the one I want to get working:
SELECT case cargot
when 'G' then 'GENERAL CARGO' when 'C' then 'CONTAINER'
when 'V' then 'AUTO' when 'S' then 'STEEL'
when ' ' then 'GENERAL CARGO' end as CargoType,
case
when ILA_OPERATION_CODE = 'S' then 'VESSEL'
when ILA_OPERATION_CODE = ' ' and TSHIP like('%SSS') then 'VESSEL'
when ILA_OPERATION_CODE= ' ' and TSHIP like('%CLN') then 'TERMINAL'
else 'TERMINAL' end as Function,
T.ILA_JOB_CODE, Substr(RJOBDE,1,15) as JobDesc,
dec(sum(REG_HRS),11,2) as reghrs, dec(sum(REG_HRS*RCDERA),11,2) as
regpay FROM prlibrf/timecd t inner join paylibrf/PRLRTCD p on
t.ila_rate_code = RRATCD inner join paylibrf/PRPJBDE on
T.ILA_JOB_CODE = RJOBCD inner join ships on SHIP = TSHIP
WHERE CO_CODE = 'GRT' and PAY_TYPE = 'I'
and PAY_DATE between 20160101 and 20161231 and PAY_DATE between
RRATFR and RRATTO and ILA_OPERATION_CODE not in(' ','0','1') and
REG_HRS <> 0
GROUP BY cargot, ILA_OPERATION_CODE ,T.ILA_JOB_CODE ,RJOBDE
ORDER BY cargot, function , T.ILA_JOB_CODE
Thanks in advance,
64waves
Here is the statement that works, then the one I want to get working. The area in question is in BOLD.
SELECT case cargot
when 'G' then 'GENERAL CARGO' when 'C' then 'CONTAINER'
when 'V' then 'AUTO' when 'S' then 'STEEL'
when ' ' then 'GENERAL CARGO' end as CargoType,
case ILA_OPERATION_CODE
when 'S' then 'VESSEL' else 'TERMINAL' end as Function,
Substr(RJOBDE,1,15) as JobDesc,
dec(sum(REG_HRS),11,2) as reghrs, dec(sum(REG_HRS*RCDERA),11,2) as
regpay FROM prlibrf/timecd t inner join paylibrf/PRLRTCD p on
t.ila_rate_code = RRATCD inner join paylibrf/PRPJBDE on
T.ILA_JOB_CODE = RJOBCD inner join ships on SHIP = TSHIP
WHERE CO_CODE = 'GRT' and PAY_TYPE = 'I'
and PAY_DATE between 20160101 and 20161231 and PAY_DATE between
RRATFR and RRATTO and ILA_OPERATION_CODE not in(' ','0','1') and
REG_HRS <> 0
GROUP BY cargot, ILA_OPERATION_CODE ,T.ILA_JOB_CODE ,RJOBDE
ORDER BY cargot, function , T.ILA_JOB_CODE
And the one I want to get working:
SELECT case cargot
when 'G' then 'GENERAL CARGO' when 'C' then 'CONTAINER'
when 'V' then 'AUTO' when 'S' then 'STEEL'
when ' ' then 'GENERAL CARGO' end as CargoType,
case
when ILA_OPERATION_CODE = 'S' then 'VESSEL'
when ILA_OPERATION_CODE = ' ' and TSHIP like('%SSS') then 'VESSEL'
when ILA_OPERATION_CODE= ' ' and TSHIP like('%CLN') then 'TERMINAL'
else 'TERMINAL' end as Function,
T.ILA_JOB_CODE, Substr(RJOBDE,1,15) as JobDesc,
dec(sum(REG_HRS),11,2) as reghrs, dec(sum(REG_HRS*RCDERA),11,2) as
regpay FROM prlibrf/timecd t inner join paylibrf/PRLRTCD p on
t.ila_rate_code = RRATCD inner join paylibrf/PRPJBDE on
T.ILA_JOB_CODE = RJOBCD inner join ships on SHIP = TSHIP
WHERE CO_CODE = 'GRT' and PAY_TYPE = 'I'
and PAY_DATE between 20160101 and 20161231 and PAY_DATE between
RRATFR and RRATTO and ILA_OPERATION_CODE not in(' ','0','1') and
REG_HRS <> 0
GROUP BY cargot, ILA_OPERATION_CODE ,T.ILA_JOB_CODE ,RJOBDE
ORDER BY cargot, function , T.ILA_JOB_CODE
Thanks in advance,
64waves
Comment