using sql query to do (+, -, *, /) - ->HOW???!

[meracle]

I wish to use sql query to do some operations.
My 1st sql is shown as below:
(1) select i2.trade_in_discount from invoice i2, model_chassisno mc where i2.trade_in_value <> 0 and i2.model = mc.model and i2.model in('ZFL') and i2.chassis_no = mc.chassis_no

returned 2 resuls:

TRADE_IN_DISCOUNT
------------------------
5000.00
1359.75


2nd sql is shown as below:
(2) select ml.trade_in_amt from model_chassisno mc, invoice
i3, margin_limit ml where i3.model in('ZFL') and
i3.model=ml.model and i3.booking_date <=
ml.effective_date and i3.trade_in_value<>0 order by
ml.effective_date DESC FETCH FIRST 1 ROWS ONLY

returned 1 result:

TRADE_IN_AMT
------------
777

I wish to display my result using sql(1) - sql(2) which will return the result as below:

1
-----------
4223.00 (5000.00-777)
582.75 (1359.75-777)


I tried two ways to do it:
(A) select i2.trade_in_discount from invoice i2,
model_chassisno mc where i2.trade_in_value <> 0 and
i2.model = mc.model and i2.model in('ZFL') and i2.chass
is_no = mc.chassis_no - (select ml.trade_in_amt from
model_chassisno mc, invoice i3, margin_limit ml where
i3.model in('ZFL') and i3.model=ml.model and i3.booking_date <= ml.effective_date and i3.trade_in_value<>0 order by ml.effective_date DESC FETCH FIRST 1 ROWS ONLY)


(B) select i2.trade_in_discount - (select ml.trade_in_amt from model_chassisno mc, invoice i3, margin_limit ml where i3.model in('ZFL') and i3.model=ml.model and i3.booking_date <= ml.effective_date and
i3.trade_in_value<>0 order by ml.effective_date DESC
FETCH FIRST 1 ROWS ONLY) from invoice i2,
model_chassisno mc where i2.trade_in_value <> 0 and
i2.model = mc.model and i2.model in('ZFL') and i2.chassis_no = mc.chassis_no

Both of them return same error:

SQL0104N An unexpected token &quot;FETCH&quot; was found following &quot;.effective_date DESC&quot;. Expected tokens may include: &quot&quot;. SQLSTATE=42601


Can anyone tell me how to solve this problem??? in URGENT!!!

 

[MarcLodge]

Joey,
I the second SQL you have a table (model_chassisno) which appears to be unused. Also in both pieces of SQL you are using 'in ('ZFL')' when '= 'ZFL' would be better (I think).
Try the following SQL:

