)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