OPSMVS RDF TABLE

[Randy Knapton]

I would like to see results from two RDF tables into one query.  Is this possible in OPSMVS?

 

Example

TABLE A

NAME  

TASK1

TASK2

TASK3

 

TABLE B

NAME

TASK4

TASK5

 

RESULTS:

TASK1

TASK2

TASK3

TASK4

TASK5

 

Then do a SQL SELECT against the RESULTS.

[Cesar_Molina]

Randy,

 

Not exactly what you are looking for but recently I was looking at the Knowledge Document TEC427260 to address a problem another client has when trying to join two RDF tables in CA OPS/MVS.

Please visit this URL when time permits to look at the article:

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec427260.aspx

Adapt the Select statement in the KD to your RDF table names and let us know if this helps.

 

Regards, Cesar

[Randy Knapton]

Hi Cesar,

 

After read some of the OPSMVS doc, my understanding was OPSMVS did the JOIN process.  I thought I could get away with something a little more simple.

 

From OPSMVS Doc:

 

ADDRESS SQL "SELECT {columnlist|*} keywords"

 

/* The following keyword must follow the optional INTO hostvarlist keywords, */

 

/* if used. You may specify more than one tablename; if you do, be sure to */

 

/* separate each one with a comma: */

 

 

like

"Select jobname from tablea,tableb"

 

[gorda05]

Yes. The join logic as you have coded..."Select jobname from tablea,tableb" will give you list of jobname.x variables for all the entries in both tables if that is what you are looking to do. If you want to do an additional query (SQL SELECT) against that returned list, this join data doesn't help you since you have no way of knowing what table to select from (output from join doesn't have table info). Provide some details of your exact subsequent queries that you want to do with the obtained 'joined' output and we'll  look at seeing what SQL statements will best fit the need. Perhaps adding some additional sub query WHERE logic on the join statement may help.

[Randy Knapton]

What I what to do is pretty simple,  If a jobname exists in either table sqlcode = '0' then iterate (I don't care) but if it doesn't  do something else.   Right now I'm just trying to display the names in both tables,  I get a RC of 0 but names from only one table.

 

Address SQL                                           

"SELECT * FROM TABLEA,TABLEB"                         

say rc                                                

DO i = 1 to name.0                                    

say name.i                                            

end                                                  

[gorda05]

Are you perhaps on 12.3?...Quick test looks like we may have issue with SQL JOIN in 12.3 

[Randy Knapton]

Ah yes we are on 12.3

[gorda05]

OK...let me poke around in a little bit....haven't really utilized join statements that much and I'll be the first to say that the doc doesnt really give 'useful' examples.  I am seeing some differences on 12.3, so I'll get that looked at too...I'll update with results later.... 

[gorda05]

In the meantime, (if I understand what you are needing correctly) the below code that does two separate SQL selects  and compares SQLCODE = 100 (not found in either table) should do the trick if you dont have it already....

 

jobname = 'TEST'                                     

address SQL                                          

"Select JOBNAME from TBL1 where JOBNAME = :jobname"  

scode_tbl1 = sqlcode                                 

"Select JOBNAME from TBL2 where JOBNAME = :jobname"  

scode_tbl2 = sqlcode                                 

If scode_tbl1 = '100' & scode_tbl2 = '100' then      

do                                                  

  /* Do logic where jobname not in either table */   

  say 'Not in either table'                          

end                                                 

[Randy Knapton]

Thanks David
That will work...