Free Form Text Block in Code Editor

Please log all suggestions for improvements and enhancements to Visual LANSA here. Entries may be acknowledged and may be added to the list for possible inclusion in later releases of Visual LANSA.
Post Reply
jyoung
Posts: 652
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Free Form Text Block in Code Editor

Post by jyoung » Tue Oct 01, 2019 12:09 am

Do the developers still pay attention to this board? The board description mentions that all entries will acknowledged, but a lot of these go unacknowledged.

In case they do, it would be nice to have a free format text block in the IDE editor.
The editor is VERY demanding with its spacing and indention which makes dealing with large complex string (like when using select_sql) very difficult.

For example take this query that unions data from two different (but structurally same) libraries:

Code: Select all

#lSql := ("select BDBC, BDFTYP, BDFR, case when Month = 1 then sum(GM) end as JAN, case when Month = 2 then sum(GM) end as FEB, case when Month = 3 then sum(GM) end as MAR, case when Month = 4 then sum(GM) end as APR, case when Month = 5 then sum(GM) end as MAY, case when Month = 6 then sum(GM) end as JUN, case when Month = 7 then sum(GM) end as JUL, case when Month = 8 then sum(GM) end as AUG, case when Month = 9 then sum(GM) end as SEP, case when Month = 10 then sum(GM) end as OCT, case when Month = 11 then sum(GM) end as NOV, case when Month = 12 then sum(GM) end as DEC from ( select  BDBC, BDFTYP, BDFR, month(date(timestamp_format(char(BDDWP),'YYYYMMDD'))) as Month, sum(BDGMA) as GM from &1.AF1220 where BDOFID in (select OFID from &1.XF1102E where ELVL = 0 and ZONE = &3) and BDELVL = 0 and BDDWP > &4 and BDDWP <= &5 group by BDBC, BDFTYP, BDFR, month(date(timestamp_format(char(BDDWP),'YYYYMMDD'))) union all select BDBC, BDFTYP, BDFR, month(date(timestamp_format(char(BDDWP),'YYYYMMDD'))) as Month, sum(BDGMA) as GM from &1.AF1220 where BDOFID in (select OFID from &2.XF1102E where ELVL = 0 and ZONE = &3) and BDELVL = 0 and BDDWP > &4 and BDDWP <= &5 group by BDBC, BDFTYP, BDFR, month(date(timestamp_format(char(BDDWP),'YYYYMMDD')))) a group by BDBC, BDFTYP, BDFR, Month order by BDBC, BDFTYP, BDFR").Substitute( #lUSDataLibrary #lCADataLibrary #wk_Zone.AsString #wk_StartDate.AsString #wk_EndDate.AsString )
This is a very complex query that pivots on months. The query is difficult enough to handle in normal SQL tools, and near impossible to deal with in the editor.

What I would like to see is a text block capability so that query could be formatted into a more manageable form.
For example:

Code: Select all

#lSql :=( `
select BDBC, BDFTYP, BDFR,
    case when Month = 1 then sum(GM) end as JAN,    
    case when Month = 2 then sum(GM) end as FEB,
    case when Month = 3 then sum(GM) end as MAR,
    case when Month = 4 then sum(GM) end as APR,
    case when Month = 5 then sum(GM) end as MAY,
    case when Month = 6 then sum(GM) end as JUN,
    case when Month = 7 then sum(GM) end as JUL,
    case when Month = 8 then sum(GM) end as AUG,
    case when Month = 9 then sum(GM) end as SEP,
    case when Month = 10 then sum(GM) end as OCT,
    case when Month = 11 then sum(GM) end as NOV,
    case when Month = 12 then sum(GM) end as DEC
from (
    select  BDBC, 
            BDFTYP, 
            BDFR, 
            month(date(timestamp_format(char(BDDWP),'YYYYMMDD'))) as Month, 
            sum(BDGMA) as GM
    from &1.AF1220 
    where BDOFID in (select OFID from &1.XF1102E where ELVL = 0 and ZONE = &3) 
        and BDELVL = 0 
        and BDDWP > &4 
        and BDDWP <= &5 
    group by BDBC, BDFTYP, BDFR, month(date(timestamp_format(char(BDDWP),'YYYYMMDD')))
    
    union all
    
    select  BDBC, 
            BDFTYP, 
            BDFR, 
            month(date(timestamp_format(char(BDDWP),'YYYYMMDD'))) as Month, 
            sum(BDGMA) as GM
    from &1.AF1220 
    where BDOFID in (select OFID from &2.XF1102E where ELVL = 0 and ZONE = &3) 
        and BDELVL = 0 
        and BDDWP > &4 
        and BDDWP <= &5
    group by BDBC, BDFTYP, BDFR, month(date(timestamp_format(char(BDDWP),'YYYYMMDD')))
) a
group by BDBC, BDFTYP, BDFR, Month
order by BDBC, BDFTYP, BDF
`).Substitute( #lUSDataLibrary #lCADataLibrary #wk_Zone.AsString #wk_StartDate.AsString #wk_EndDate.AsString )
Notice the leading and trailing backtick (`). This is how a lot of other editors support this type of functionality and I think it would make the VL IDE much easier to use in these cases.

It could be argued that this particular query should be turned into a View, but for reasons beyond this discussion it cannot, and merely serves as an example of complex strings in the IDE.

Thoughts?
Joe

LANSAGuru
Posts: 67
Joined: Thu Mar 24, 2016 5:31 am

Re: Free Form Text Block in Code Editor

Post by LANSAGuru » Tue Oct 01, 2019 12:48 am

This seems like a good idea to me. +1

BrendanB
Posts: 29
Joined: Tue Nov 24, 2015 10:29 am

Re: Free Form Text Block in Code Editor

Post by BrendanB » Thu Oct 03, 2019 11:22 pm

Joe,

something that one of my colleagues does when doing SQL statements:

#lSql := "( SELECT BDBC, BDFTYP, BDFR, "
#lSql += " case when Month = 1 then sum(GM) end as JAN,"
#lSql += " case ..."

and so on....

This allows you to break the statement for readability....

I know it is not as nice as what you have requested, but i think the reason comes down to:
languages that allow the free format (as in your example) usually have a special character (such as ";") to indicate: this is the end of the statement.

RDML and RDMLX do not have an 'end of statment' character, so it would be difficult for the compiler to determine when a statement ends.

Brendan.

jyoung
Posts: 652
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: Free Form Text Block in Code Editor

Post by jyoung » Thu Oct 03, 2019 11:54 pm

Hi Brendan,

I think that is a good work around and something I will have to remember to do.

I get that there are limitations on what the compiler can handle right now, but surely it can be upgraded.

With other languages like C#, Java etc. the compiler is continuously updated to provide new features.
Yeah, I know LANSA is not Microsoft or Oracle and don't have the resources they do, but I don't think asking for a few updates to make RDML/X better is unreasonable.

Thanks,
Joe

Post Reply