Home » SQL & PL/SQL » SQL & PL/SQL » SQL HELP (ORACLE10g)
SQL HELP [message #307409] Tue, 18 March 2008 17:17 Go to next message
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

Re: SQL HELP [message #307411 is a reply to message #307409] Tue, 18 March 2008 17:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where does 25 and 50 comes from?

Regards
Michel
Re: SQL HELP [message #307511 is a reply to message #307500] Wed, 19 March 2008 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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
Re: SQL HELP [message #307650 is a reply to message #307511] Wed, 19 March 2008 06:16 Go to previous messageGo to next message
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.

Re: SQL HELP [message #307657 is a reply to message #307650] Wed, 19 March 2008 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: SQL HELP [message #307671 is a reply to message #307657] Wed, 19 March 2008 07:12 Go to previous messageGo to next message
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.

Re: SQL HELP [message #307673 is a reply to message #307671] Wed, 19 March 2008 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which "pre" is associated with which "post"?
I hope you understand my question.

Regards
Michel
Re: SQL HELP [message #307677 is a reply to message #307673] Wed, 19 March 2008 07:44 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
pre of N_DECREASE_EXP_AMT is associated with Post of redist
Re: SQL HELP [message #307678 is a reply to message #307677] Wed, 19 March 2008 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: SQL HELP [message #307680 is a reply to message #307678] Wed, 19 March 2008 08:15 Go to previous messageGo to next message
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).


Re: SQL HELP [message #307682 is a reply to message #307680] Wed, 19 March 2008 08:21 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
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:
  


Current Time: Tue Dec 06 06:20:34 CST 2016

Total time taken to generate the page: 0.13719 seconds