All Forums Database
AsherShah 3 posts Joined 01/12
19 Mar 2013
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
 

ulrich 816 posts Joined 09/09
20 Mar 2013

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?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AsherShah 3 posts Joined 01/12
20 Mar 2013

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;

 

 

dnoeth 4628 posts Joined 11/04
20 Mar 2013

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

Dieter

ulrich 816 posts Joined 09/09
20 Mar 2013

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

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AsherShah 3 posts Joined 01/12
23 Mar 2013

Partition By Clause on proper column solved the issue.
Thanks Ulrich and Dieter for your help.
Asher
 

x31 1 post Joined 03/13
26 Mar 2013

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

dnoeth 4628 posts Joined 11/04
27 Mar 2013

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

Dieter

You must sign in to leave a comment.