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?

 

[DotNetGnat]

equivalent of

substring(alma_codi from 5 for 10)

is

substring(alma_codi,5,10)

-DNG

 

[DotNetGnat]

similarly

MID(alma_codi,5,10)

5 indicates the start position and 10 indicates the number of characters...

its the same for both substring and mid fucntions...

BUT what actually you mean when you say...alma_codi from 5 for 10??

-DNG

 

[XaRz]

I mean that the characters from place 5 to 10 is the string that I want.

 

[DotNetGnat]

then it should be

substring(alma_codi,5,5)

or


MID(alma_codi,5,5)

-DNG

 

[XaRz]

Ok, but I receive a message that the type of data doesn't match because I'm using:

SELECT ALMA.ALMA_CODI, ALMA.ALMA_DESC, REPR.REPR_DESC, ARTIALMA.ARTIALMA_STOCK_ACTUAL, ARTI.ARTI_DESC

FROM (REPRCLIE INNER JOIN REPR ON REPRCLIE.REPRCLIE_REPR_PK = REPR.REPR_PK) INNER JOIN ((ARTIALMA INNER JOIN (ALMA INNER JOIN CLIE ON ALMA.ALMA_CODI = CLIE.CLIE_PK) ON ARTIALMA.ARTIALMA_ALMA_PK = ALMA.ALMA_PK) INNER JOIN ARTI ON ARTIALMA.ARTIALMA_ARTI_PK = ARTI.ARTI_PK) ON REPRCLIE.REPRCLIE_CLIE_PK = CLIE.CLIE_PK

WHERE (((ARTIALMA.ARTIALMA_STOCK_ACTUAL)>0) AND ((CLIE.CLIE_CODI)=Mid("alma_codi,5,5")))

GROUP BY ALMA.ALMA_CODI, ALMA.ALMA_DESC, REPR.REPR_DESC, ARTIALMA.ARTIALMA_STOCK_ACTUAL, ARTI.ARTI_DESC

HAVING (((ALMA.ALMA_CODI) Like '0101%'));

I need to join in the where statment the field codi_client (integer) with a string. What I need to do? convert the string as a value? How?

 

[DotNetGnat]

yes, you need to do conversion and bring both the variable to the same data type so that you can match them up..

-DNG

 

[XaRz]

I'm lost. I don't know why access give me an error like "Doesn't match types in the expression", I've double checked every column in the joins and all is correct.

Perhaps the problem is in the Mid() function, but CLIE_CODI is a varchar(12), alma_codi is a varchar(18) and Mid(alma_codi,5,5) may return a varchar(5).

where is the type missmatch error?

 

[PHV]

Replace this:
Mid("alma_codi,5,5")
By this:
Mid(alma_codi,5,5)

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

 

[XaRz]

Same error message.

I've managed to make the report to the old fashioned way that it is exporting the query results to xls from ibexperts and importing the table in access to deal with the data.
Perhaps the problem is the kind of join that doesn't like access, because the join:

Join clie on clie_codi = substring(alma_codi from 5 for 10)

Thanks for your effort guys. Tektips rocks because alll of you!

 

[PHV]

And this ?
SELECT alma_codi, alma_desc, repr_desc, artialma_stock_actual,arti_desc
FROM ((((alma
INNER JOIN artialma ON artialma_alma_pk = alma_pk)
INNER JOIN arti ON arti_pk = artialma_arti_pk)
INNER JOIN clie ON clie_codi = Mid(alma_codi,5,5))
INNER JOIN reprclie ON reprclie_clie_pk = clie_pk)
INNER JOIN repr ON repr_pk = reprclie_repr_pk
WHERE artialma_stock_actual > 0
AND LEFT(alma_codi,4) = '0101'
GROUP BY alma_desc, artialma_stock_actual, repr_desc, arti_desc, alma_codi;

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

 

[XaRz]

Exacly the same error.

 

[PHV]

Exacly the same error
And where goes the cursor after the error ?

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

 

[XaRz]

At the end of the query. At the last character of the sentence GROUP BY.

 

[PHV]

the field codi_client (integer)
but CLIE_CODI is a varchar(12)
Which sentence is true ?

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

 

[XaRz]

The second. In fact I was thinking that the problem was this one. I'ven't been aware that CLIE_CODI is a varchar but include with this mental fix tha code doesn't work yet.

 

[PHV]

So, actually we haven't any syntax error but a type mismatch one, right ?
Just to be sure the error is where you suspect:
INNER JOIN clie ON Val(clie_codi) = Val(alma_codi)

BTW, no memo field ?

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

 

[XaRz]

This Select you wrote down't work. I've tested something like:

Select alma_codi, clie_codi 
from alma
INNER JOIN clie ON Val(clie_codi) = Val(alma_codi);

Is this you want me to try?
Then it doesn't work. Access say that "This expression of combination is not admited".

 

[PHV]

Do you have the type mismatch error with this ?
SELECT alma_codi, clie_codi
FROM alma
INNER JOIN clie ON clie_codi = Mid(alma_codi,5,5)

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

 

[XaRz]

doing

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

Message: "sintaxis error in Join clause"

And I can't do the query.