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
  • 5 replies
  • Latest Post - ‏2019-07-12T16:42:42Z by Mike F
Mike F
Mike F
3 Posts

Pinned topic Cursor: Fetch Specific Column ?

‏2019-07-08T14:35:12Z | cursor fetch

Can I select a specific column from a cursor into another field in an RPG program.

 

Something like: FETCH InputCursor (Column_1) into Field_1 ?

 

The basic problem is that I have a data structure based on a table:

 

dcl-ds dsCustTable      extname('CustTable') inz alias;
end-ds;

 

then

 

declare inputCursor cursor for

select CT.*

from CustTable

 

But I now want to add a column to my cursor from another table like 

 

declare inputCursor cursor for

select CT.*,  CO.SalesDate

from CustTable CT

join CustOrd CO on …..

Where …..

 

But I don't want to hardcode every column in the original data structure, I just want to add 1 column SalesDate on the end.

 

So maybe FETCH into the DS, then FETCH CURRENT  SalesDate ?

 

 

 

 

Updated on 2019-07-08T14:46:02Z at 2019-07-08T14:46:02Z by Mike F
  • B.Hauser
    B.Hauser
    128 Posts
    ACCEPTED ANSWER

    Re: Cursor: Fetch Specific Column ?

    ‏2019-07-09T07:40:18Z  

    You want to read all columns of a table plus one (or several additional) columns?

     

    Just read the additional values into additional host variables or data structures

    dcl-ds dsCustTable      extname('CustTable') inz alias;
    end-ds;

    DCL-S xSalesDate    Date;
    

    Exec SQL declare inputCursor cursor for

                select CT.*,  CO.SalesDate

                   from CustTable CT

                   join CustOrd CO on …..

                   Where ….;

    Exec SQL Open InuptCursor;
    
    ...
    
    Fetch next From InputCursor into :DSCustTable, ;XSalesDate;
    
    Birgitta
    
    ...
    
    
    Updated on 2019-07-09T07:40:44Z at 2019-07-09T07:40:44Z by B.Hauser
  • Rocky_Marquiss
    Rocky_Marquiss
    19 Posts

    Re: Cursor: Fetch Specific Column ?

    ‏2019-07-08T17:26:02Z  

    The fetch is for the set, not for the column.

    But you don't have to hardcode very column. Put every column into a data structure and fetch into that data structure. I do that all the time, makes coding a lot simpler...

     

             Dcl-Ds SelectDS QUALIFIED;
               PCLNO Char(9);
               Legal     Like(SrchData.Legal);
               HNM       Zoned(6);
               Dir       Char(4);
               Str       Char(20);
               Suf       Char(4);
               Unt       Char(8);
               OwnerName Like(SrchData.OwnerName);
               OwnerFlag Char(1);
             End-Ds SelectDS;               

    ...

           EXEC SQL
             FETCH NEXT FROM Cs_Srchl
               INTO :Selectds;
     

  • B.Hauser
    B.Hauser
    128 Posts

    Re: Cursor: Fetch Specific Column ?

    ‏2019-07-09T07:40:18Z  

    You want to read all columns of a table plus one (or several additional) columns?

     

    Just read the additional values into additional host variables or data structures

    dcl-ds dsCustTable      extname('CustTable') inz alias;
    end-ds;

    DCL-S xSalesDate    Date;
    

    Exec SQL declare inputCursor cursor for

                select CT.*,  CO.SalesDate

                   from CustTable CT

                   join CustOrd CO on …..

                   Where ….;

    Exec SQL Open InuptCursor;
    
    ...
    
    Fetch next From InputCursor into :DSCustTable, ;XSalesDate;
    
    Birgitta
    
    ...
    
    
    Updated on 2019-07-09T07:40:44Z at 2019-07-09T07:40:44Z by B.Hauser
  • Mike F
    Mike F
    3 Posts

    Re: Cursor: Fetch Specific Column ?

    ‏2019-07-09T14:31:44Z  

    Thanks for the replies. I got it working.

     

    Updated on 2019-07-12T16:42:29Z at 2019-07-12T16:42:29Z by Mike F
  • JonParis
    JonParis
    346 Posts

    Re: Cursor: Fetch Specific Column ?

    ‏2019-07-09T15:16:51Z  

    For the benefit of people who read this in the future Mike, it would be n ice if you could post a quick summary of what "got it working".

  • Mike F
    Mike F
    3 Posts

    Re: Cursor: Fetch Specific Column ?

    ‏2019-07-12T16:42:42Z  
    • JonParis
    • ‏2019-07-09T15:16:51Z

    For the benefit of people who read this in the future Mike, it would be n ice if you could post a quick summary of what "got it working".

    I pretty much followed B.Hauser's suggestion. Thanks.