Gary_Cherlet

Making an IDD Code Table behave like a “real” SQL Table

Discussion created by Gary_Cherlet on Mar 6, 2013
Latest reply on Mar 27, 2013 by richard.pierce1.1
[size=5]Look at the query below - and the output. You may or may not believe me, but the "Office" data comes from the EMPSCHM network schema, and the "State name" data comes from an IDD Encode/Decode Code Table!

There is a document attached to this post that describes how to do this.

I hope that some of you will find this of interest, and that it may lead to new and creative uses of the data that is already embedded in your IDMS Network database and also in IDD Dictionary "tables"!

Enjoy - cheers - Gary[size]

[color=#cf4002]Updated 2013-03-07 1:30pm Picture of the join made smaller for online viewing friendliness and the attachment has been updated to include descriptions of the JISDICT and RSQLSHR schemas.
Please let me know if there is anything else that I can add to this post to make it more useful for you, your colleagues, or other IUA/EIUA Community members.[color]

[font=Courier New][color=#0f07e5]select OFFICE_CODE_0450 as Offc, OFFICE_STREET_0450 as Street, OFFICE_CITY_0450 as City, OFFICE_STATE_0450 as St , StateName
from empschm.office , rsqlshr.USAstates
where office_state_0450 = State
order by office_city_0450;
*+                                                              
*+ OFFC  STREET                CITY             ST  STATENAME   
*+ ----  ------                ----             --  ---------   
*+ 002   567 BOYLSTON ST       BOSTON           MA  Maine       
*+ 012   734 MASS. AVE         CAMBRIDGE            Blank       
*+ 005   7690 NEAR SIGHT AVE   GLASSTER         SA  South Aus   
*+ 001   20 W BLOOMFIELD ST    SPRINGFIELD      MA  Maine       
*+ 008   910 E NORTHSOUTH AVE  WESTON           MA  Maine       
*+                                                              
*+ 5 rows processed                                              [color][font]

Outcomes