)clear
clear ws
      )load sqapl
C:\apps\dyalog182U64\ws\sqapl.dws saved Tue Mar  8 14:28:04 2022
      SQA.Init '' 
0  SQAPL loaded from: C:\apps\dyalog182U64\cwdya64u64w.dll Using default translation no aplunicd.ini present 
      SQA.Connect 'C1' 'workshop' '***' 'bhc@insight.dk' ('Wide' 1)
0
      SQA.Tables 'C1' '' 'TABLE' 
0   TABLE_CAT  TABLE_SCHEM  TABLE_NAME  TABLE_TYPE  REMARKS       6
    workshop                deliveries  TABLE                      
    workshop                products    TABLE                      
      SQA.Columns 'C1' 
0   TABLE_CAT  TABLE_SCHEM  TABLE_NAME  COLUMN_NAME  DATA_TYPE  TYPE_NAME  COLUMN_SIZE  BUFFER_LENGTH  DECIMAL_DIGITS  NUM_PREC_RADIX  NULLABLE  REMARKS  COLUMN_DEF      SQL_DATA_TYPE  SQL_DATETIME_SUB  CHAR_OCTET_LENGTH  ORDINAL_POSITION  IS_NULLABLE       6
    workshop                deliveries  Delivered           93  DATETIME            19             16               0              10         1           NULL                        9                 3                  0                 1  YES                
    workshop                deliveries  Code                 1  CHAR                 3             12               0               0         1           NULL                        1                 0                 12                 2  YES                
    workshop                deliveries  Quantity             4  INT                 10              4               0              10         1           NULL                        4                 0                  2                 3  YES                
    workshop                deliveries  CBy                 12  VARCHAR             60            240               0               0         0           current_user()             12                 0                240                 4  NO                 
    workshop                products    Code                 1  CHAR                 3             12               0               0         0                                       1                 0                 12                 1  NO                 
    workshop                products    Description         12  VARCHAR            200            800               0               0         1           NULL                       12                 0                800                 2  YES                
    workshop                products    Cost                 3  DECIMAL              8             10               2              10         1           NULL                        3                 0                  2                 3  YES                
    workshop                products    CBy                 12  VARCHAR             60            240               0               0         0           current_user()             12                 0                240                 4  NO                 
      SQA.Do 'C1' 'Select Code,Description,Cost from products'
0  C1.s1    EGG  Half-dozen eggs        1.89       6
            FFM  Pasteurised milk 1pt   0.94        
            SKM  Skimmed milk 1pt       0.91        
            SSM  Semi-skimmed milk 1pt  0.92        
      SQA.Do 'C1' 'select Code,Delivered,Quantity from deliveries'
0  C1.s1    SSM  2022-06-18 02:23:00  7       6
            SKM  2022-06-18 02:23:00  4        
            SSM  2022-06-25 03:40:00  7        
            SKM  2022-06-25 03:40:00  4        
            SSM  2022-07-02 02:50:10  8        
            SKM  2022-07-02 02:50:10  3        
            SSM  2022-07-09 05:20:00  5        
            SKM  2022-07-09 05:20:00  1        
            SSM  2022-07-16 03:27:34  6        
            SKM  2022-07-16 03:27:34  2        
            SSM  2022-07-23 04:18:44  8        
            SKM  2022-07-23 04:18:44  4        
            SSM  2022-07-30 01:58:10  8        
            SKM  2022-07-30 01:58:10  3        
            SSM  2022-08-06 03:29:39  4        
            SKM  2022-08-06 03:29:39  1        
            FFM  2022-10-05 10:21:26  2        
      ]box on
Was OFF
      res←SQA.Do 'C1' 'Select Code,Description,Cost from products'
      res ⍝ How is the result formed?
