viewibm-midrangecrystal-reports-xiibm-ifs

Crystal reports not connectiing to Views from AS/400 IFS

I have a strange case here. I created a View in AS/400. I need to have view not join logical because I need to do a UNION ALL. The view is created and I went to IFS and granted permissions ALL. But when I open Crystal Reports, make the connection to iSeries, and go to that library of the View, it is not showing, so I cannot use.

Is there anything else I need to do?

I just tried something else. this is the view:

CREATE VIEW MKLIB/BEMPLOCM AS
   ((SELECT  LMRIDC, LMCOM#, LMWHS#, LMLOC1, LMLOC2, LMLOC3, LMLTPC, LMLCT1
     FROM
        ((SELECT  LMRIDC, LMCOM#, LMWHS#, LMLOC1, LMLOC2, LMLOC3, LMLTPC, LMLCT1
          FROM ASTDTA/ICLOCMLM WHERE LMLTPC IN ('PCK', 'PAL', 'RAK')) t1
     EXCEPTION JOIN
        (SELECT * FROM ASTDTA/ICBALMIE) t2
           ON LMLOC1=IELOC1 AND LMLOC2=IELOC2 AND LMLOC3=IELOC3 )
     EXCEPTION JOIN
        (SELECT * FROM ASTDTA/ICBLDTIR) t3
           ON LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3 ))

Now in Crystal reports there is also COMMAND to use to get your data, there you can make the same query but although I want it on the AS/400 for time consideration, the union is needed otherwise i have to run the queries via CL all day. SO I took the above code from the SELECT point, and got this error:

failed to retrieve data from the database. Details: HY000 IBM ISeries ACCESS ODBC DRIVER (DB2 UDB) sql 5016 - Qualified object name ICLOCMLM not valid. Vendor code 5016.

Not sure what that means.

Solution

  • Use the GRANT statement to control SQL privileges.

    If the view is over regular files you may also have to use the commands GRTOBJAUT, EDTOBJAUT and RVKOBJAUT to modify authorization on those objects.