Hello everybody!
I am trying to write an SQL query to select the records that are appearing more than once in tffst121 and exist in tfgld008. and then print them.
The problem is that I have accounts and they are declared in range so I get an error message.

The SQL I wrote is,
select tfgld008.leac,tfgld008.desc,tffst121.fstm,tffst121.accn,tffst121.lacf,tffst121.lact,tffst121.cmpm
from tfgld008, tffst121
where tfgld008._index1 inrange {:account.f} and {:account.t}
and tfgld008.subl= 00

and HERE IS THE PROBLEM! (tfgld008.leac between tffst121.lacf and tffst121.lact)
group by tfgld008.leac,tfgld008.desc,tffst121.fstm,tffst121.accn,tffst121.lacf,tffst121.lact,tffst121.cmpm
having count(*) > 1
selectdo
selectempty
message ("Unknown Account")
endselect

Proposal:

select tfgld008.leac,tfgld008.desc
from tfgld008
where tfgld008._index1 inrange {:account.f} and {:account.t}
and tfgld008.subl= 0
and 1 < (select count(*)
from tffst121
where tfgld008.leac between tffst121.lacf and tffst121.lact)

Kind regards,
Dick

Thanx a lot Dick!
I got really confused with this one!
It seems OK now.

It seems to have a problem...
For a reason I cannot explain when I run the session the connection with the server is lost!Although, the compile is OK.Can it be a problem in the script?

I tried putting this query in a script and it seems to work. Can you debug it and see where it fails?

Kinds regards,
Dick

Hi,

Possible cause: running the query in the wrong company (000), where specified tables do not exist, will result in bw connection getting lost..

Regards,
Klaas

In that case you should get error 506 - Table doesn't exists
But maybe you are right when having set up more complicated stuff.

Kind regards,
Dick

Dick, what I get when debugging is Fatal Error object 0 in process 9 not found.
I will try to do it another way...

Thanx a lot!

Question is when does this error happen? At the moment you reach the query or elsewhere?

Kind regards,
Dick

I changed the SQL and it works OK now.
Thanx for your help!

Hello everybody. I have a problem about SQL statement. I want to clean space in leading and trailing, so i used TRIM, LTRIM,RTRIM, but not excuse !!!


before generate TRIM:


select

tdsls401.item, | Item
tcibd001.dsca, | Description
tdsls401.orno, | Sales Order


from
tcibd001, | Items - General
tdsls401 | Sales Order Lines


where
tdsls401.item = tcibd001.item

AND after generate TRIM:


select

LTRIM(RTRIM(tdsls401.item)), | Item
tcibd001.dsca, | Description
tdsls401.orno, | Sales Order


from
tcibd001, | Items - General
tdsls401 | Sales Order Lines


where
tdsls401.item = tcibd001.item


ITS NOTICE: TABLE NOT PRESENT IN QUERY: LTRIM(RTRIM......


I was working with Baan LN, msql7, Infor Worktop 2.4.


Please, for me a introduce !!!!

Hello everybody. I have a problem about SQL statement. I want to clean space in leading and trailing, so i used TRIM, LTRIM,RTRIM, but not excuse !!!


before generate TRIM:


select

tdsls401.item, | Item
tcibd001.dsca, | Description
tdsls401.orno, | Sales Order


from
tcibd001, | Items - General
tdsls401 | Sales Order Lines


where
tdsls401.item = tcibd001.item

AND after generate TRIM:


select

LTRIM(RTRIM(tdsls401.item)), | Item
tcibd001.dsca, | Description
tdsls401.orno, | Sales Order


from
tcibd001, | Items - General
tdsls401 | Sales Order Lines


where
tdsls401.item = tcibd001.item


ITS NOTICE: TABLE NOT PRESENT IN QUERY: LTRIM(RTRIM......


I was working with Baan LN, msql7, Infor Worktop 2.4.


Please, for me a introduce !!!!

Hello,

use shiftX$()

BaanERP Programmers Guide


shiftc$(), shiftl$(), shiftr$()

--------------------------------------------------------------------------------

Syntax
string shiftc$( string strg(.) )
string shiftl$( string strg(.) )
string shiftr$( string strg(.) )

Description
These return the specified string with certain adjustments.

shiftc$() centers the string by equaling the number of leading and trailing spaces. If there is an odd number of spaces, the extra space becomes a trailing space. The returned string always has the same length as the input string.

shiftl$() removes any leading spaces from the input string.

shiftr$() moves the contents of the input string to the right, if there are trailing spaces. The length of the result is always the same as the length of the input string (this is not necessarily the declaration length).

Note
These functions do not change the input string.

Context
Bshell functions.

Example
shiftc$(" ABC ") | result " ABC "
shiftl$(" ABC ") | result "ABC "
shiftr$(" ABC ") | result " ABC"


or

strip$() only for trailing spaces

e.g. variable = shiftr$(shiftl$(variable))
RESULT is "ABC"

Use the search option in LN 6.1 Programmers Guide !

Regards

Hello,

use shiftX$()

BaanERP Programmers Guide


shiftc$(), shiftl$(), shiftr$()

--------------------------------------------------------------------------------

Syntax
string shiftc$( string strg(.) )
string shiftl$( string strg(.) )
string shiftr$( string strg(.) )

Description
These return the specified string with certain adjustments.

shiftc$() centers the string by equaling the number of leading and trailing spaces. If there is an odd number of spaces, the extra space becomes a trailing space. The returned string always has the same length as the input string.

shiftl$() removes any leading spaces from the input string.

shiftr$() moves the contents of the input string to the right, if there are trailing spaces. The length of the result is always the same as the length of the input string (this is not necessarily the declaration length).

Note
These functions do not change the input string.

Context
Bshell functions.

Example
shiftc$(" ABC ") | result " ABC "
shiftl$(" ABC ") | result "ABC "
shiftr$(" ABC ") | result " ABC"


or

strip$() only for trailing spaces

e.g. variable = shiftr$(shiftl$(variable))
RESULT is "ABC"

Use the search option in LN 6.1 Programmers Guide !

Regards

Dear bdittmar,

Thank so much for your introduce !!!

Im a newbie, i just have used Baan for over 1 month and everyday, my work just only have exported data from Baan to Excel by SQL Query in "SQL Text Manager". I have looking for all information about skill of using, SQl.... but It's difficult !!!!
Could you help me how i can use shiftr$, shiftl$...Such as: where was written their position (select or where or others in sql statement)? because when I get them in "SQL Test manager"(specify in query data), Appear Notice board: "Not table presen shiftr(....)" and Not excuse !!!!!

Could you tell some introduce more detail !!!!!


Thanks a lot,
Best regard !!!

Dear bdittmar,

Thank so much for your introduce !!!

Im a newbie, i just have used Baan for over 1 month and everyday, my work just only have exported data from Baan to Excel by SQL Query in "SQL Text Manager". I have looking for all information about skill of using, SQl.... but It's difficult !!!!
Could you help me how i can use shiftr$, shiftl$...Such as: where was written their position (select or where or others in sql statement)? because when I get them in "SQL Test manager"(specify in query data), Appear Notice board: "Not table presen shiftr(....)" and Not excuse !!!!!


Thanks a lot,
Best regard !!!

Hello,

select field/fields
from table/tables
where conditions ........
selectdo
var = ....... shiftr$(........)

selectempty

endselect

Regards

Hello,

select field/fields
from table/tables
where conditions ........
selectdo
var = ....... shiftr$(........)

selectempty

endselect

Regards


Thanks a lot !!!!

Best wish !!!