OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL HELP (ORACLE10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL HELP [message #307409]

Tue, 18 March 2008 17:17

ravi214u
Messages: 153
Registered: February 2008
Location: CANADA

Senior Member

hi ,

select n_decrease_exp_amt,CASE WHEN v_pre_post_ind ='Pre' then n_decrease_exp_amt when v_pre_post_ind ='Post'then n_decrease_exp_amt * -1 end Redist,v_pre_post_ind from fct_op;

i wan this output

n_decrease_exp_amt Redistribution v_pre_post_ind
-50 -50 Pre
-25 -25 Pre
0 0 Pre
0 50 Post
0 25 Post
0 0 Post

But i M getting
n_decrease_exp_amt Redistribution v_pre_post_ind
-50 -50 Pre
-25 -25 Pre
0 0 Pre
0 0 Post
0 0 Post
0 0 Post

please help

thanks in advance

Report message to a moderator

Re: SQL HELP [message #307411 is a reply to message #307409]

Tue, 18 March 2008 17:26

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

You need to provide create table and insert statements for the data that your results are based on and explain why the results should be that way, what logic dictates that the 25 and 50 should be 0 instead. Please read the forum guide highlighted in yellow at the top of the forum page for what we expect. 0 times -1 is 0, not 25 or 50, the query is providing what you have asked for.

[Updated on: Tue, 18 March 2008 17:28]

Report message to a moderator

Re: SQL HELP [message #307419 is a reply to message #307411]

Tue, 18 March 2008 18:16

ravi214u
Messages: 153
Registered: February 2008
Location: CANADA

Senior Member

Sorry, i am again explaning it clearly.

Master table

create table VW_FCT_IR
(
  N_DECREASE_EXP_AMT          NUMBER(22,2),
  V_PRE_POST_IND              VARCHAR2(5)
);

select * from VW_FCT_IR

N_DECREASE_EXP_AMT	V_PRE_POST_IND
-50.00			Pre
-25.00			Pre
0.00			Pre
0.00			Post
0.00			Post
0.00			Post

I have to insert value from master table to the new table as 
shown below

The condition is when V_PRE_POST_IND is Pre insert 
N_DECREASE_EXP_AMT and when V_PRE_POST_IND is Post 
N_DECREASE_EXP_AMT * -1 in same column.


create table FCT_IR
(
  N_DECREASE_EXP_AMT          NUMBER(22,2),
  REDIST                      NUMBER(22,2),
  V_PRE_POST_IND              VARCHAR2(5)
);

 Required Output:-

N_DECREASE_EXP_AMT  Redist	V_PRE_POST_IND
-50.00		    -50		Pre
-25.00		    -25		Pre
0.00		    0		Pre
0.00		    50		Post
0.00		    25		Post
0.00		    0		Post

Thanks in advance

[Updated on: Wed, 19 March 2008 00:45] by Moderator

Report message to a moderator

Re: SQL HELP [message #307500 is a reply to message #307419]

Wed, 19 March 2008 00:47

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Where does 25 and 50 comes from?

Regards
Michel

Report message to a moderator

Re: SQL HELP [message #307511 is a reply to message #307500]

Wed, 19 March 2008 01:05

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

These are results of (-25 * -1) and (-50 * -1); at least, that's what I guessed.

But, where's the connection between three "Pre" and three "Post" records? I'd expect 0 as a result for all three "Post" records.

Does it mean that it doesn't matter what is written in all of the "Post" records? As you've put it, all you need are "Pre" records and for every "Pre" record you'd insert two records into a new table:

- #1 N_DECREASE_EXP_AMT |  N_DECREASE_EXP_AMT | Pre
- #2                  0 | -N_DECREASE_EXP_AMT | Post

Report message to a moderator

Re: SQL HELP [message #307650 is a reply to message #307511]

Wed, 19 March 2008 06:16

ravi214u
Messages: 153
Registered: February 2008
Location: CANADA

Senior Member

Hi Michel,
Thanks for replying.

what ever come in pre . the same will come in post with multipling (-1). so that the total will be 0.

Report message to a moderator

Re: SQL HELP [message #307657 is a reply to message #307650]

Wed, 19 March 2008 06:32

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But which ones? Which "post" with which "pre"? Anyone? What happens there is more "post" than "pre"? Or less?

Regards
Michel

Report message to a moderator

Re: SQL HELP [message #307671 is a reply to message #307657]

Wed, 19 March 2008 07:12

ravi214u
Messages: 153
Registered: February 2008
Location: CANADA

Senior Member

Micheal pre and post always remain same .. Bec they have to sum up to zero.

So the case is when V_PRE_POST_IND is Pre insert
N_DECREASE_EXP_AMT in Redist only for those rows where V_PRE_POST_IND ='Pre' and for same column when V_PRE_POST_IND is Post insert
N_DECREASE_EXP_AMT *(-1) into redist where V_PRE_POST_IND is pre and it will update those rows in redist where V_PRE_POST_IND ='Post' .

I thing u will understand with this example.

Report message to a moderator

Re: SQL HELP [message #307673 is a reply to message #307671]

Wed, 19 March 2008 07:22

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which "pre" is associated with which "post"?
I hope you understand my question.

Regards
Michel

Report message to a moderator

Re: SQL HELP [message #307677 is a reply to message #307673]

Wed, 19 March 2008 07:44

ravi214u
Messages: 153
Registered: February 2008
Location: CANADA

Senior Member

pre of N_DECREASE_EXP_AMT is associated with Post of redist

Report message to a moderator

Re: SQL HELP [message #307678 is a reply to message #307677]

Wed, 19 March 2008 08:04

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

pre of N_DECREASE_EXP_AMT is associated with Post of redist

This is completly meaningless.

We have this:

N_DECREASE_EXP_AMT	V_PRE_POST_IND
-50.00			Pre
-25.00			Pre
0.00			Pre
0.00			Post
0.00			Post
0.00			Post

Which "pre" is associated with which "post"?
Which "post" is 0? Which "post" is 25? Which "post" is 50?

Regards
Michel

Report message to a moderator

Re: SQL HELP [message #307680 is a reply to message #307678]

Wed, 19 March 2008 08:15

ravi214u
Messages: 153
Registered: February 2008
Location: CANADA

Senior Member

ok i will explain again
i have to populate redist.
the condition will be when V_PRE_POST_IND='Pre' insert N_DECREASE_EXP_AMT into redist. only 3 rows will be updated. right
Now same 3 rows will be updated in next 3 rows of same column where the condition is Post but multiplying (-1).

Report message to a moderator

Re: SQL HELP [message #307682 is a reply to message #307680]

Wed, 19 March 2008 08:21

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes but: "Which "post" is 0? Which "post" is 25? Which "post" is 50?"
Still don't have the answer for this.

Regards
Michel

Report message to a moderator

Previous Topic:	copy table from 1 database to another
Next Topic:	ORA-06502: PL/SQL: numeric or value error: number precision too large

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 15 02:04:50 CST 2025