Using Mid() in SQL Command to Join Tables

[Llazwas]

I'm using Crystal X with a provideX database writing a sales history report. I'm trying to use a sql command to get around a linking problem and having trouble.

Two of the fields I need to link are different lenghts:

"IM5_TransactionDetail"."TransactionRefNumber" is 10 characters
"AR_InvoiceHistoryHeader"."InvoiceNo" is 7 characters.

I need to link the right 7 characters of "IM5_TransactionDetail"."TransactionRefNumber" to the "AR_InvoiceHistoryHeader"."InvoiceNo" field.

This is the command I have so far but keep getting errors:

 SELECT "IM5_TransactionDetail"."TransactionCode", "IM5_TransactionDetail"."TransactionQty", "IM5_TransactionDetail"."TransactionDate", "IM5_TransactionDetail"."WarehouseCode", "IM1_InventoryMasterfile"."ItemDescription", "IM1_InventoryMasterfile"."ItemNumber", "IM5_TransactionDetail"."UnitCost", "IM5_TransactionDetail"."UnitPrice", "CI_Item"."UDF_KEY_ITEM", "AR_InvoiceHistoryHeader"."CustomerNo"
 FROM   "IM5_TransactionDetail" "IM5_TransactionDetail", "IM1_InventoryMasterfile" "IM1_InventoryMasterfile", "AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader", "CI_Item" "CI_Item"
 
WHERE  ("IM5_TransactionDetail"."ItemNumber"="IM1_InventoryMasterfile"."ItemNumber") AND [COLOR=blue]((mid("IM5_TransactionDetail"."TransactionRefNumber"),3,7)="AR_InvoiceHistoryHeader"."InvoiceNo")[/color] AND ("IM1_InventoryMasterfile"."ItemNumber"="CI_Item"."ItemCode")  O
RDER BY "IM1_InventoryMasterfile"."ItemNumber"

Any help is greatly appreciated

 

[hilfy]

Mid is a Crystal function. It won't work in a command. Instead you need to use the corresponding command for your database. This is usually something like "substr" or "substring". I've never used provideX, so I can't tell you for sure what it is there.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Llazwas]

I checked with ProvideX and they do allow mid funtionality. I'm just wondering if I have brackets or quotes in the wrong place or something.

 

[lbass]

mid("IM5_TransactionDetail"."TransactionRefNumber"),[red]4[/red],7)

-LB