select i2.trade_in_discount - ml.trade_in_amt
from invoice i2,
model_chassisno mc
margin_limit ml
where i2.trade_in_value <> 0
and i2.model = mc.model
and i2.model = 'ZFL'
and i2.chassis_no = mc.chassis_no
and ml.model=i2.model
and i2.booking_date <= ml.effective_date
and ml.effective_date = (select max(ml2.effective_date
from margin_limit ml2
where ml2.model = ml.model )

Although you may have to add some more 'where' clauses to the subselect in order to get back the correct unique value you are after.

Let us know how you get on.
Marc

 

[blom0344]

FETCH FIRST .... etc will only work on the resultset, not on an intermediate result (just like order by) I guess, so try this using subsets like:

select (i2-amt) from
(select 'X' as dummy1,i2.trade_in_discount as i2 from invoice i2,
model_chassisno mc where i2.trade_in_value <> 0 and
i2.model = mc.model and i2.model in('ZFL') and i2.chass
is_no = mc.chassis_no) temp1,
(select 'X' as dummy2,ml.trade_in_amt as amt,
rank() over (order by ml.effective_date) as #rank from
model_chassisno mc, invoice i3, margin_limit ml where
i3.model in('ZFL') and i3.model=ml.model and i3.booking_date <= ml.effective_date and i3.trade_in_value<>0
and #rank =1) temp2
where temp1.dummy1 = temp2.dummy2

T. Blom
Information analyst
tbl@shimano-eu.com

 

[meracle]

I'd tried out Marc's idea, return error:

SQL0104N An unexpected token &quot;from&quot; was found following
&quot;x(ml2.effective_date&quot;. Expected tokens may include: &quot&quot;. SQLSTATE=42601

With T.Blom's idea, return error:
SQL0206N &quot;#RANK&quot; is not valid in the context where it is used. SQLSTATE=42703

 

[MarcLodge]

There's a bracket missing after effective_date in my one. Sorry, only desk checked it
Marc

 

[blom0344]

Sorry, Marc's strategy is probably the better one, this is getting too complicated:



select (i2-amount) from
(select 'X' as dummy1,i2.trade_in_discount as i2 from invoice i2,
model_chassisno mc where i2.trade_in_value <> 0 and
i2.model = mc.model and i2.model = 'ZFL' and i2.chass
is_no = mc.chassis_no) temp1,
(select dummy2 as dummy3, amt as amount from
(select 'X' as dummy2,ml.trade_in_amt as amt,
rank() over (order by ml.effective_date) as #rank from
model_chassisno mc, invoice i3, margin_limit ml where
i3.model in('ZFL') and i3.model=ml.model and i3.booking_date <= ml.effective_date and i3.trade_in_value<>0)temp2 where #rank =1) temp3
where temp1.dummy1 = temp3.dummy3

T. Blom
Information analyst
tbl@shimano-eu.com

 

[meracle]

Yeahh...I got it! Marc, thanks!

 

[MarcLodge]

Glad that it worked.
Marc

 

[meracle]

i need to return only one result. but why the query below return 7 rows? im sure that (chassis_no + model) is unique. where did the query goes wrong???

select effective_date, trade_in_amt from margin_limit ml, invoice i where branch='CRII' and i.model='ZFL' and i.chassis_no='A00011' and trade_in_value <> 0 and i.model=ml.model

 

[blom0344]

Are you sure that it is unique in BOTH tables?
Only then you may expect that it returns just one row...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[meracle]

no, inside margin_limit table don't have chassis_no...i know the reason. anywhere, thanks!

 

[MarcLodge]

Joey,
I did say that I though you might have to add more clauses in order to get a unique row. Have you now got it working in the way that you require?
Marc

 

[meracle]

actually Im in a frust condition..because im now want to combine 2 query by using report tool call &quot;Formula One&quot;.

(1) select i.trade_in_discount from invoice i where branch='CRII' and model='ZFL' and trade_in_value<>0

(2) select ml.trade_in_amt from margin_limit ml, invoice i2 where i2.branch='CRII' and i2.model='ZFL' and i2.chassis_no='A00011' and i2.booking_date >= ml.effective_date and ml.model=i2.model order by ml.effective_date desc fetch first 1 rows only

.....AND the i2.chassis_no will change dinamically according to the diffrent rows so i can get the trade_in_discount MINUS trade_in_amt by combining these 2 queries. but when using sql(1) - sql(2), error occured! [an unexpected token &quot;fetch&quot; was found following &quot;effective_date desc&quot;. Expected tokens may include: &quot&quot;. SQLSTATE=42601]

My sql query is below:

select distinct i.chassis_no, (select i2.trade_in_discount date from invoice i2 where i2.branch='CRII' and i2.model='ZFL' and i2.trade_in_value<>0) as A, (select ml.effective_date, ml.trade_in_amt from margin_limit ml, invoice i3 where i3.branch='CRII' and i3.model='ZFL' and chassis_no=i.chassis_no and booking_date >= effective_date and margin_limit.model=invoice.model order by effective_date desc fetch first 1 rows only) as B from invoice i;

I want to get these 2 value 1st, and then using &quot;Formula One&quot; to count the result by using the formula (Minus)...I know the critical issue here is i need to compare the booking_date and the effective_date to get the trade_in_amt...

I did try another way too, error occured too:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000]

My sql is like below:

select distinct i.chassis_no, (select ml.effective_date from margin_limit ml, invoice i2 where ml.model = i2.model and i2.chassis_no=i.chassis_no order by ml2.effective_date DESC ) as B from invoice i fetch first 1 rows only;

Im so tired oredi to try it and try it again...the hard step is to use this reporting tool...im not quite familiar with it...Can anybody teach me how to do??

 

[meracle]

Add some more..I did not use max(effective_date) because the result return is not the value I want. The max(effective_date) make every diffrent chassis_no return same trade_in_amt because the key here is:

invoice.model = margin_limit.model
AND
invoice.booking_date >= (margin_limit.effective_date order by DESC fetch first 1 rows only)
AND
invoice.chassis_no will always change.

If i use max(margin_limit.effective_date) here, sure all the rows will return same value to me...but if using FETCH, error occured too...that's why, i now become headache!!!