SQL Syntax

Database

SQL Syntax

I have a view:

Create view VW1 as SEL col1, col2, col3 from tab1;

and I can run following SEL.

SEL a.col1, a.col2, a.col3, b.col4 from 

( sel col1, col2, col3 from VW1 where col2 = 'ABC') a

left outer join

(SEL col1, Col3 as col4 from VW1 where col2 = 'ABC'

qualify row_number() over ( order by col1) = 1

) b 

on a.col1 = b.col1;

I need to change above SEL to a View so that user can access with simple query like SEL * from VW2 where col2 = 'ABC'

Any suggestions?

Thanks

Asher

7 REPLIES
Supporter

Re: SQL Syntax

did you try to create a view like

Create view VW2 as 
SEL a.col1,
a.col2,
a.col3,
b.col4
from
( sel col1,
col2,
col3
from tab1
where col2 = 'ABC'
) a
left outer join
(SEL col1,
Col3 as col4
from tab1
where col2 = 'ABC'
qualify row_number() over ( order by col1) = 1
) b
on a.col1 = b.col1;

If yes, what had been the issue?

If no, what had been preventing you from doing so?

Re: SQL Syntax

yes, I tired but it won't work because this view can not have the hard coded WHERE clause as this will be passed by user. Based on your syntax view should be like code below but this is not working for my requirement. The left outer join part is returing result from whole table while first sel is returning correct result. Both sides of join are not matching in the view sql like the way they do in query.

Create view VW2 as

SEL a.col1,

a.col2,

a.col3,

b.col4

from

( sel col1,

col2,

col3

from tab1

) a

left outer join

(SEL col1,

Col3 as col4

from tab1

qualify row_number() over ( order by col1) = 1

) b

on a.col1 = b.col1;

 

Senior Apprentice

Re: SQL Syntax

You probably need to a PARTITION to your ROW_NUMBER:

row_number() over (PARTITION BY col2 order by col1)

But you should check explain, it might do the STATS step before applying the WHERE filter.

Dieter

Supporter

Re: SQL Syntax

It's like Dieter suggested

try 

replace view test_db _uli.v_test1
as
select a.col1,
a.col2,
a.col3,
b.col4
from
(
select calendar_date as col1,
month_of_year as col2,
year_of_calendar as col3
from sys_calendar.calendar
) a
left join
(
select calendar_date as col1,
year_of_calendar as col4
from sys_calendar.calendar
qualify row_number() over (partition by month_of_calendar order by calendar_date) = 1
) b
on a.col1 = b.col1
;

select * from test_db _uli.v_test1
where col2 = 3 and col3 in ( 2012, 2013)
order by 1

Re: SQL Syntax

Partition By Clause on proper column solved the issue.

Thanks Ulrich and Dieter for your help.

Asher

x31
N/A

Re: SQL Syntax

can anyone tell me how to setup shortcut for comment-out code ??

Senior Apprentice

Re: SQL Syntax

You should post new questions as new posts.

And your question is unspecific, which tool? In SQLA there's already a shortcut, CTRL-D.

Dieter