Variables to hold table column functions

Idea created by GregAnderson82343422 on Apr 12, 2018
    New
    Score4
    • AdrianJohnsonUK
    • GregAnderson82343422
    • SrinivasanSathiamurthi
    • Anil Kumar Appukuttan

    When cloning data from one database to another  the functions in the columns are like 

     

    @seqlov(0,@sqllist(PDataSource,"SELECT * FROM SCHEMA.TABLE1  L1 WHERE ( TABLE COLUMNS ) IN (SELECT TABLE COLUMNS from SCHEMA.TABLE1 L1 INNER JOIN SCHEMA.TABLE2 L0 ON TABLE COLUMNS AND COLUMN in (CRITERIA))")@,~COLUMN_NAME~)@

     

    when additional actions are required on this field the function can get very large 

    aka,   @if(stringabove=0,0,@xlookup(table.name,stringabove,@xref(table.name2,stringabove,range@right(stringabove,6,10)@)@)@

     

    as you can see the selection string is used 4 times and all I want to do is validate the value is not 0 then see if it has been added to a list and use that value,  if it is not in the list create a new list to save the old and new values....    now imagine the sql being longer with a few more table joins and this becomes a very long string...

     

     @if(@seqlov(0,@sqllist(PDataSource,"SELECT * FROM SCHEMA.TABLE1  L1 WHERE ( TABLE COLUMNS ) IN (SELECT TABLE COLUMNS from SCHEMA.TABLE1 L1 INNER JOIN SCHEMA.TABLE2 L0 ON TABLE COLUMNS AND COLUMN in (CRITERIA))")@,~COLUMN_NAME~)@=0,0,@xlookup(table.name,@seqlov(0,@sqllist(PDataSource,"SELECT * FROM SCHEMA.TABLE1  L1 WHERE ( TABLE COLUMNS ) IN (SELECT TABLE COLUMNS from SCHEMA.TABLE1 L1 INNER JOIN SCHEMA.TABLE2 L0 ON TABLE COLUMNS AND COLUMN in (CRITERIA))")@,~COLUMN_NAME~)@,@xref(table.name2,@seqlov(0,@sqllist(PDataSource,"SELECT * FROM SCHEMA.TABLE1  L1 WHERE ( TABLE COLUMNS ) IN (SELECT TABLE COLUMNS from SCHEMA.TABLE1 L1 INNER JOIN SCHEMA.TABLE2 L0 ON TABLE COLUMNS AND COLUMN in (CRITERIA))")@,~COLUMN_NAME~)@,range@right(@seqlov(0,@sqllist(PDataSource,"SELECT * FROM SCHEMA.TABLE1  L1 WHERE ( TABLE COLUMNS ) IN (SELECT TABLE COLUMNS from SCHEMA.TABLE1 L1 INNER JOIN SCHEMA.TABLE2 L0 ON TABLE COLUMNS AND COLUMN in (CRITERIA))")@,~COLUMN_NAME~)@,6,10)@)@)@

     

    could a special variable class be created specifically for stringabove at a table level as most columns would not need to have extra functions so the default would work but for the columns that require extra logic this gets very convoluted and hard to read. ( see ideas for pretty print and comments to make these complex functions  more user friendly)

     

    also - if you ever needed to change the SQL -   good luck getting all the places  changed correctly..... a variable would be a 1 stop shop to change all the columns in the table at 1 time (yes you could change the first column and copy right - unless you have special functions scattered in those columns)

     

    It would be good if these variables were set apart from the current variables as the purpose is different.  Current variables are interactive in that you would want to potentially change them at time of publish.  These variables would not change unless there was new selection criteria for the table.