Progress SQL SELECT into file

Written by James McDonald

April 11, 2011

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.

3 Comments

  1. Brett Ryan

    That’s neat, any idea how to combine this with delimiter? i.e.


    OUTPUT TO myfile.txt.
    SELECT * FROM MasterTable WITH EXPORT delimiter ','.

    Reply
    • James McDonald

      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.

      Reply
      • Brett Ryan

        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.

        Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

You May Also Like…

How to Research a CPU Upgrade

How to Research a CPU Upgrade

Upgrade Time! Doing a lot of VMWare Workstation virtualization to create labs for self-study and training. Finding...