Crystal Reports Data Type Problem 1

[apeasecpc]

I have a problem where crystal reports is converting a decimal value to integer and I can't figure out how to prevent it. I'm using Crystal Reports 2008 to access DHARMA ODBC data. My query is:

SELECT
jd_fy as FY,
jd_ap as AP,
jd_id as JID,
jd_entry as JE,
jd_line as JLN,
jd_ru as RU,
jd_ru_desc as RU_DESC,
jd_ca as CA_DETAIL,
to_number (jd_ca) as CA,
jd_ca_desc as CA_DESC,
CASE
WHEN jd_dc_ind='D' THEN jd_amt
ELSE -1*jd_amt
END AS AMT,
0.0 AS PAMT,
CASE
WHEN to_number (jd_ca) <200.0 THEN 1.0
ELSE 2.0
END AS CAT
FROM jd
WHERE
jd_fy={?PromptFY} and
jd_ap<={?PromptAP} and
to_number (jd_ca) <=299.99 AND
to_number (jd_ca) >=100.0

UNION
SELECT
jd_fy as FY,
jd_ap as AP,
jd_id as JID,
jd_entry as JE,
jd_line as JLN,
jd_ru as RU,
jd_ru_desc as RU_DESC,
jd_ca as CA_DETAIL,
to_number (jd_ca) as CA,
jd_ca_desc as CA_DESC,
0.0 AS AMT,
CASE
WHEN jd_dc_ind='D' THEN jd_amt
ELSE -1*jd_amt
END AS PAMT,
CASE
WHEN to_number (jd_ca) <200.0 THEN 1.0
ELSE 2.0
END AS CAT
FROM jd
WHERE
jd_fy={?PromptFY}-1 and
jd_ap<={?PromptAP} and
to_number (jd_ca) <=299.99 AND
to_number (jd_ca) >=100.0

My problem is the CA field is is a text representation of a decimal number used for our chart of accounts, having 3 significant digits both before and after the decimal point. For the prompts I'm using PromptFY=2012 and PromptAP=6. If I just use either one of the queries as my data source the CA field appears correctly as a decimal value, but when I combine both queries as a union CA truncates everything right of the decimal.

Adding to my delima, if I insert the following on top of my querie making a 3 part union the hardcoded CA of 1.1 added by this shows correctly, meaning that the truncating must be happening with the to_number function:

SELECT
0 as FY,
0 as AP,
0 as JID,
0 as JE,
0 as JLN,
0 as RU,
'' as RU_DESC,
'' as CA_DETAIL,
1.1 as CA,
'' as CA_DESC,
0.0 as AMT,
0.0 as PAMT,
0 as CAT
From jd
WHERE
jd_fy=2012 and
jd_ap=1 and
jd_id=1 and
jd_entry=1 and
jd_line=1
UNION

Does anyone have any ideas why this is happening or how to fix it?

 

[hilfy]

This is not exactly a Crystal issue, it's a problem in the database since that's where the number is being converted. I would look at the database documentation to see whether there's a way to specifically format the number from the call to ToNumber. I don't know the syntax for DHARMA, but is there a way to "Cast" a field as a number? That might be a way to get around the issue.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[apeasecpc]

You're right. I was able to use MS Query to look at the underlying data pulled by the same SQL statement which showed the same problem independent of Crystal. There must be a bug in the DHARMA to_number function that only occurs with a union query. I modified my query to use {fn CONVERT} casting to type SQL_FLOAT and was able to make it work correctly:

SELECT
jd_fy as FY,
jd_ap as AP,
jd_id as JID,
jd_entry as JE,
jd_line as JLN,
jd_ru as RU,
jd_ru_desc as RU_DESC,
jd_ca as CA_DETAIL,
{fn CONVERT (jd_ca, SQL_FLOAT)} as CA,
jd_ca_desc as CA_DESC,
CASE
WHEN jd_dc_ind='D' THEN jd_amt
ELSE -1*jd_amt
END AS AMT,
0.0 AS PAMT,
CASE
WHEN {fn CONVERT (jd_ca, SQL_FLOAT)} <200.0 THEN 1
ELSE 2
END AS CAT
FROM jd
WHERE
jd_fy={?PromptFY} and
jd_ap<={?PromptAP} and
{fn CONVERT (jd_ca, SQL_FLOAT)} <=299.99 AND
{fn CONVERT (jd_ca, SQL_FLOAT)} >=100.0

UNION
SELECT
jd_fy as FY,
jd_ap as AP,
jd_id as JID,
jd_entry as JE,
jd_line as JLN,
jd_ru as RU,
jd_ru_desc as RU_DESC,
jd_ca as CA_DETAIL,
{fn CONVERT (jd_ca, SQL_FLOAT)} as CA,
jd_ca_desc as CA_DESC,
0.0 AS AMT,
CASE
WHEN jd_dc_ind='D' THEN jd_amt
ELSE -1*jd_amt
END AS PAMT,
CASE
WHEN {fn CONVERT (jd_ca, SQL_FLOAT)} <200.0 THEN 1
ELSE 2
END AS CAT
FROM jd
WHERE
jd_fy={?PromptFY}-1 and
jd_ap<={?PromptAP} and
{fn CONVERT (jd_ca, SQL_FLOAT)} <=299.99 AND
{fn CONVERT (jd_ca, SQL_FLOAT)} >=100.0

Thanks for your insight.