Dealing to convert the substring TSQL function to InstrRev

[XaRz]

Here is the TSQL query:

Select alma_codi, alma_desc, repr_desc, artialma_stock_actual,arti_desc
from alma
Join artialma on artialma_alma_pk = alma_pk
Join arti on arti_pk = artialma_arti_pk
Join clie on clie_codi = substring(alma_codi from 5 for 10)
join reprclie on reprclie_clie_pk = clie_pk
join repr on repr_pk = reprclie_repr_pk

where (artialma_stock_actual > 0)
AND alma_codi like '0101%'

group by alma_desc, artialma_stock_actual, repr_desc, arti_desc, alma_codi;

The problem is the join with:
Join clie on clie_codi = substring(alma_codi from 5 for 10)

How can I do this query in jet?
I working on something like:

AND ((CLIE.CLIE_CODI)=Mid(ALMA_CODI, InStrRev(ALMA_CODI, "0101 " + 1)))

But access don't let me to do this, any hint?

 

[PHV]

Replace this:
INNER JOIN ALMA
By this:
INNER JOIN CLIE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[XaRz]

Sorry I've made an error
The correct query is

SELECT ALMA.ALMA_CODI, CLIE.CLIE_CODI
FROM ALMA INNER JOIN CLIE ON CLIE.CLIE_CODI=MID(ALMA.ALMA_CODI,5,5);

And access lets me to save but running the query I receive:

ODBC: Falló la llamada
[ODBC Firebird Driver][Firebird]Dynamic SQL error
SQL Error Code= -104

 

[DotNetGnat]

please post your question in forum756

-DNG