┌─┬─────┬──────────────────────────────────┬──┬─┐
│0│C1.s1│┌────────────────────────────────┐│┌┐│6│
│ │     ││┌───┬─────────────────────┬────┐│││││ │
│ │     │││EGG│Half-dozen eggs      │1.89│││└┘│ │
│ │     ││├───┼─────────────────────┼────┤││  │ │
│ │     │││FFM│Pasteurised milk 1pt │0.94│││  │ │
│ │     ││├───┼─────────────────────┼────┤││  │ │
│ │     │││SKM│Skimmed milk 1pt     │0.91│││  │ │
│ │     ││├───┼─────────────────────┼────┤││  │ │
│ │     │││SSM│Semi-skimmed milk 1pt│0.92│││  │ │
│ │     ││└───┴─────────────────────┴────┘││  │ │
│ │     │└────────────────────────────────┘│  │ │
└─┴─────┴──────────────────────────────────┴──┴─┘
      SQA.Prepare 'C1.S1' 'Select Code,Description, Cost from products where Code like :codeC30:,Cost from products'
0
      SQA.Exec 'C1.S1'                                   
0 4
      res← SQA.Fetch 'C1.S1'  ('Columnwise' 1)             
      ⍴¨ 2⊃res
┌───┬────┬─┐
│4 3│4 30│4│
└───┴────┴─┘
      2⊃res
┌───┬──────────────────────────────┬───────────────────┐
│EGG│Half-dozen eggs               │1.89 0.94 0.91 0.92│
│FFM│Pasteurised milk 1pt          │                   │
│SKM│Skimmed milk 1pt              │                   │
│SSM│Semi-skimmed milk 1pt         │                   │
└───┴──────────────────────────────┴───────────────────┘
      SQA.Close 'C1.S1'
0
      ]box off
Was ON
      SQA.Do 'C1' 'select Code,Description,Cost,Code,Delivered,Quantity from products ,deliveries '
4   23000  1052  [ma-3.1.17][10.9.3-MariaDB-1:10.9.3+maria~ubu2204]Column 'Code' in field list is ambiguous  0
      SqlStmt←'select p.Code,Description,Cost,d.Code,Delivered,Quantity from products p ,deliveries d'
      SQA.Do 'C1' SqlStmt
