how to join 2 tables

[st33chen]

hi, all

if i have 2 tables :

table1 (stocks)

stk_no  stk_date  stk_qty
------  --------  -------
abcd    20081101       10
abcd    20081115       20
abcd    20081121       15
 

table2 (calendar)

cal_date  workingday
--------  ----------
...
20081101  Y
20081102  Y
20081103  N
20081104  N
...
20081130  Y
...
 

i want to get (stk_date between 20081101 and 20081130, forget workingday)

stk_no  stk_date  stk_qty
------  --------  -------
abcd    20081101       10
abcd    20081102       10    <-- stk_qty of 20081101
abcd    20081103       10    <-- stk_qty of 20081101
...
abcd    20081114       10    <-- stk_qty of 20081101
abcd    20081115       20    
abcd    20081116       20    <-- stk_qty of 20081115
abcd    20081117       20    <-- stk_qty of 20081115
...
abcd    20081120       20    <-- stk_qty of 20081115
abcd    20081121       15
abcd    20081121       15    <-- stk_qty of 20081121
abcd    20081121       15    <-- stk_qty of 20081121
...
abcd    20081130       15    <-- stk_qty of 20081121
 

how to write a select statement without stored-functions.

thanks in advance.

[st33chen]

sorry, some were mis-typed ( part of "want to get")

stk_no stk_date stk_qty
------ -------- -------
abcd 20081101 10
abcd 20081102 10 <-- stk_qty of 20081101
abcd 20081103 10 <-- stk_qty of 20081101
...
abcd 20081114 10 <-- stk_qty of 20081101
abcd 20081115 20
abcd 20081116 20 <-- stk_qty of 20081115
abcd 20081117 20 <-- stk_qty of 20081115
...
abcd 20081120 20 <-- stk_qty of 20081115
abcd 20081121 15
abcd 20081122 15 <-- stk_qty of 20081121
abcd 20081123 15 <-- stk_qty of 20081121
...
abcd 20081130 15 <-- stk_qty of 20081121

[debasisdas]

You do not need to join the tables for that.

what is the query that you are working on ?

[st33chen]

hi,

if i use table1 only, how do i generate
the records having stk_date between '20081102' and '20081114' ?
(i only have 3 records -- ie. 20081101, 20081115, 20081121 -- in table1 as described)

my query string seemed to be (what i tried was more complicated, so i simplified it):
select table1.stk_no, table2.cal_date, table1.stk_qty
from table2, table1
where table2.cal_date=table1.stk_date(+)
and table2.cal_date between '20081101' and '20081130'

currently, i'm trying the LAG function.

thank you.

[Pilgrim333]

@st33chen
Hi,

The following should be sufficient, you do not need the join:

select table1.stk_no
     , table1.stk_date
     , table1.stk_qty
from table1
where table1.stk_date between '20081101' 
                          and '20081130'
 

Pilgrim.

[Haque123]

hii try finding the other way through oracle site so that ul learn more