By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

help in query

P: 2
any can help me. iam new in oracle.

i have a table like this

key || datetime || amount

where key is varchar(7) and amount is number

i want make some view that show sum of amount per a mounth and look like this

key || year || amount_jan || amount_feb || amount_mar || amount_apr || .....

the problem is i can show the entry if the data dont have a mounth that related.
example :

key || datetime || amount
1 1/1/07 65
1 2/3/07 68
1 3/1/07 56
1 4/1/07 46
1 5/1/07 73
1 6/1/07 12
1 7/1/07 3
1 8/1/07 98
1 9/1/07 15
1 10/1/07 87
1 11/1/07 65
1 12/1/07 86
2 2/1/07 58
2 3/1/07 35
2 6/1/07 6


the result for query is

key || year || amount_jan || amount_feb || amount_mar || amount_apr ||
1 2007 65 68 56 46

amount_may || amount_jun || amount_jul || amount_aug || amount_sep ||
73 12 3 98 15

amount_oct || amount_nov || amount_des ||
87 65 86

the entry for key = 2 is not appears. i want to show it event the month is not exist and the value for that month is 0. any can help me? please... iam have try it for 3 weeks and i still dont get it.
Aug 13 '07 #1
Share this Question
Share on Google+
2 Replies


P: 2
hi all. anyone can help me. iam new in oracle.

i have a table like this

key || datetime || amount

where key is varchar(7) and amount is number

i want make some view that show sum of amount per a mounth and look like this

key || year || amount_jan || amount_feb || amount_mar || amount_apr || .....

the problem is i can show the entry if the data dont have a month that related.
example :

key || datetime || amount
1 1/1/07 65
1 2/3/07 68
1 3/1/07 56
1 4/1/07 46
1 5/1/07 73
1 6/1/07 12
1 7/1/07 3
1 8/1/07 98
1 9/1/07 15
1 10/1/07 87
1 11/1/07 65
1 12/1/07 86
2 2/1/07 58
2 3/1/07 35
2 6/1/07 6

i use this query :

[SELECT t1.key, to_char(t1.datetime,'YYYY'), t1.amount as amount_jan, t2.amount as amount_feb, ....., t12.amount as amount_des
FROM table t1, table t2, ..... table t12
WHERE t2.key = t1.key and t3.key = t1.key and .... and t12.key = t1.key and
to_char(t2.datetime,'YYYY') = to_char(t1.datetime,'YYYY') and .... and to_char(t12.datetime,'YYYY') = to_char(t1.datetime,'YYYY') and
to_char(t1.datetime,'MM') = '01 and .... and to_char(t12.datetime,'YYYY') = '12']

the result for query is

key || year || amount_jan || amount_feb || amount_mar || amount_apr ||
1 2007 65 68 56 46

amount_may || amount_jun || amount_jul || amount_aug || amount_sep ||
73 12 3 98 15

amount_oct || amount_nov || amount_des ||
87 65 86

the entry for key = 2 is not appears. i want to show it event the month is not exist and the value for that month is 0. any can help me? please... i have try it for 3 weeks and i still dont get it.
Aug 15 '07 #2

P: 12
If you have n tables together, you need a n-1 join condintions. for example you have 4 tables, minimum of 3 conditions.

once you check in where condition with another tables.






hi all. anyone can help me. iam new in oracle.

i have a table like this

key || datetime || amount

where key is varchar(7) and amount is number

i want make some view that show sum of amount per a mounth and look like this

key || year || amount_jan || amount_feb || amount_mar || amount_apr || .....

the problem is i can show the entry if the data dont have a month that related.
example :

key || datetime || amount
1 1/1/07 65
1 2/3/07 68
1 3/1/07 56
1 4/1/07 46
1 5/1/07 73
1 6/1/07 12
1 7/1/07 3
1 8/1/07 98
1 9/1/07 15
1 10/1/07 87
1 11/1/07 65
1 12/1/07 86
2 2/1/07 58
2 3/1/07 35
2 6/1/07 6

i use this query :

[SELECT t1.key, to_char(t1.datetime,'YYYY'), t1.amount as amount_jan, t2.amount as amount_feb, ....., t12.amount as amount_des
FROM table t1, table t2, ..... table t12
WHERE t2.key = t1.key and t3.key = t1.key and .... and t12.key = t1.key and
to_char(t2.datetime,'YYYY') = to_char(t1.datetime,'YYYY') and .... and to_char(t12.datetime,'YYYY') = to_char(t1.datetime,'YYYY') and
to_char(t1.datetime,'MM') = '01 and .... and to_char(t12.datetime,'YYYY') = '12']

the result for query is

key || year || amount_jan || amount_feb || amount_mar || amount_apr ||
1 2007 65 68 56 46

amount_may || amount_jun || amount_jul || amount_aug || amount_sep ||
73 12 3 98 15

amount_oct || amount_nov || amount_des ||
87 65 86

the entry for key = 2 is not appears. i want to show it event the month is not exist and the value for that month is 0. any can help me? please... i have try it for 3 weeks and i still dont get it.
Aug 16 '07 #3

Post your reply

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