)clear
)load sqapl
SQA.Init '' 
SQA.Connect 'C1' 'workshop' 'klyp' 'bhc@insight.dk' ('Wide' 1)
SQA.Tables 'C1' '' 'TABLE' 
SQA.Columns 'C1' 
SQA.Do 'C1' 'Select Code,Description,Cost from products'
SQA.Do 'C1' 'select Code,Delivered,Quantity from deliveries'
]box on
res←SQA.Do 'C1' 'Select Code,Description,Cost from products'
res ⍝ How is the result formed?
SQA.Prepare 'C1.S1' 'Select Code,Description, Cost from products where Code like :code<C3:'
SQA.Exec 'C1.S1' 'FFM'
SQA.Fetch 'C1.S1'
SQA.Exec 'C1.S1' 'S%'
SQA.Fetch 'C1.S1'
SQA.Close 'C1.S1'
SQA.Prepare 'C1.S1' 'select Code,Description,Cost from products'
SQA.Exec 'C1.S1'
SQA.Fetch 'C1.S1'
SQA.Exec 'C1.S1'
res←SQA.Fetch 'C1.S1' ('ColumnWise' 1)
⎕pw←256
2⊃res
⍴¨2⊃res
+/2 3⊃res
SQA.Close 'C1.S1'
SQA.Prepare 'C1.S1' 'select Code,:Description>C30:,Cost from products'
SQA.Exec 'C1.S1'                                   
res← SQA.Fetch 'C1.S1'  ('Columnwise' 1)             
⍴¨ 2⊃res
2⊃res
SQA.Close 'C1.S1'
]box off
SQA.Do 'C1' 'select Code,Description,Cost,Code,Delivered,Quantity from products ,deliveries '
SqlStmt←'select p.Code,Description,Cost,d.Code,Delivered,Quantity from products p ,deliveries d'
SQA.Do 'C1' SqlStmt
SqlStmt,←' where p.Code=d.Code'
ShowSql←{↑(⊃∨/'select' 'from' 'where' 'group'⍷¨(⊂⍵))⊂⍵}
ShowSql SqlStmt
SQA.Do 'C1' SqlStmt
SqlStmt,←' and Delivered >= ''2022-07-01'' and Delivered < ''2022-08-01''' 
ShowSql SqlStmt
SQA.Do 'C1' SqlStmt
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
SQA.Do 'C1' SqlStmt
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
SQA.Do 'C1' SqlStmt 
SqlStmt←'select year(Delivered),month(Delivered),sum(Cost*Quantity) from products p ,deliveries d '
SqlStmt,←' where p.Code=d.Code and year(Delivered)=:year<I: and month(Delivered)=:month<I:'
SqlStmt,←'  group by 1,2'
ShowSql SqlStmt
SQA.Do 'C1' SqlStmt 2022 6
SQA.Do 'C1' SqlStmt 2022 7
SQA.Do 'C1' 'Insert into deliveries (Code,Delivered,Quantity) values (:code<C3:,now(),:qua<I:)' 'FFM' 2
SQA.Do 'C1' 'select Code,Delivered,Quantity from deliveries'
