Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Data Connectivity
Message Icon Topic: Linking Text and Number Data Types Post Reply Post New Topic
Author Message
danimal
Newbie
Newbie
Avatar

Joined: 08 Mar 2011
Location: United States
Online Status: Offline
Posts: 5
Quote danimal Replybullet Topic: Linking Text and Number Data Types
    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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Mar 2011 at 4:46am
use a command to convert the data and join the tables in the command
IP IP Logged
danimal
Newbie
Newbie
Avatar

Joined: 08 Mar 2011
Location: United States
Online Status: Offline
Posts: 5
Quote danimal Replybullet 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?Confused   I am looking for a way to do this within CR and not via SQL commands.  Can that be done???

Edited by danimal - 09 Mar 2011 at 3:35am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
on
tableA.number field = CAST(TableB.textfield AS int)
 
this can replace all your tables


Edited by DBlank - 09 Mar 2011 at 4:14am
IP IP Logged
danimal
Newbie
Newbie
Avatar

Joined: 08 Mar 2011
Location: United States
Online Status: Offline
Posts: 5
Quote danimal Replybullet 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. Embarrassed
 
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)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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


Edited by DBlank - 09 Mar 2011 at 8:59am
IP IP Logged
danimal
Newbie
Newbie
Avatar

Joined: 08 Mar 2011
Location: United States
Online Status: Offline
Posts: 5
Quote danimal Replybullet 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).
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
chudok
Groupie
Groupie


Joined: 18 May 2010
Online Status: Offline
Posts: 70
Quote chudok Replybullet 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)
IP IP Logged
chudok
Groupie
Groupie


Joined: 18 May 2010
Online Status: Offline
Posts: 70
Quote chudok Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.027 seconds.