Visual LANSA Forum

Posted: **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

Posted: **Tue Oct 01, 2019 12:48 am**

This seems like a good idea to me. +1

Posted: **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.

Posted: **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

Visual LANSA Forum

Free Form Text Block in Code Editor

Free Form Text Block in Code Editor

Re: Free Form Text Block in Code Editor

Re: Free Form Text Block in Code Editor

Re: Free Form Text Block in Code Editor