ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Case statement with AND

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Case statement with AND

    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

  • #2
    First, what does "to get working" mean? What happens? Is there an error? Or is it just incorrect results?

    Next, what is the data definition of TSHIP? And what are some example values that seem not to work? Do any ever work?

    Finally, where is this running? Is this embedded SQL? Remote SQL? In STRSQL? QM query? And what OS version?
    Tom

    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

    Comment


    • #3
      Good morning, this is STRSQL on a iseries 7.1 os.


      The message I get is "Column TSHIP or expression in SELECT list not valid."

      When I try to expand the first example that works, to the second example with the 2 statements I get this message. TSHIP is on each record being read, I am linking to the ship master file matching with the ship field in that file. Field Definition 6A.

      Thank you and have a good day.

      Comment


      • #4
        The TSHIP column is not a part of the group by clause so it can't be used. Here is a reworked SQL statement that may fix the problem:

        With RESULTS as (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,
        REG_HRS, RCDERA
        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)

        SELECT CargoType, Fuction, ILA_JOB_CODE, JobDesc, dec(sum(REG_HRS),11,2) as reghrs,
        dec(sum(REG_HRS*RCDERA),11,2) as regpay
        FROM RESULTS
        GROUP BY CargoType, Function, ILA_JOB_CODE, JobDesc
        ORDER BY CargoType, function , ILA_JOB_CODE

        Note that in the Where clause ILA_OPERATION_CODE not in(' ','0','1') is changed to ILA_OPERATION_CODE not in('0','1') to allow blank values to be selected so the TSHIP test will be performed.

        Comment


        • #5
          Good afternoon, thanks for the input and it does work this way. I need to exclude other things where the ILA_OPERATION_CODE = Blanks, so I am working with that now.

          I appreciate the help, good to know I was not too far off!

          Thank you and have a good day.

          Comment

          Working...
          X