469,328 Members | 1,275 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

how to join 2 tables

hi, all

if i have 2 tables :

table1 (stocks)
Expand|Select|Wrap|Line Numbers
  1. stk_no  stk_date  stk_qty
  2. ------  --------  -------
  3. abcd    20081101       10
  4. abcd    20081115       20
  5. abcd    20081121       15
  6.  
table2 (calendar)
Expand|Select|Wrap|Line Numbers
  1. cal_date  workingday
  2. --------  ----------
  3. ...
  4. 20081101  Y
  5. 20081102  Y
  6. 20081103  N
  7. 20081104  N
  8. ...
  9. 20081130  Y
  10. ...
  11.  
i want to get (stk_date between 20081101 and 20081130, forget workingday)
Expand|Select|Wrap|Line Numbers
  1. stk_no  stk_date  stk_qty
  2. ------  --------  -------
  3. abcd    20081101       10
  4. abcd    20081102       10    <-- stk_qty of 20081101
  5. abcd    20081103       10    <-- stk_qty of 20081101
  6. ...
  7. abcd    20081114       10    <-- stk_qty of 20081101
  8. abcd    20081115       20    
  9. abcd    20081116       20    <-- stk_qty of 20081115
  10. abcd    20081117       20    <-- stk_qty of 20081115
  11. ...
  12. abcd    20081120       20    <-- stk_qty of 20081115
  13. abcd    20081121       15
  14. abcd    20081121       15    <-- stk_qty of 20081121
  15. abcd    20081121       15    <-- stk_qty of 20081121
  16. ...
  17. abcd    20081130       15    <-- stk_qty of 20081121
  18.  
how to write a select statement without stored-functions.

thanks in advance.
Jan 16 '09 #1
5 1829
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
Jan 16 '09 #2
debasisdas
8,127 Expert 4TB
You do not need to join the tables for that.

what is the query that you are working on ?
Jan 16 '09 #3
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.
Jan 17 '09 #4
Pilgrim333
127 100+
@st33chen
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. select table1.stk_no
  2.      , table1.stk_date
  3.      , table1.stk_qty
  4. from table1
  5. where table1.stk_date between '20081101' 
  6.                           and '20081130'
  7.  
Pilgrim.
Jan 23 '09 #5
hii try finding the other way through oracle site so that ul learn more
Feb 19 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Matt | last post: by
4 posts views Thread by Anthony Robinson | last post: by
2 posts views Thread by dskillingstad | last post: by
2 posts views Thread by Bennett Haselton | last post: by
7 posts views Thread by Shanimal | last post: by
3 posts views Thread by Zeff | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.