I need to do a query as follows:

select tcibd001.ccde
from tcibd001
where tcibd001.item(10;10) = :order
group by tcibd001.ccde
as set with 1 rows


There is something wrong in the phrase tcibd001.item(10;10). This should be similar to the substring function in SQL server.
Please advise.

You can use a substring (as you wrote in your post) in a program script , but i think you can't in a query. The SQL sintax for querys is more restrictive than a program script (i.e, you can't use {field1,field2...} refers to.. in query sintax but in script).

I guess you can use, something like this in report generated by query

detail.1:
before.layout:
lattr.print = (tcibd001.item(10;10) = order) ? true : false

where both tcibd001.item and order must be defined as report input fields.
But i don't really think this could be very "performant"

Hi pcolombo
Thanks for your reply.
Let me explain with an example.

The order number (2912010020) .

The tcibd001.item number is 10000000002912010020.

If the item number contains the order number, the query must return the country code (ccde) from the table tcibd001

I concatenate this ccde to a string variable and print that in my report.

line = strip$(itemdsca & " Sold on: " & soldon & "/" & str$(pono) & cr$() & lf$() & "Country Code " & ccde )

Can you please tell me, how can I add the expression lattr.print = (tcibd001.item(10;10) = order) ? true : false in the variable “line”?

I need to do a query as follows:

select tcibd001.ccde
from tcibd001
where tcibd001.item(10;10) = :order
group by tcibd001.ccde
as set with 1 rows


There is something wrong in the phrase tcibd001.item(10;10). This should be similar to the substring function in SQL server.
Please advise.

Hello,


select ..........
from ............
where tcibd001._index1 = {:1 & :2}
wherebind (1, " ")
wherebind (2, ?????????)
.........

Regards

Hi bdittmar
I changed my query as per your suggestion.

order ="2912890220"
select tcibd001.ccde
from tcibd001
where tcibd001.item = :1
as set with 1 rows
selectbind (1, ????????? & order & ????????????????????????????)
Now I am getting errors like
? not expected
Select do , endselect not expected.
what is wrong in my query?

Hi bdittmar
I changed my query as per your suggestion.

order ="2912890220"
select tcibd001.ccde
from tcibd001
where tcibd001.item = :1
as set with 1 rows
selectbind (1, ????????? & order & ????????????????????????????)
Now I am getting errors like
? not expected
Select do , endselect not expected.
what is wrong in my query?

Hello,

Read wherebind in DEV-Guide, please !

Something like:


order ="2912890220"
select tcibd001.ccde
from tcibd001
|where tcibd001.item = :1
where tcibd001._index1 inrange {:1 & :2}
and {:3 & :4}
selectbind (1, " ")
selectbind (2, :order)
selectbind (3, "ZZZZZZZZZ")
selectbind (4, (:order&"ZZZZZZZZZZZZZZ"))
as set with 1 rows
|selectbind (1, ????????? & order & ????????????????????????????)


Regards

As you posted previosuly you are in a query!. As far i know u cannot use wherebind or selectbind in querys but in program scripts

Are you want to filter by order? If it , why just dont simply join tdsls401, to tcibd001 and then put a filter in query for tdsls401.orno?. In another way you can't put a field in screen to filter.