Author Topic: Unidata Equivalent of SQL > SELECT * FROM FILENAME  (Read 4001 times)

Chris Weitzel

  • Professional
  • ***
  • Posts: 30
Unidata Equivalent of SQL > SELECT * FROM FILENAME
« on: August 23, 2017, 03:55:56 PM »

What options do I have for (easily) doing a complete data dump of a Unidata file, including field names, without having to name each field?  I'm wanting something like SQL SELECT * FROM FILENAME.  I need the data displayed horizontally, (ideally) delimited, with output to a file.  Not too much to ask, is it?

I have experimented with LIST.ITEM and LIST ALL...  They both have their weaknesses.  For example, LIST.ITEM output is vertical and does not contain field names.  LIST ALL doesn't really list every attribute - I.e., multi-value fields are excluded.


Chris W.

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 224
  • Tom Pellitieri - Toledo, Ohio
Re: Unidata Equivalent of SQL > SELECT * FROM FILENAME
« Reply #1 on: August 24, 2017, 01:55:42 PM »
Your problem shows one of the philosophical differences between an SQL TABLE and a UniData/MultiValue Dictionary.  The TABLE forces the data into a particular structure, while the Dictionary allows you to access the data by multiple methods.

For example, our CUSTOMER file has TAX.JUR.NUM as a Multi-Valued field (Attribute 16).  It also has TAX.JUR.NUM1, TAX.JUR.NUM2 and TAX.JUR.NUM3 defined for the first three values (Attributes 16.1, 16.2, and 16.3).  Which field(s) would you want the SQL SELECT to return?  That's why LIST ALL provides the data by Attribute, with little regard to the Dictionary (it converts Dates, apparently).

Even if you applied CONVERT.SQL to a file, you may still have issues with the Multi-Valued fields.

*** Warning ***  Gory "Under-the-Hood" details follow

A "roll your own" solution would be to write a program to build the field names for the LIST/SORT command from the SB+ Dictionary Items.  In the file's DICT, these items have "Z" in Attribute 1.  Attribute 2.1 is the corresponding Attribute number (zero for Derived fields), and Attribute 2.2 has the Value number (zero for the entire attribute, -1 for MV fields). 

The SB+ field names are the same as the UniData field names, with a prepended dot.  E.g., SB+ stores its information in .PROD.NUM for the UniData field PROD.NUM (/FD maintains both).