Database Views and Outer Joins (SQL)

building semantic layer
1

Hi,

I have searched but found nothing that helped answer my problem.

I got my dba to create a view on a SQL database which I use in designer to get around certain problems I was having creating specific objects. This worked perfectly. However when I use any object created from the view table with any in a table with and outer join on I get the following error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Joined tables cannot be specified in a query containing outer join operators. View or function ‘V_Collision_Vehicle’ contains joined tables.4424

Can anyone think why this is a happening or perhaps offer me some kind of solution as I am stumped.

Thanks in advance

Dan

bhafcdan (BOB member since 2005-01-21)

2

What’s the definition/SQL of your view, please?

Andreas :de: (BOB member since 2002-06-20)

3

Just a shot in the dark…you may want to get this created as an actual table instead of a view. I think this problem is arising because of the fact that a view is ‘created’ / run on demand. There seems to be a conflict in that the tables are joined to create the view are conflicting with the outer joins on your universe when you run the query as whole…

Mak 1 :uk: (BOB member since 2005-01-06)

4

I have a Person Table, A Vehicle Table and a Contributory Factor Table and these are all joined in a view, but are also used as tables seperately in the universe

Sorry I know very little about the database side - Is it possible for them to be joined in a table at database level then rather than just in a view? and do you think my dba will be willing to do this.

Confused

Dan

bhafcdan (BOB member since 2005-01-21)

5

I’m assuming that Andreas is asking this because he suspects that you have outer joins in your view. I would take this assumption a step further and assume that you are using SQL Server without setting the outer join parameter in your prm file. This thread will probably help you.

Regards,
Mark

6

Thanks for your reply mark,

I had looked at that thread earlier and you are correct in assuming that I have not altered the .prn file. This is a universe that is going to be used by many users through WebI - will altering the .prn file only work for my local full client machine?

Cheers
Dan

bhafcdan (BOB member since 2005-01-21)

7

Yes, the PRM file is machine specific. This will need to be amended on any of your servers that start an instance of the full client - either ZABO or Full client report run over infoview - for use by your users.
Fat clients need to have the amended prm file on their machine for the changes to take effect.
Finally, if you are required to run Webi instances using these settings, there a different set of prm type files to amend - the names of which escape me off the top of my head… :?

Mak 1 :uk: (BOB member since 2005-01-06)

8

Thanks again for your reply.

I have tried changing the .prm file on my local machine to check that it works but to no avail.

What prm file do i need to change? I have tried changing sqlsrv.prm and the odbc.prm files - changing this line

usual

to

FULL_ODBC

Is this correct?

Can someone guide me through the process please

Thanks in advance

Dan

bhafcdan (BOB member since 2005-01-21)

9

Dan,

Please can you also do what Andreas suggested and post the code used to create your view, as there could be an number of uissues here…

Cheers

Mak 1 :uk: (BOB member since 2005-01-06)