Prev: Mixet.se® - International Business Advertising
Next: string concatenation via Group By
From: JIACHENG SUN on 25 Jan 2010 09:49
table 1
ref code
1 code_1
1 code_2
2 code_3
2 code_5
........
........

table 2
code code_description value
code_1 description1 3.4
code_2 description2 3.5
code_3 description3 0
code_4 description4 0
code_5 description5 0

how to write a sql to get the result below:

ref code description value
1 code_1 description1 3.4
1 code_2 description2 3.5
1 code_3 description3 0
1 code_4 description4 0
1 code_5 description5 0
2 code_1 description1 3.4
2 code_2 description2 3.5
2 code_3 description3 0
2 code_4 description4 0
2 code_5 description5 0
............
.............

I tried to use outer join and nvl function, but still couldn't get the
result i need. Cos if we have 1000 refs in table 1, it is impossible
to use nvl. how do your guys think?

thanks so much!!
From: Michel Cadot on 25 Jan 2010 12:15

"JIACHENG SUN" <sunjcb(a)googlemail.com> a �crit dans le message de news:
538246a1-9c3c-4c6f-aa3a-1bf7f2ea55d4(a)p24g2000yqm.googlegroups.com...
| table 1
| ref code
| 1 code_1
| 1 code_2
| 2 code_3
| 2 code_5
| .......
| .......
|
| table 2
| code code_description value
| code_1 description1 3.4
| code_2 description2 3.5
| code_3 description3 0
| code_4 description4 0
| code_5 description5 0
|
| how to write a sql to get the result below:
|
| ref code description value
| 1 code_1 description1 3.4
| 1 code_2 description2 3.5
| 1 code_3 description3 0
| 1 code_4 description4 0
| 1 code_5 description5 0
| 2 code_1 description1 3.4
| 2 code_2 description2 3.5
| 2 code_3 description3 0
| 2 code_4 description4 0
| 2 code_5 description5 0
| ...........
| ............
|
| I tried to use outer join and nvl function, but still couldn't get the
| result i need. Cos if we have 1000 refs in table 1, it is impossible
| to use nvl. how do your guys think?
|
| thanks so much!!

It is useless to repost the same question in other Oracle forums,
the same people answer to all of them.

So, once again:
Provide the statements for the simple example.
Explain with WORDS the rule to get the result.

Regards
Michel


From: Gerard H. Pille on 25 Jan 2010 14:22
JIACHENG SUN wrote:
> table 1
> ref code
> 1 code_1
> 1 code_2
> 2 code_3
> 2 code_5
> .......
> .......
>
> table 2
> code code_description value
> code_1 description1 3.4
> code_2 description2 3.5
> code_3 description3 0
> code_4 description4 0
> code_5 description5 0
>
> how to write a sql to get the result below:
>
> ref code description value
> 1 code_1 description1 3.4
> 1 code_2 description2 3.5
> 1 code_3 description3 0
> 1 code_4 description4 0
> 1 code_5 description5 0
> 2 code_1 description1 3.4
> 2 code_2 description2 3.5
> 2 code_3 description3 0
> 2 code_4 description4 0
> 2 code_5 description5 0
> ...........
> ............
>
> I tried to use outer join and nvl function, but still couldn't get the
> result i need. Cos if we have 1000 refs in table 1, it is impossible
> to use nvl. how do your guys think?
>
> thanks so much!!

This looks like a simple full outer join to me. What "syntax" did you try? No NVL needed,
methinks.
From: Mark D Powell on 25 Jan 2010 15:11
On Jan 25, 9:49 am, JIACHENG SUN <sun...(a)googlemail.com> wrote:
> table 1
> ref     code
> 1       code_1
> 1       code_2
> 2       code_3
> 2       code_5
> .......
> .......
>
> table 2
> code      code_description    value
> code_1    description1         3.4
> code_2    description2         3.5
> code_3    description3         0
> code_4    description4         0
> code_5    description5         0
>
> how to write a sql to get the result below:
>
> ref    code       description      value
> 1      code_1    description1       3.4
> 1      code_2    description2       3.5
> 1      code_3    description3       0
> 1      code_4    description4       0
> 1      code_5    description5       0
> 2      code_1    description1       3.4
> 2      code_2    description2       3.5
> 2      code_3    description3       0
> 2      code_4    description4       0
> 2      code_5    description5       0
> ...........
> ............
>
> I tried to use outer join and nvl function, but still couldn't get the
> result i need. Cos if we have 1000 refs in table 1,  it is impossible
> to use nvl. how do your guys think?
>
> thanks so much!!

Please do not cross-post. This thread is also posted to the server
forum where a potential answer has been posted.

From: JIACHENG SUN on 26 Jan 2010 11:24
thanks so much for ALL your help.

It does help me a lot. I just finish all the script i need. :)

Sure, next time, i will post as much as info i could. Have a nice day!!
 | 
Pages: 1
Prev: Mixet.se® - International Business Advertising
Next: string concatenation via Group By