×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

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