0  C1.s1    EGG  Half-dozen eggs        1.89  SSM  2022-06-18 02:23:00  7       6
            FFM  Pasteurised milk 1pt   0.94  SSM  2022-06-18 02:23:00  7        
            SKM  Skimmed milk 1pt       0.91  SSM  2022-06-18 02:23:00  7        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-06-18 02:23:00  7        
            EGG  Half-dozen eggs        1.89  SKM  2022-06-18 02:23:00  4        
            FFM  Pasteurised milk 1pt   0.94  SKM  2022-06-18 02:23:00  4        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-06-18 02:23:00  4        
            SSM  Semi-skimmed milk 1pt  0.92  SKM  2022-06-18 02:23:00  4        
            EGG  Half-dozen eggs        1.89  SSM  2022-06-25 03:40:00  7        
            FFM  Pasteurised milk 1pt   0.94  SSM  2022-06-25 03:40:00  7        
            SKM  Skimmed milk 1pt       0.91  SSM  2022-06-25 03:40:00  7        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-06-25 03:40:00  7        
            EGG  Half-dozen eggs        1.89  SKM  2022-06-25 03:40:00  4        
            FFM  Pasteurised milk 1pt   0.94  SKM  2022-06-25 03:40:00  4        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-06-25 03:40:00  4        
            SSM  Semi-skimmed milk 1pt  0.92  SKM  2022-06-25 03:40:00  4        
            EGG  Half-dozen eggs        1.89  SSM  2022-07-02 02:50:10  8        
            FFM  Pasteurised milk 1pt   0.94  SSM  2022-07-02 02:50:10  8        
            SKM  Skimmed milk 1pt       0.91  SSM  2022-07-02 02:50:10  8        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-02 02:50:10  8        
            EGG  Half-dozen eggs        1.89  SKM  2022-07-02 02:50:10  3        
            FFM  Pasteurised milk 1pt   0.94  SKM  2022-07-02 02:50:10  3        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-02 02:50:10  3        
            SSM  Semi-skimmed milk 1pt  0.92  SKM  2022-07-02 02:50:10  3        
            EGG  Half-dozen eggs        1.89  SSM  2022-07-09 05:20:00  5        
            FFM  Pasteurised milk 1pt   0.94  SSM  2022-07-09 05:20:00  5        
            SKM  Skimmed milk 1pt       0.91  SSM  2022-07-09 05:20:00  5        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-09 05:20:00  5        
            EGG  Half-dozen eggs        1.89  SKM  2022-07-09 05:20:00  1        
            FFM  Pasteurised milk 1pt   0.94  SKM  2022-07-09 05:20:00  1        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-09 05:20:00  1        
            SSM  Semi-skimmed milk 1pt  0.92  SKM  2022-07-09 05:20:00  1        
            EGG  Half-dozen eggs        1.89  SSM  2022-07-16 03:27:34  6        
            FFM  Pasteurised milk 1pt   0.94  SSM  2022-07-16 03:27:34  6        
            SKM  Skimmed milk 1pt       0.91  SSM  2022-07-16 03:27:34  6        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-16 03:27:34  6        
            EGG  Half-dozen eggs        1.89  SKM  2022-07-16 03:27:34  2        
            FFM  Pasteurised milk 1pt   0.94  SKM  2022-07-16 03:27:34  2        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-16 03:27:34  2        
            SSM  Semi-skimmed milk 1pt  0.92  SKM  2022-07-16 03:27:34  2        
            EGG  Half-dozen eggs        1.89  SSM  2022-07-23 04:18:44  8        
            FFM  Pasteurised milk 1pt   0.94  SSM  2022-07-23 04:18:44  8        
            SKM  Skimmed milk 1pt       0.91  SSM  2022-07-23 04:18:44  8        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-23 04:18:44  8        
            EGG  Half-dozen eggs        1.89  SKM  2022-07-23 04:18:44  4        
            FFM  Pasteurised milk 1pt   0.94  SKM  2022-07-23 04:18:44  4        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-23 04:18:44  4        
            SSM  Semi-skimmed milk 1pt  0.92  SKM  2022-07-23 04:18:44  4        
            EGG  Half-dozen eggs        1.89  SSM  2022-07-30 01:58:10  8        
            FFM  Pasteurised milk 1pt   0.94  SSM  2022-07-30 01:58:10  8        
            SKM  Skimmed milk 1pt       0.91  SSM  2022-07-30 01:58:10  8        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-30 01:58:10  8        
            EGG  Half-dozen eggs        1.89  SKM  2022-07-30 01:58:10  3        
            FFM  Pasteurised milk 1pt   0.94  SKM  2022-07-30 01:58:10  3        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-30 01:58:10  3        
            SSM  Semi-skimmed milk 1pt  0.92  SKM  2022-07-30 01:58:10  3        
            EGG  Half-dozen eggs        1.89  SSM  2022-08-06 03:29:39  4        
            FFM  Pasteurised milk 1pt   0.94  SSM  2022-08-06 03:29:39  4        
            SKM  Skimmed milk 1pt       0.91  SSM  2022-08-06 03:29:39  4        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-08-06 03:29:39  4        
            EGG  Half-dozen eggs        1.89  SKM  2022-08-06 03:29:39  1        
            FFM  Pasteurised milk 1pt   0.94  SKM  2022-08-06 03:29:39  1        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-08-06 03:29:39  1        
            SSM  Semi-skimmed milk 1pt  0.92  SKM  2022-08-06 03:29:39  1        
            EGG  Half-dozen eggs        1.89  FFM  2022-10-05 10:21:26  2        
            FFM  Pasteurised milk 1pt   0.94  FFM  2022-10-05 10:21:26  2        
            SKM  Skimmed milk 1pt       0.91  FFM  2022-10-05 10:21:26  2        
            SSM  Semi-skimmed milk 1pt  0.92  FFM  2022-10-05 10:21:26  2        
      SqlStmt,←' where p.Code=d.Code'
      ShowSql←{↑(⊃∨/'select' 'from' 'where' 'group'⍷¨(⊂⍵))⊂⍵}
      ShowSql SqlStmt
