| Author |
Message |
danimal
Newbie
Joined: 08 Mar 2011
Location: United States
Online Status: Offline
Posts: 5
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Mar 2011 at 4:46am |
|
use a command to convert the data and join the tables in the command
|
|
IP Logged |
|
danimal
Newbie
Joined: 08 Mar 2011
Location: United States
Online Status: Offline
Posts: 5
|
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???
Edited by danimal - 09 Mar 2011 at 3:35am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
danimal
Newbie
Joined: 08 Mar 2011
Location: United States
Online Status: Offline
Posts: 5
|
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)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
danimal
Newbie
Joined: 08 Mar 2011
Location: United States
Online Status: Offline
Posts: 5
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
chudok
Groupie
Joined: 18 May 2010
Online Status: Offline
Posts: 70
|
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 Logged |
|
chudok
Groupie
Joined: 18 May 2010
Online Status: Offline
Posts: 70
|
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 Logged |
|
|
|
