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

Leave a Reply to Brett Ryan Cancel reply

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

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...