select p.Code,Description,Cost,d.Code,Delivered,Quantity 
from products p ,deliveries d                            
where p.Code=d.Code                                      
      SQA.Do 'C1' SqlStmt
0  C1.s1    SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-06-18 02:23:00  7       6
            SKM  Skimmed milk 1pt       0.91  SKM  2022-06-18 02:23:00  4        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-06-25 03:40:00  7        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-06-25 03:40:00  4        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-02 02:50:10  8        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-02 02:50:10  3        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-09 05:20:00  5        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-09 05:20:00  1        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-16 03:27:34  6        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-16 03:27:34  2        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-23 04:18:44  8        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-23 04:18:44  4        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-30 01:58:10  8        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-30 01:58:10  3        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-08-06 03:29:39  4        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-08-06 03:29:39  1        
            FFM  Pasteurised milk 1pt   0.94  FFM  2022-10-05 10:21:26  2        
      SqlStmt,←' and Delivered >= ''2022-07-01'' and Delivered < ''2022-08-01''' 
      ShowSql SqlStmt
select p.Code,Description,Cost,d.Code,Delivered,Quantity                      
from products p ,deliveries d                                                 
where p.Code=d.Code and Delivered >= '2022-07-01' and Delivered < '2022-08-01'
      SQA.Do 'C1' SqlStmt
0  C1.s1    SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-02 02:50:10  8       6
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-02 02:50:10  3        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-09 05:20:00  5        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-09 05:20:00  1        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-16 03:27:34  6        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-16 03:27:34  2        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-23 04:18:44  8        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-23 04:18:44  4        
            SSM  Semi-skimmed milk 1pt  0.92  SSM  2022-07-30 01:58:10  8        
            SKM  Skimmed milk 1pt       0.91  SKM  2022-07-30 01:58:10  3        
      SqlStmt←'select p.Code,Description,Cost,Delivered,Quantity,Cost*Quantity from products p ,deliveries d '
      SqlStmt,←' where p.Code=d.Code'
      SqlStmt,←' and month(Delivered)=7'
      ShowSql SqlStmt
select p.Code,Description,Cost,Delivered,Quantity,Cost*Quantity 
from products p ,deliveries d                                   
where p.Code=d.Code and month(Delivered)=7                      
      SQA.Do 'C1' SqlStmt
0  C1.s1    SSM  Semi-skimmed milk 1pt  0.92  2022-07-02 02:50:10  8 7.36       6
            SKM  Skimmed milk 1pt       0.91  2022-07-02 02:50:10  3 2.73        
            SSM  Semi-skimmed milk 1pt  0.92  2022-07-09 05:20:00  5 4.6         
            SKM  Skimmed milk 1pt       0.91  2022-07-09 05:20:00  1 0.91        
            SSM  Semi-skimmed milk 1pt  0.92  2022-07-16 03:27:34  6 5.52        
            SKM  Skimmed milk 1pt       0.91  2022-07-16 03:27:34  2 1.82        
            SSM  Semi-skimmed milk 1pt  0.92  2022-07-23 04:18:44  8 7.36        
            SKM  Skimmed milk 1pt       0.91  2022-07-23 04:18:44  4 3.64        
            SSM  Semi-skimmed milk 1pt  0.92  2022-07-30 01:58:10  8 7.36        
            SKM  Skimmed milk 1pt       0.91  2022-07-30 01:58:10  3 2.73        
      SqlStmt←'select year(Delivered),month(Delivered),sum(Cost*Quantity) from products p ,deliveries d '
      SqlStmt,←' where p.Code=d.Code and month(Delivered)=7'
      SqlStmt,←' group by 1,2'
      ShowSql SqlStmt
select year(Delivered),month(Delivered),sum(Cost*Quantity) 
from products p ,deliveries d                              
where p.Code=d.Code and month(Delivered)=7                 
group by 1,2                                               
      SQA.Do 'C1' SqlStmt 
0  C1.s1   2022 7 44.03       6
      SqlStmt←'select year(Delivered),month(Delivered),sum(Cost*Quantity) from products p ,deliveries d '
      SqlStmt,←' where p.Code=d.Code and year(Delivered)=:year