IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2019-07-08T18:00:02Z by Rocky_Marquiss
RickyThompson
RickyThompson
4 Posts

Pinned topic DataStructure and SQL

‏2019-04-26T12:50:54Z |

I was trying to do a SQL Select into myExtDs but SQL does not like the data structure since I'm trying to define an array and put 12 dates for example into it.  My solution was to make 2 data structures and move one into the the other.  Is there a way to use a single data structure and make this work?  Also it would be nice to be able to use an array on a where clause like where date = :startDatesArr(1); or on Insert/Update statements.  

DCL-DS myExtDs ExtName('MYTABLE') QUALIFIED;                
  startDatesArr DATE dim(12) pos(10);  
END-DS;                                                             
                                                                    
DCL-DS myExtDs2 ExtName('MYTABLE') QUALIFIED;               
END-DS;                  

Exec Sql                                                            
  Select *                                                          
  INTO :myExtDs2                                               
  from MYTABLE                                                   
  Fetch First Row Only;                                             
  
myExtDs = myExtDs2;                                       

 

Updated on 2019-04-26T12:52:33Z at 2019-04-26T12:52:33Z by RickyThompson
  • JonParis
    JonParis
    347 Posts

    Re: DataStructure and SQL

    ‏2019-04-26T14:03:02Z  

    With the advent of 7.4 (and this feature is PTF'd into 7.3) you could use SAMEPOS instead of the hard-coded POS(10) and since the SQL pre-compiler has been updated to handle this it is possible that this may work.  If not it is always worth writing up a ticket as there is no reason I can see to disallow it.

     

    The only obvious alternative to what you have done (and which would avoid the data copying) is to define myExtDs as BASED(pmyExtDs2) and then define pmyExtDs2 as a pointer with an Inz(%Addr(myExtDs2)).

    Array on WHERE clause?  Well now that SQL does at least accept that arrays exist in the real world maybe IBM would consider an RFE but unless the SQL standard is leaning toward that kind of flexibility they may not look kindly on it.  You could of course do it with a dynamic SQL statement and as I understand it, that is now as efficient as static SQL.  Just a thought.

  • Rocky_Marquiss
    Rocky_Marquiss
    19 Posts

    Re: DataStructure and SQL

    ‏2019-07-08T18:00:02Z  

    All you need to do is overlay one data structure over the other. You do this using a pointer.

     

    DCL-S myExtDS_PTR POINTER Inz(%ADDR(myExtDS2));

    DCL-DS myExtDs ExtName('MYTABLE') QUALIFIED BASED(myExtDS_PTR);                
      startDatesArr DATE dim(12) pos(10);  
    END-DS;                                                             
                                                                        
    DCL-DS myExtDs2 ExtName('MYTABLE') QUALIFIED;               
    END-DS; 

     

    The data structures are now interchangeable. You put data in one, it's in the other by virtue pointing to the same portion of memory.