ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL 305 error with coalesce in RPGLE

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

  • SQL 305 error with coalesce in RPGLE

    I have what I thought was a simple statement (below)

    H ALWNULL(*INPUTONLY)

    select rhwhs,rhvnno,rhbol,rhcanm,rhrcdt,rhsnno,vennam,
    cast (coalesce(apevthh,0) as numeric(2,0)) as apevthh,
    cast (coalesce(apevtmm,0) as numeric(2,0)) as apevtmm

    from po210m1
    join po180m1 on rhorno=poorno and rhwhs=powhse
    join apmastp on rhvnno=venno
    left join po275m3 on rhsnno=apsnno
    where postat='O'


    I can open the cursor fine but I am getting a -305 when it executes the fetch. I can cut and paste the statement into a SQL browser and it runs in that environment.

    I am using the coalesce on the "AP" fields which come from the table that is left joined. If I remove the "left" it works great. So, I assume that is the table generating the error.

    I have tried all sorts of things to get around this but I am really stuck. This technique has worked really well in the past. I would appreciate any help that someone could offer.

    Regards,

    Mark
    Search the world's information, including webpages, images, videos and more. Google has many special features to help you find exactly what you're looking for.

  • #2
    You need the COALESCE for columns coming from the right file.
    Left outer join means, all rows from the left table and from the second table that match.
    If there is no row for the right table NULL values are returned.
    With COALESCE you convert a NULL value into a default value.

    Birgitta

    Comment

    Working...
    X