inventory FIFO

Hi,
I want to calculate inventory with FIFO METHOD. my tabel is :
inventory(doc char(10),tgl date, qty numeric(15,2), price
numeric(15,2))
doc tgl PRICE QTY
---
FP123 02/02/06 180 10
ASD12 07/02/06 -9
FP23 10/02/06 150 2
ASD14 11/02/06 -2
I want to result is as below:
doc tgl price qty
---
FP123 02/02/06 180 10
ASD12 07/02/06 180 -9
FP23 10/02/06 150 2
ASD14 11/02/06 180 -1
ASD14 11/02/06 150 -1
how to create sintak SQL SERVER ?
thank you for your advanced.
best regards,
alimIf I understand your problem correctly , the sybtax is;
SELECT doc,tgl,qty,price ORDER BY tgl ASC
Jack Vamvas
___________________________________
Receive free SQL tips - http://www.ciquery.com/sqlserver.htm
"alim" <alfen_lim@.yahoo.com> wrote in message
news:1141293436.115783.286230@.i40g2000cwc.googlegroups.com...
> Hi,
> I want to calculate inventory with FIFO METHOD. my tabel is :
> inventory(doc char(10),tgl date, qty numeric(15,2), price
> numeric(15,2))
> doc tgl PRICE QTY
> ---
> FP123 02/02/06 180 10
> ASD12 07/02/06 -9
> FP23 10/02/06 150 2
> ASD14 11/02/06 -2
> I want to result is as below:
> doc tgl price qty
> ---
> FP123 02/02/06 180 10
> ASD12 07/02/06 180 -9
> FP23 10/02/06 150 2
> ASD14 11/02/06 180 -1
> ASD14 11/02/06 150 -1
> how to create sintak SQL SERVER ?
> thank you for your advanced.
> best regards,
> alim
>|||hi jack,
cannot used syntax like :SELECT doc,tgl,qty,price ORDER BY tgl ASC
because
in tabel : ASD14 11/02/06 -2
I want to :
ASD14 11/02/06 180 -1
ASD14 11/02/06 150 -1
best regards,
alim|||Have you been over to www.dbazine.com and looked for my article on
inventory?

Invalid Data for Numeric when EXEC returns empty row

Hi, whenever the underlying query being called by EXEC in the following
has an empty result set I get the following error -- Invalid Data for
'Numeric' when EXEC returns empty row. However if I call the query
without using REPLACE (which I'm forced to do, because openquery does
not allow variables), I get just an empty result set. Whenever the
underlying query returns a non-empty result set, the code works without
error (regardless of wether there are nulls in the numeric column).

set @.switch ='5707550'
set @.start_date = '01-JAN-2006'
set @.end_date = '27-JAN-2006'
set @.month = 1
set @.year = 2006

set @.sql_str='
SELECT * FROM
(select MSC_KEY,
to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",
ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (
NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)
), 5)
as "PER_CPU_UTIL"
FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view v
WHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +
@.switch + ')
and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @.start_date + '
00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') and
to_date(''' + @.end_date + ' 23:59:00'', ''DD-MON-YYYY
HH24:MI:SS'')

group by MSC_KEY, trunc(tstamp)
)
WHERE rownum < 10000'

SET @.sql_str = N'select * from OPENQUERY(VISION, ''' +
REPLACE(@.sql_str, '''', ''') + ''')'

EXEC (@.sql_str);

Is there anyway to prevent this error?

Thanks,

CrazyCrazy Cat wrote:
> Hi, whenever the underlying query being called by EXEC in the following
> has an empty result set I get the following error -- Invalid Data for
> 'Numeric' when EXEC returns empty row. However if I call the query
> without using REPLACE (which I'm forced to do, because openquery does
> not allow variables), I get just an empty result set. Whenever the
> underlying query returns a non-empty result set, the code works without
> error (regardless of wether there are nulls in the numeric column).

code deleted to save space ...

> Is there anyway to prevent this error?
> Thanks,
> Crazy

Found the problem -- apparently one of the keys was of type numeric and
I wasn't converting it to varchar before selecting it -- funny it
worked when the result set was non-empty.

Thanks,

Crazy