Alternative to Join Lateral with Order By and Fetch First ?

[Viking]

I wrote an SQL statement that works well using a join lateral, a technique I learned from jim_IT here on this forum. It gives me a bit of info about Varsity Shipper shipments including the last tracking activity, which is exactly what I want. I then wanted to use it within CNX Valence to create an App out of it, but it's not working there. Their tech support responded to me that they don't support order by or fetch first on join lateral sub-selects. That's how I was getting only the one most recent tracking activity record... by ordering in descending order and selecting just the first one. They recommended rewriting it using a with clause. I've used the with clause quite a bit but can't seem to figure out how use it here to do what I need.

This is the SQL statement:

/* Varsity Shipper Shipments                        */
/* Select relevant detail and header tracking info, */
/* and only the latest tracking status scan info.   */

select mdcmno as Company,
       mdordr as OrderNo,
       mdcr#1 as PORef,
       mhfcst as FromName,
       mhcnam as ToName,
       mdsdat as ShipDate,
       mdcrcd as Carrier,
       mdsvtp as ServType,
       mdftrk as Tracking,
       coalesce(stmsg,'-') as Status,
       coalesce(stdate,0) as StatusDate,
       coalesce(sttime,0) as StatusTime

/* Varsity Shipments Detail File */
from varshp30f.mfd1md as D

/* Varsity Shipments Header File */
left join varshp30f.mfh1mh on D.mdordr = mhordr and D.mdcmno = mhcmno

/* Varsity Tracking Status Scans File */
left join lateral (
  select stcmno, stordr, stmsg, stdate, sttime
  from varshp30f.ststus
  where D.mdordr = stordr and D.mdcmno = stcmno
  order by stordr, stdate desc, sttime desc
  fetch first 1 row only) as S
on D.mdordr = S.stordr and D.mdcmno = S.stcmno

where mdsdat between 20200301 and 20201231
order by mdsdat

How would I do what they suggest and use a with clause, or some other technique, to achieve the same results?

Thanks!

[Viking]

Ok, I figured out a way using the with-clause, so I'm good now. Here is my new version, just FYI:

[SIZE=14px]/* Varsity Shipper Shipments                        */
/* Select relevant detail and header tracking info, */
/* and only the latest tracking status scan info.   */

/* Select only the latest tracking scan activity for each order */
with S as
 (select * from
   (select stcmno, stordr, stmsg, stdate, sttime, row_number() over(partition by stordr
      order by stdate desc, sttime desc) as rn
    from varshp30f.ststus) s
  where s.rn = 1 and stdate >= 20200301)

select mdcmno as Company,
       mdordr as OrderNo,
       mdcr#1 as PORef,
       mhfcst as FromName,
       mhcnam as ToName,
       mdsdat as ShipDate,
       mdcrcd as Carrier,
       mdsvtp as ServType,
       mdftrk as Tracking,
       coalesce(stmsg,'-') as Status,
       coalesce(stdate,0) as StatusDate,
       coalesce(sttime,0) as StatusTime

/* Varsity Shipments Detail File */
from varshp30f.mfd1md as D

/* Varsity Shipments Header File */
left join varshp30f.mfh1mh on D.mdordr = mhordr and D.mdcmno = mhcmno

/* Varsity Tracking Status Scans File */
left join S on D.mdordr = S.stordr and D.mdcmno = S.stcmno

/* Only select shipments between given date and current date */
where mdsdat between 20200301 and dec(varchar_format (current date, 'YYYYMMDD'),8)

/* Sort the resulting shipment records by ship date */
order by mdsdat[/SIZE]

[Scott M]

Do they support stored procedures? If so, you could put your sql in a procedure and call that.

[Viking]

Scott, that is a good idea! I do have other Valence programs that use SQL statements that use SQL functions, so that would probably work.

I actually figured out how to write it using a with-clause, and I already posted my solution as a reply here, but for some reason it's waiting for someone to approve it...

Would someone who has the credentials please approve my reply above?