Incorrect information from a "View"

I am created a "view" like the one below
CREATE VIEW Tenant_Yearly AS
SELECT tn_proj as Company,
LTRIM(prj_name) as Company_Name,
tn_id as Tenant_ID,
ISNULL(monthly_rent.trm_amount,0) as Monthly_Rent_Amt,
ISNULL(monthly_other.tro_amount,0) as Monthly_Other_Amt,
ISNULL(monthly_rent.trm_amount*12,0) as Yearly_Rent_Amt,
ISNULL(monthly_other.tro_amount*12,0) as Yearly_Other_Amt,
ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
ISNULL(monthly_other.tro_amount*12/tn_sq_ft,0) as
Yearly_Other_Per_SqFt
FROM tenants
INNER JOIN projects
ON prj_id = tn_proj
LEFT OUTER JOIN
(SELECT tr_proj as trm_proj, tr_id as trm_id,
tr_amount as trm_amount
FROM ten_revenue trm WHERE trm.tr_code = 1)
as monthly_rent
ON trm_proj = tn_proj AND trm_id = tn_id
LEFT OUTER JOIN
(SELECT tr_proj as tro_proj, tr_id as tro_id,
sum(tr_amount) as tro_amount
FROM ten_revenue tro
WHERE tro.tr_code > 1 AND tro.tr_code < 90
GROUP BY tro.tr_proj, tro.tr_id)
as monthly_other
ON tro_proj = tn_proj AND tro_id = tn_id;
I have a program that selects the information from the "view", however
when my program selects the "Yearly_Rent_Per_SqFt", it doesn't select
the correct number. My Tenant's monthly rent amount is 1000.00 and the
Tenant's square feet is 1000, but the value that gets returned for the
Yearly_rent_per_sqft when selecting information from the view, is
20000000000000000{, and it should be 00000000000000120{.
Does anyone have any suggestions of why this is happening?mtt_trcy@.yahoo.com,

> ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
From which table is the column [tn_sq_ft] comming?
Can you include column [tn_sq_ft] in the "select" statement to be sure that
the value is 1000, same with [trm_amount]?
What data type are those columns?
AMB
AMB
"mtt_trcy@.yahoo.com" wrote:

> I am created a "view" like the one below
> CREATE VIEW Tenant_Yearly AS
> SELECT tn_proj as Company,
> LTRIM(prj_name) as Company_Name,
> tn_id as Tenant_ID,
> ISNULL(monthly_rent.trm_amount,0) as Monthly_Rent_Amt,
> ISNULL(monthly_other.tro_amount,0) as Monthly_Other_Amt,
> ISNULL(monthly_rent.trm_amount*12,0) as Yearly_Rent_Amt,
> ISNULL(monthly_other.tro_amount*12,0) as Yearly_Other_Amt,
> ISNULL(monthly_rent.trm_amount*12/tn_sq_ft,0) as Yearly_Rent_Per_SqFt,
> ISNULL(monthly_other.tro_amount*12/tn_sq_ft,0) as
> Yearly_Other_Per_SqFt
> FROM tenants
> INNER JOIN projects
> ON prj_id = tn_proj
> LEFT OUTER JOIN
> (SELECT tr_proj as trm_proj, tr_id as trm_id,
> tr_amount as trm_amount
> FROM ten_revenue trm WHERE trm.tr_code = 1)
> as monthly_rent
> ON trm_proj = tn_proj AND trm_id = tn_id
> LEFT OUTER JOIN
> (SELECT tr_proj as tro_proj, tr_id as tro_id,
> sum(tr_amount) as tro_amount
> FROM ten_revenue tro
> WHERE tro.tr_code > 1 AND tro.tr_code < 90
> GROUP BY tro.tr_proj, tro.tr_id)
> as monthly_other
> ON tro_proj = tn_proj AND tro_id = tn_id;
> I have a program that selects the information from the "view", however
> when my program selects the "Yearly_Rent_Per_SqFt", it doesn't select
> the correct number. My Tenant's monthly rent amount is 1000.00 and the
> Tenant's square feet is 1000, but the value that gets returned for the
> Yearly_rent_per_sqft when selecting information from the view, is
> 20000000000000000{, and it should be 00000000000000120{.
> Does anyone have any suggestions of why this is happening?
>|||tn_sq_ft is from the tenants table.
I put it is the select and it made no difference.
The data type of the Yearly_Rent_Per_SqFt column is decimal(38,17).
I think because the decimal part is so large, that it is contributing
to my problem, but I can't seem to get the decimal number to be a
smaller number. I think it would work if I could get the data type to
be decimal(38,2). I just can't seem to figure out how to do that.
Alejandro Mesa wrote:
> mtt_trcy@.yahoo.com,
>
> From which table is the column [tn_sq_ft] comming?
> Can you include column [tn_sq_ft] in the "select" statement to be sure tha
t
> the value is 1000, same with [trm_amount]?
> What data type are those columns?
>
> AMB
>
> AMB
>
> "mtt_trcy@.yahoo.com" wrote:
>|||Try using cast or convert in the expression,
ISNULL(
cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
numeric(7, 2)), 0) as Yearly_Rent_Per_SqFt,
AMB
"mtt_trcy@.yahoo.com" wrote:

> tn_sq_ft is from the tenants table.
> I put it is the select and it made no difference.
> The data type of the Yearly_Rent_Per_SqFt column is decimal(38,17).
> I think because the decimal part is so large, that it is contributing
> to my problem, but I can't seem to get the decimal number to be a
> smaller number. I think it would work if I could get the data type to
> be decimal(38,2). I just can't seem to figure out how to do that.
>
> Alejandro Mesa wrote:
>|||I tried your suggestion and the view gets created OK, but the data type
is still too big
the data type becomes decimal(22,10)
I tried lowering the numbers, but I could never get the second number
in the data type to be lower than 6. I need it to be 2.
Thank you for all your help. I really appreciate it. If you have any
other advice please send it my way.
Alejandro Mesa wrote:
> Try using cast or convert in the expression,
> ISNULL(
> cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
> numeric(7, 2)), 0) as Yearly_Rent_Per_SqFt,
>
> AMB
> "mtt_trcy@.yahoo.com" wrote:
>|||mtt_trcy@.yahoo.com,
Try casting the result also.
cast(
ISNULL(
cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
numeric(7, 2)), 0), as numeric(8, 2)
) as Yearly_Rent_Per_SqFt,
AMB
"mtt_trcy@.yahoo.com" wrote:

> I tried your suggestion and the view gets created OK, but the data type
> is still too big
> the data type becomes decimal(22,10)
> I tried lowering the numbers, but I could never get the second number
> in the data type to be lower than 6. I need it to be 2.
> Thank you for all your help. I really appreciate it. If you have any
> other advice please send it my way.
>
> Alejandro Mesa wrote:
>|||Alejandro,
Thank you so much. I think that will work for MSDE views.
Do you, by any chance, know if the "cast" will work when creating views
in Sybase 9?
Alejandro Mesa wrote:
> mtt_trcy@.yahoo.com,
> Try casting the result also.
> cast(
> ISNULL(
> cast(monthly_rent.trm_amount as numeric(9, 2)) * 12.00 / cast(tn_sq_ft as
> numeric(7, 2)), 0), as numeric(8, 2)
> ) as Yearly_Rent_Per_SqFt,
>
> AMB
> "mtt_trcy@.yahoo.com" wrote:
>|||'CAST' the entire results to the form that you desire.
cast( isnull( monthly_rent.trm_amount * 12.00 / cast(tn_sq_ft as decimal(7,
2)), 0 ), decimal(9,2) )
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<mtt_trcy@.yahoo.com> wrote in message
news:1151673822.716321.294790@.x69g2000cwx.googlegroups.com...
>I tried your suggestion and the view gets created OK, but the data type
> is still too big
> the data type becomes decimal(22,10)
> I tried lowering the numbers, but I could never get the second number
> in the data type to be lower than 6. I need it to be 2.
> Thank you for all your help. I really appreciate it. If you have any
> other advice please send it my way.
>
> Alejandro Mesa wrote:
>