Linking datasources

[BigglesUK]

Hello,

Im running crystal reports XI and SQL 2000.

I have a weekly XML file I recieve. I need to compare it to my database to find if any item numbers that are in the file are not in the database.

Unfortunatly the XML file has the item number text parsed as " 12345678" where in my SQL database, its "12345678" with no leading spaces. I cant link the two together on the database expert because they dont properly match.

The only solution i could find was to not create a link, and then in the record selection put:
trim({BELORDER.PART_NO}) = {INVENTTABLE.ITEMID}

To trim the one that has the leading spaces, however this goes through a huge amount of work. Is there any better way I can do this? i would really like to create the initial link with this. I dont want to have to format the XML file in a different way each time I recieve it.

thanks,

Andrew

 

[MisterMo]

you could use the replace option on your SQL statement

something like:

where replace(BELORDER.PART_NO,' ','') = INVENTTABLE.ITEMID


-Mo

 

[BigglesUK]

That would be a great idea to use, but how can i implement it? I dont know how i can edit the SQL statement. I can view it thats about it as far as im aware.

 

[BigglesUK]

Managed to solve it.

I had to set up my connection as ODBC to be able to do a "Command", then I created my sql view which was:

select ltrim(part_no) as part_no
from xml

The left trim now trims fine and can be linked to my other odbc connection to my sql database.

thanks for your assistance.

 

[MisterMo]

I was trying to get the sql script with the xml file option but you beat me to it, well done.


-Mo