{"id":41602,"date":"2020-04-21T09:37:08","date_gmt":"2020-04-21T07:37:08","guid":{"rendered":"http:\/\/54.194.80.134.nip.io\/sqlscript-solution-pattern\/"},"modified":"2023-12-25T22:11:04","modified_gmt":"2023-12-25T21:11:04","slug":"sqlscript-solution-pattern","status":"publish","type":"post","link":"https:\/\/www.cubeserv.com\/en\/sqlscript-solution-pattern\/","title":{"rendered":"SQLscript solution pattern"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Been looking for it for a long time? Now you’ve found it!<\/h3>\n

Our overview of typical problems and solutions in the area of HANA SQLscript.<\/p>\n

The solution patterns range from purely linguistic problems (e.g. “Which language element do I use to determine the first entry”) to formal problems (e.g. “How do I convert time characteristics in SQLscript”) and application-related requirements (e.g. “How do I look up master data in SQLscript”).<\/p>\n

The sample solutions do not claim to be the sole or best solution to a problem but are intended to serve as a template and inspire you with different approaches. Your feedback on improvements, alternatives, and additions is always welcome!<\/p>\n

The coding example is formulated as it might typically occur in an AMDP routine. However, this formulation cannot be used in a HANA Studio SQL window or in the SQL editor of the ABAP transaction DBACOCKPIT (as it is not defined there what the inTab should be). A coding example is therefore also given in some places, which could be used in the SQL window. These examples contain additional lines that build up the sample data.<\/p>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Basic language elements:<\/strong><\/p>\n

    \n
  1. Initial values<\/a><\/li>\n
  2. Temporary tables (table variables)<\/a><\/li>\n
  3. Renaming columns<\/a><\/li>\n
  4. Union of identical tables<\/a><\/li>\n
  5. Joining two tables<\/a><\/li>\n
  6. Case differentiation<\/a><\/li>\n
  7. Defining, filling, and using variables<\/a><\/li>\n
  8. Avoid NULL values<\/a><\/li>\n
  9. Count the number of rows<\/a><\/li>\n
  10. Conditions<\/a><\/li>\n
  11. Sum, Max, Min, …<\/a><\/li>\n
  12. Replace leading 0s<\/a><\/li>\n
  13. Check for digits<\/a><\/li>\n
  14. Condense spaces<\/a><\/li>\n
  15. Remove invalid characters<\/a><\/li>\n
  16. Determine the first data record<\/a><\/li>\n
  17. Determine rank<\/a><\/li>\n
  18. Access to the previous or subsequent line<\/a><\/li>\n<\/ol>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    Applicator solutions:<\/strong><\/p>\n

      \n
    1. Date calculation<\/a><\/li>\n
    2. Derivation of a current UTC timestamp<\/a><\/li>\n
    3. Derivation of the request TSN<\/a><\/li>\n
    4. Latest data of a wo-ADSO<\/a><\/li>\n
    5. Reading master data<\/a><\/li>\n
    6. Catching SQL errors in the AMDP<\/a><\/li>\n
    7. Throw your own SQL error messages in the AMDP<\/a><\/li>\n
    8. Update valid records in AMDP, write incorrect records to the error stack<\/a><\/li>\n<\/ol>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t\t
      \n\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t
      \n\t\t\t\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t\t
      \n\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t

      Initial values<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t\t\t\t\t

      Description<\/h3>\n

      Initialize character-type columns with ”, numbers with 0.<\/p>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

      \n\t\t\t\t\t\t
      \n\t\t\t\t\t
      \n\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t

      Coding example<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t
      \n\t\t\t
      \n\t\t\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t\t
      \n\t\t\t
      \n\t\t\t\t\t\t
      \n\t\t\t\t
      \n\t\t\t
      outtab =\r\nSELECT\r\n'' AS "\/BIC\/STRASSE",\r\n0  AS "\/BIC\/HAUSNR"\r\nFROM :intab; <\/code><\/pre>