The things you learn.
Send your Select statement to a file using Progress SQL
OUTPUT TO myfile.txt.
SELECT * FROM MasterTable WITH EXPORT.
Perform a self join (alt is the alternate name of the table you are self joining)
SELECT MyTable.Name, MyTable.Sales-Rep, alt.Name
FROM MyTable, MyTable alt
WHERE alt.Sales-Rep = MyTable.Sales-Rep
AND alt.Cust-Num <> MyTable.Cust-Num.
That’s neat, any idea how to combine this with delimiter? i.e.
OUTPUT TO myfile.txt.
SELECT * FROM MasterTable WITH EXPORT delimiter ','.
I don’t think you can mix the SQL with the Progress language… probably just have to do the progress stuff e.g.:
output to tags.txt.
EXPORT DELIMITER “,” “Tag_nbr” “DESC” “CODE” “LOC” “LOT/SERIAL” “REFERENCE” “FRZ_QTY”.
for each tag_mstr where tag_nbr >= 1062 and tag_nbr <= 1357:
for each ld_det where ((tag_site = ld_site) and (tag_loc = ld_loc)
and (tag_part = ld_part) and (tag_serial = ld_lot)
and (tag_ref = ld_ref) ) no-lock:
for each pt_mstr where tag_part = pt_part no-lock:
EXPORT DELIMITER "," tag_nbr pt_desc1 tag_part tag_loc ld_lot ld_ref ld_qty_frz.
end.
end.
end.
output close.
Another option is post processing the output using Perl or another scripting language.
Thanks James. I often use SQL in progress where I need an aggregate query as performing aggregates is so mach more natural in SQL, i.e.
function isOnPosr returns logical (custType as char):
def var n as int.
select count(*) into n
from cm_mstr
join jcm_ext on jcm_addr = cm_addr
where cm_type = custType
and jcm_on_posr = true.
return n > 0.
end function.
This captures an aggregate into variable n, you can do this with all columns, but I would LOVE to be able to do:
create temp table CityCounts (city, cnt) as (
select ad_city, count(*)
from ad_mstr
where ad_type = 'customer'
group by ad_city)
Which is a standard SQL way of creating a temp-table.
What I may do instead in situations where it’s helpful is:
output to value("test.temp").
select ad_city, count(*)
from ad_mstr
where ad_type = 'customer'
group by ad_city
with export.
output close.
def temp-table citycounts field city as char field cnt as int.
input from value("test.temp").
repeat: create citycounts. import citycounts. end.
output close.