Data Connectivity: Linking Text and Number Data Types

Print Page | Close Window

Linking Text and Number Data Types

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12536
Printed Date: 27 Apr 2024 at 11:52pm

Topic: Linking Text and Number Data Types

Posted By: danimal
Subject: Linking Text and Number Data Types
Date Posted: 08 Mar 2011 at 2:50am

In CR11, is there anyway to use a formula to do the linking between tables so that I can convert a number to text or text to number field so that I can join on a field with different data types in the two source tables?

Replies:

Posted By: DBlank
Date Posted: 08 Mar 2011 at 4:46am

use a command to convert the data and join the tables in the command

Posted By: danimal
Date Posted: 09 Mar 2011 at 3:22am

Thanks DBlank. I know how to use a formula to convert the data, but what command would I use to join the tables?

I am looking for a way to do this within CR and not via SQL commands. Can that be done???

Posted By: DBlank
Date Posted: 09 Mar 2011 at 3:55am

in your database expert

in the source for your report there is an option for 'Add Command'

double click on it and a box opens

write your SQL query here using a CAST in the join

select tableAfield1, tableA.field2,TableB.field1, etc.

from tableA

inner join

tableB

tableA.number field = CAST(TableB.textfield AS int)

this can replace all your tables

Posted By: danimal
Date Posted: 09 Mar 2011 at 8:53am

Trying to use your suggestion from earlier today, bu I am getting an error "Failed to retrieve data from database. Details: 42000[Microsoft]ODBC Microsoft Access Driver] Syntax error (missing operator)..." with the code below. What am I missing? The Customer ID field is a text data type in the tblFirstContact table and an integer in the tblCustomer table. They are tables in the same Access database. I really do appreciate you assistance.

Select
     tblCustomer."Customer ID",
     tblCustomer."Customer Name",
     tblCustomer."Address1",
     tblCustomer."City",
     tblCustomer."Region",
     tblFirstContact."Customer ID",
     tblFirstContact."Customer Name",
     tblFirstContact."Initial Contact"
FROM
     tblCustomer
INNER JOIN
     tblFirstContact
ON
     tblCustomer."Customer ID" = CAST(tblFirstContact."Customer ID" AS int)

Posted By: DBlank
Date Posted: 09 Mar 2011 at 8:58am

does it work if you get rid of the join?

Select
     tblCustomer."Customer ID",
     tblCustomer."Customer Name",
     tblCustomer."Address1",
     tblCustomer."City",
     tblCustomer."Region"
FROM
     tblCustomer

Posted By: danimal
Date Posted: 09 Mar 2011 at 9:47am

When I do it like your example I get a different error message - Too few parameters. Expected (3).

Posted By: DBlank
Date Posted: 09 Mar 2011 at 9:57am

to clarify

in the database expert

you expand your current connections

there is a connection to your access db

above that is an 'add command'

you opende the add command and entered

Select
     tblCustomer."Customer ID",
     tblCustomer."Customer Name",
     tblCustomer."Address1",
     tblCustomer."City",
     tblCustomer."Region"
FROM
     tblCustomer

there is also a parameter list inside the command (on the right) and this is empty.

Is this all accurate?

Posted By: chudok
Date Posted: 09 Nov 2012 at 2:22am

Select INVOICENo
from SO_INVOICEHEADER
inner join
RESUBMIT
on
SO_INVOICEHEADER."invoiceno" = CAST(RESUBMIT."invoicenumber" AS int)

I am trying to do this but it is failing on SO_INVOICEHEADER."invoiceno" = CAST(RESUBMIT."invoicenumber" AS int)

can you help me determine what I amdoing wrong.....

I get invalid use of '.','!'

then when i try this:

Select INVOICENo
from SO_INVOICEHEADER
inner join
RESUBMIT
on
invoiceno = CAST(invoicenumber AS int)

I get missing operator in query expression invoiceno0case(invoicenumber as int)

Posted By: chudok
Date Posted: 09 Nov 2012 at 2:26am

I should comment that the resubmit table is an excel table taking a text field to match my crystal string field..... so i am not even sure I am converting the right field type.

Print Page | Close Window