Crystal reports not connectiing to Views from AS/400 IFS

0
votes

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.

3
viewibm-midrangecrystal-reports-xiibm-ifs
What does the IFS have to do with permissions of a VIEW? – James Allman
I thought that might be a reason why this view does not appear in list of objects to use in Crystal. in IFS before it did not have ALL for permissions so I changed it. – Booksman
Normally, when people write IFS, they mean a stream file, not an object in the QSYS.LIB file system. That's why James is asking what the IFS has to do with anything. You can't create a view in a stream file system. – Buck Calabro
OK I wrote a view, i want to use this view in crystal reports but it does not list it in the library when i see the tables and views in the crystal. My question is, what is the reason it's not being seen? – Booksman
VIew is written on the 400 via the STRSQL. On 400 I can run a query against this. WHat must I do to have the crystal reports see this view? – Booksman

3 Answers

1
votes

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.

1
votes

"It does not list it in the library when I see the tables and views in crystal."

Are you sure that the view is actually in the library you think?

What may have happened is that it may have been created in another library. Try checking in QGPL library, or if you have a library that matches your user profile name, check there.

0
votes

If the question is 'Why do I get Qualified object name ICLOCMLM not valid.' the answer is probably that you are using *SQL naming and the statement you are running is using *SYSTEM naming. Try changing FROM ASTDTA/ICLOCMLM to FROM ASTDTA.ICLOCMLM and see if the 5016 error goes away.