cancel
Showing results for 
Search instead for 
Did you mean: 

How do we write outer join on single table in snowflake universe?

former_member211289
Participant
0 Kudos

Dear All,

We are facing issues over how to write outer join on single table in snowflake universe.

In detail, We have an object 'X' and in where clause we have the syntax as below in Oracle universe.

Object: X

WHERE Clause: Table1.Column1(+) =@Prompt('Select a Plan Rate Year ','N','Object\Class',MONO,CONSTRAINED)

Query:

How do we write outer join on single table in snowflake universe?

Table1. Column1(+) =@Prompt('Select a Plan Rate Year ','N','Object\Class',MONO,CONSTRAINED)

Although, we have set ANSI92 Parameter to Yes, (+) sign wasn't removed automatically.

Is it like Table1.Column1 RIGHT OUTER JOIN @Prompt('Select a Plan Rate Year ','N','Object\Class',MONO,CONSTRAINED)?

Please do the needful.

Thanks

Ramanaidu

Accepted Solutions (1)

Accepted Solutions (1)

daniele_tiles2
Active Participant
0 Kudos

Hi Ramainadu,

You can try to do like this:

- go to the dfx

- click insert a join

- choose your table

- write the condition with the prompt

- select the cubic for the outer join.

That maybe works, still is kind of weird.

However, your requirement is not clear yet: what are the reporting requirements? Why aren’t you trying to solve by making more queries in WebI?

HTH

Daniele

Answers (2)

Answers (2)

daniele_tiles2
Active Participant
0 Kudos

Hi Ramanaidu,

I'd never use the WHERE clause in an object, moreover on a measure.

As I told you, I'd look to put the prompt in the JOIN in the DFX, not in an object of the BLX.

Best regards


Daniele

former_member211289
Participant
0 Kudos

Ok Daniele,

I understand your explanation but here the requirement is

Let's say, we have a join in the DFX (Table.YEAR_RATE_VALID(+)=2017) in snowflake and how will we write an outer join on single table in snowflake universe without simply removing the (+) sign?

Oracle : Table.YEAR_RATE_VALID (+)=2017 ( + indicates outer join in oracle)

Snowflake : Table.YEAR_RATE_VALID(+)=2017, we shouldn't keep (+) sign in snowflake universe as it will not support via ODBC drivers to snowflake database.

Thanks

daniele_tiles2
Active Participant
0 Kudos

Hi Ramainadu,

if you set Ansi 92 it doesn't change the properties of the object. You should double click the join you'd like to have prompted, and set there the prompt. What is the object you are using? I guess that switching to Ansi 92 gets unnecessary that object.

Best regards

Daniele Tiles

former_member211289
Participant
0 Kudos

It's actually a measure object but in where clause.

Oracle : Table.YEAR_RATE_VALID (+)=2017 ( + indicates outer join in oracle)

Snowflake : Table.YEAR_RATE_VALID(+)=2017, we shouldn't keep (+) sign in snowflake universe as it will not support via ODBC drivers to snowflake database.

Is there any way to write outer join on single table in snowflake universe without simply removing the (+) sign?