×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Crystal Reports Data Type Problem

Crystal Reports Data Type Problem

Crystal Reports Data Type Problem

(OP)
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?

RE: Crystal Reports Data Type Problem

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
www.decisionfirst.com

RE: Crystal Reports Data Type Problem

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close