Results 1 to 4 of 4

Thread: Openquery Join

  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Openquery Join

    Good morning,
    I'm using Openquery to retrive data from DB2 z/OS Mainframe via MS SQL 2005 ENT linked server. I'm using MS Access as front end app to search for a record and display its values and then fill in earnings tax return. I have been able to retrive records just fine but I'm bit stumped when it comes to join in Openquery. I want to be able to add City/State from ZIPX_CODE table based on MSTR_ZIPC value from ETAX_MASTER table.
    Here is SQL for getting specific values from ETAX_MASTER table;
    SELECT MSTR_FIDN, MSTR_NAM1, MSTR_NAM2, MSTR_STRT, MSTR_ZIPC, MSTR_ATYP, MSTR_ADAT, MSTR_AEND FROM OPENQUERY(TSTESA5,'SELECT * FROM stl01.ETAX_MASTER where MSTR_FIDN =''26202479000''')
    And here is the retrival of City and State just using zip code 63135;
    SELECT ZIPX_CODE, ZIPX_CITY_ST FROM OPENQUERY(TSTESA5,'SELECT * FROM STL01.ETAX_ZIPX WHERE ZIPX_CODE = 63135')

    Now I want to join these two and use MSTR_ZIPC code from ETAX_MASTER table and ZIPX_CODE and retrieve ZIPX_CITY_ST.

    Thanks a lot


    Amir

  2. #2
    Join Date
    Mar 2011
    Posts
    3

    join

    This statement actually works but its pulling whole ETAX_ZIPX table down and then joining causing 40 second delay.
    select * from
    OPENQUERY(TSTESA5,'SELECT MSTR_FIDN, MSTR_NAM1, MSTR_NAM2, MSTR_STRT, MSTR_ZIPC, MSTR_ATYP, MSTR_ADAT, MSTR_AEND FROM stl01.ETAX_MASTER where MSTR_FIDN =''26200206800''')as a inner join
    OPENQUERY(TSTESA5,'SELECT ZIPX_CODE, ZIPX_CITY_ST FROM STL01.ETAX_ZIPX')AS p ON a.MSTR_ZIPC=p.ZIPX_CODE



    Somehow this needs to be called as one big OPENQUERY statement...

    Thanks

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Instead of using two openqueries and joining it in SQL Server you should use join within one openquery.

  4. #4
    Join Date
    Mar 2011
    Posts
    3
    I agree, that was exactly my issue. I didn't know how to get it all under one OPENQUERY umbrella so that it would take no time to run and all the work would be done by Mainframe.

    In any case I was able to get one of the DB2 experts to help me with this and query is now running fine. Here is the correct language;
    strsql = "SELECT MSTR_FIDN, MSTR_NAM1, MSTR_NAM2, MSTR_STRT, MSTR_XTRA, MSTR_ZIPC, MSTR_ATYP, MSTR_ADAT, MSTR_AEND, MSTR_WQTR, MSTR_WEND, MSTR_PDAT, MSTR_PEND, ZIPX_CITY_ST FROM OPENQUERY(TSTESA5,'SELECT STL01.ETAX_MASTER.*, STL01.ETAX_ZIPX.* FROM STL01.ETAX_MASTER, STL01.ETAX_ZIPX WHERE MSTR_FIDN ='" & "'" & Me!MSTR_FIDN & "'' AND MSTR_ZIPC = ZIPX_CODE')"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •