Convert field to text .. then link fields from two different DBs

[Designware]

Hi,

I have two separate databases that I need to link together based upon a common field (PO #) which is a text field in one database and a numeric field in another. I found a Tek Tips thread that appears to address the issue (

http://www.tek-tips.com/viewthread.cfm?qid=1614914

). I attempted the suggestion of lbass and tried to connect the two databases via SQL. Here is my sql attempt:

select "SVR-MAIN-TLS007"."pvtApDetHist"."PONum", "SVR-RESPONSE"."POHEAD"."PO_NUMBER"
from "SVR-RESPONSE"."POHEAD" inner join "SVR-MAIN-TLS007"."pvtApDetHist" on
to_text("SVR-RESPONSE"."POHEAD"."PO_NUMBER") = "SVR-MAIN-TLS007"."pvtApDetHist"."PONum"

The databases are on two separate DB servers, so I assumed I'd have to specify the server name in front of the table name. The error message I receive is:
-----------
Failed to open a rowset.
Details: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Provider for SQL Server
Description: 'to_text' is not a recognized built-in function name.
SQL State: 42000
Native Error: 195
-----------

I tried the other way around and used 'to_number' on the other DB which contains the text field, with a similar error. FYI, the 'pvtApDetHist' is a view ... in case that matters.

Both are Windows Server 2003 with SQL Server 2008 and 2005.

Using Crystal Reports 10.

Any help is appreciated.

Dale

 

[hilfy]

To_Text() and To_Number() are Crystal functions. In SQL Server you need to use the appropriate syntax for Convert() to convert a field from text to number or vice versa.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[kray4660]

to_text is not a function of MS-SQL (not sure where it works). Anyhow you might be able to use a CONVERT or CAST function. But you will need to know the data type (exactly) of the "SVR-MAIN-TLS007"."pvtApDetHist"."PONum" field.

 

[Designware]

Thanks hilfy and kray. I decided to try the CAST function first. Here is my syntax:

select "SVR-MAIN-TLS007"."pvtApDetHist"."PONum", "SVR-RESPONSE"."POHEAD"."PO_NUMBER", CAST(PO As Integer) "PONum" From "SVR-MAIN-TLS007"."pvtApDetHist"
from "SVR-RESPONSE"."POHEAD" inner join "SVR-MAIN-TLS007"."pvtApDetHist" on
"SVR-RESPONSE"."POHEAD"."PO_NUMBER" = "PO"

And the message I received was:

Failed to open a rowset.
Details: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Provider for SQL Server
Description: Incorrect syntax near the keyword 'from'.
SQL State 42000
Native Error: 156

I then tried to use convert command (below) in a little different spot, but got the exact same error message as above:

select "SVR-MAIN-TLS007"."pvtApDetHist"."PONum", "SVR-RESPONSE"."POHEAD"."PO_NUMBER"
from "SVR-RESPONSE"."POHEAD" inner join "SVR-MAIN-TLS007"."pvtApDetHist" on
"SVR-RESPONSE"."POHEAD"."PO_NUMBER" = CONVERT(Integer, PONum) From "SVR-MAIN-TLS007"."pvtApDetHist"

It's probably because I'm misapplying the syntax I researched.

Thanks again for any additional help.

Dale

 

[kray4660]

Syntax for CAST is almost correct CAST(PO As Int), but I see two From's in both statements, I do not believe that is allowed.

Syntax for CONVERT is CONVERT(int, PO). I have never tried to change the data type to do a join. But the basic select syntax is the following.

Select fields from tables inner (outer) join more_tables on tables.field = more_tables.field

I think in this case you want to CAST/CONVERT one of the fields on the join to match the other.

Lots of luck.

 

[IanWaterman]

As Kray said you have too many FROMs

Try

select "SVR-MAIN-TLS007"."pvtApDetHist"."PONum", "SVR-RESPONSE"."POHEAD"."PO_NUMBER"
from "SVR-RESPONSE"."POHEAD"
inner join "SVR-MAIN-TLS007"."pvtApDetHist"
on "SVR-RESPONSE"."POHEAD"."PO_NUMBER" = cast("SVR-MAIN-TLS007"."pvtApDetHist"."PONum" as int)

BTW To_Text() and To_number() is Oracle syntax

Ian

 

[Designware]

Thanks for the corrections! It got past the error I was getting.

I first received an error for an unknown object on the SVR-REPSONSE. I ended up taking off "SVR-RESPONSE" off the name of the table/field references because I am writing the command "from" SVR-RESPONSE as the starting point (see attached screen print ... perhaps I'm putting the command in the wrong place). It then went past that error on onto an unknown object error message on the database I'm trying to connect to using the join.

However, I don't think I can reduce the reference name for that object since it is on a completely different SQL server across the network. I also just thought of something else I don't know how to handle in the join. There's a different login and password for the second SQL server, which I'm sure must be put in the command somewhere. My Google search didn't return an example of the syntax for that. Still looking.

Here is the current syntax:

select "SVR-MAIN-TLS007"."TLS"."pvtApDetHist"."PONum", "POHEAD"."PO_NUMBER"
from "POHEAD"
inner join "SVR-MAIN-TLS007"."TLS"."pvtApDetHist"
on "POHEAD"."PO_NUMBER" = cast("SVR-MAIN-TLS007"."TLS"."pvtApDetHist"."PONum" as int)

Here's the current error message I'm receiving:

Failed to open a rowset.
Details: ADO Error Code: 0x80040e37
Source: Microsoft OLE DB Provider for SQL Server
Description: Invalid object name 'SVR-MAIN-TLS007.TLS.pvtApDetHist'.
SQL State: 42502
Native Error: 208

Note that I added "TLS" in the middle of the reference name, since I figured I better identify the database name on the server.

Can this type of join actually be done within Crystal Reports ... connecting two different tables on two different SQL servers?

Thanks for your help.

Dale