Going from SQLite to SAP ASE/SQL Server need some assistance on query rewrite

Question

I wrote the following query in SQLite, which works fine, but have found out the office utilizes SAP ASE (Sybase SQL Server) and it does not display the same result there.

select
dm04_maf.mcn,
dm04_maf.wc_cd,
dm04_maf.buno_serno,
max(dm12_maf_note.maf_note) as Last_Note,
dm12_maf_note.note_dttm as Time_of_Note,
dm12_maf_note.orignr
from
dm04_maf
left join
dm12_maf_note on dm04_maf.mcn = dm12_maf_note.mcn
where dm04_maf.ty_maf_cd = 'TD'
group by dm04_maf.mcn

I believe it is not performing group by correctly as it isn't giving me the last note for each mcn (primary key) it is giving me every note for each mcn.

Any guidance for this would be appreciated.

(assuming Sybase ASE) while this query is 'legal' and ASE will run it, the fact the query is not ANSI compliant wrt the `group by` clause means you may get some unexpected results; best bet would be to make the query ANSI compliant, ie, all non-aggregates (in the `select`/projection list) should also exist in the `group by` clause — markp-fuso, Oct 06 '20 at 13:02
@markp-fuso In SQLite such a query is valid and its behavior is documented: http://www.sqlite.org/draft/lang_select.html#simple_select_processing search for *bare* columns. — forpas, Oct 06 '20 at 13:28
it doesn't matter what is (not) valid in SQLite; what matters is what's valid in ASE and how ASE processes said query; OP's query is 'valid' in ASE but due to its non-ANSI compliance may generate undesired results; when it comes to non-ANSI compliant queries each RDBMS is free to determine how (or if) said query will be processed ... and in this case (it sounds like) SQLite and ASE have chosen different ways to process the query — markp-fuso, Oct 06 '20 at 13:32
@markp-fuso the OP's query is valid in SQLite, not in SQL Server. This is why this question is posted I guess. — forpas, Oct 06 '20 at 13:39
how do you determine what row contains the `last note`? is `max(raf_note`) sufficient to determine `last note`? do you also need a `max(note_dttm)` (I'm assuming this is a date/time value)? what about the other columns (eg, `mc_cd`, `buno_serno`, etc) ... are these supposed to come from the row that contains the `last note`? — markp-fuso, Oct 06 '20 at 17:44

score 0 · Answer 1 · answered Oct 06 '20 at 12:50

0

With ROW_NUMBER() window function:

select t.mcn, t.wc_cd, t.buno_serno,
       t.maf_note as Last_Note,
       t.note_dttm as Time_of_Note,
       t.orignr 
from (
  select d04.mcn, d04.wc_cd, d04.buno_serno,
         d12.maf_note, d12.note_dttm, d12.orignr,
         row_number() over (partition by d04.mcn order by d12.maf_note desc) rn
  from dm04_maf d04 left join dm12_maf_note d12 
  on d04.mcn = d12.mcn
  where d04.ty_maf_cd = 'TD'
) t
where t.rn = 1

answered Oct 06 '20 at 12:50

forpas

160,666
10
38
76

ASE does not support `row_number()` – markp-fuso Oct 06 '20 at 13:37
This question was tagged SQL Server and SQL Server supports row_number(). – forpas Oct 06 '20 at 13:40
the question was updated and tagged as ASE, then you re-tagged it as (MS) SQL Server; as you're likely aware these are 2 completely different products; OP will need to edit the tags (again) to set the correct tag; seeing how the OP has stated in the question ... `SAP ASE` and `Sybase SQL Server` (the original name of the Sybase RDBMS) I'm guessing your tag (re)edit is incorrect and should have been left as `sybase` and `sybase-ase` – markp-fuso Oct 06 '20 at 13:45
@markp-fuso *OP will need to edit the tags (again) to set the correct tag* I agree 100% – forpas Oct 06 '20 at 13:46

score 0 · Accepted Answer · answered Oct 06 '20 at 18:23

An ANSI compliant group by query will have all non-aggregate columns (from the select/projection list) also in the group by clause. While many RDBMSs will allow non-ANSI compliant group by queries (like in this question), how each RDBMS chooses to process said non-ANSI compliant group by query is up for grabs (ie, there is no guarantee of getting the same result across different RDBMSs).

Some assumptions:

OP mentions wanting to display just the 'last note'; for now we'll assume that max(maf_note) is sufficient to determine the 'last note' for a given mcn value
the other non-aggregate columns (eg, wc_cd, buno_serno, note_dttm and orignr) should come from the same row that produces last note = max(maf_note)`

Since SAP (Sybase) ASE does not support windows functions nor ROW_NUMBER(), one idea would be to use a sub-query to find the 'last note' and then join this into the main query to pull the rest of the desired values, eg:

select  dm1.mcn,
        dm1.wc_cd,
        dm1.buno_serno,
        dt.Last_Note,
        dmn1.note_dttm as Time_of_Note,
        dmn1.orignr

from    dm04_maf      dm1
left
join    dm12_maf_note dmn1
on      dm1.mcn = dmn1.mcn

join   (select dm2.mcn,
               max(dmn2.maf_note) as Last_Note

        from   dm04_maf      dm2
        join   dm12_maf_note dmn2
        on     dm2.mcn = dmn2.mcn

        where  dm2.ty_maf_cd = 'TD'
        group by dm2.mcn
       ) dt

on      dm1.mcn       = dt.mcn
and     dmn1.maf_note = dt.Last_Note
where  dm1.ty_maf_cd = 'TD'

NOTES:

the extra dm1.ty_maf_cd = 'TD' is likely redundant; will leave it up to the OP to decide on whether to keep or remove
(obviously) may need to come back and tweak based on validity of the assumptions and/or updates to the question

This code mostly worked. At home the (original) code displays NULL values for maf_note still while your code does not (at work - haven't tried at home on SQLite). I would like to still see null values if possible but this was great, thank you very much. — Gabbelgak, Oct 06 '20 at 20:13
yeah, I figured you might want the NULLs but without some actual data (raw and final result) I was hesitant to throw that into the mix; I figured this should get you started and adding support for NULL should be relatively easy given your knowledge of the data and the desired output; keep in mind that ASE does not work well with stuff like `where column = NULL` but does work with `where column is NULL` — markp-fuso, Oct 06 '20 at 20:28

Going from SQLite to SAP ASE/SQL Server need some assistance on query rewrite

2 Answers2