I am trying to select a distinct substring from a table field. The oracle sql statement would look like this:
select distinct SUBSTR(tdsst010.sstr,64,16)
from tdsst010
This syntax does not obviously work in a baan script. Is there another way to do a similar select? This table has a large string field with a bunch of fixed width fields imbedded in it. I am looking to select only distinct items from this table and in that glob field is the only place i can find item number.

Any help would be greatly appreciated.

Thanks

matt

The use of substr is fine for quick and dirty queries in sqlplus, but not very reccommendable for permanent sessions.

You should realize that by using substr, you are forcing Oracle to go through the entire table anyway, since the substr itself is not indexed.

In other words,

select * from tdsst010.sstr
...
selectdo
if tdsst010.sstr(64,80) meets condition then
etc.

...may look a lot less elegant but will have the same effect in the end.

true,

thanks for your reply. I will try to select all records and filer my results after the fact as you showed above.

Thanks again

Matt

FYI

It is true that the use of a function on an indexed column will negate its use in Oracle. However, Oracle 8 has a nice feature called function indexes. It allows you to create an index that specifies a column or group of columns modified by a function so that that same configuration can be specified in a query and still make use of that index. Works really well.

I know that you are trying to re-work this in Baan, but thought this might be of interest to those using Oracle.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Hi Matt,

You can do it in BaaN SQL with the selectbind clause. You use the selectbind option when you are looking for records in a table based on array values, sub strings etc.

Please refer to the help manual for more details -

It is also possible to use a pseudo variable in the WHERE clause. The WHEREBIND function is then used to link a value to the pseudo variable. For example:

SELECT ppmod123.field1:5, ppmod123.field2:6
SELECTBIND(5, my_val1)
SELECTBIND(6, my_val2)
SELECTDO
....

ENDSELECT

SELECT ....
WHERE ppmod123.field1 = :1
WHEREBIND(1, 10 + sqrt(a+b))
SELECTDO
....
ENDSELECT

Used BAAN 4GL variables (with ':') are 'bound' automatically.

© 1998 Baan Development B.V. All rights reserved

Prashanth,

Sorry to revive an old thread. Could you post code that would solve the original question using selectbind. I believe I have a similar situation for warehouses. Shown below is the list of warehouses and I need the distinct first 3 characters. Better explained with some data.
Input | Output
----------------+-------------------------
CVA100 | CVA
----------------+-------------------------
HBA100 |
HBA101 | HBA
----------------+-------------------------
HDQ100 |
HDQ101 | HDQ
----------------+-------------------------
HDZ100 | HDZ
----------------+-------------------------
IDR100 |
IDR101 | IDR
----------------+-------------------------
KOG100 |
KOG101 |
KOG200 |
KOG900 | KOG
----------------+-------------------------
KOM100 |
KOM101 |
KOM102 | KOM
----------------+-------------------------
KOT100 |
KOT101 | KOT
----------------+-------------------------
SGO100 |
SGO101 |
SGO200 | SGO
----------------+-------------------------
TJO100 |
TJO101 | TJO

Personally I do not believe that this is solved using selectbind. So should be a nice problem to chew on :).

Vamsi -
In what context? Are you trying to print a report like that? Or, trying to select based on the first 3 char's of some field value? If selecting based on the first 3 characters, just use a range:



start.value = "CVA"
end.value = "CVAZZZ" |* Fill however appropriate

select table
from table
where table.value(or index) inrange :start.value and :end.value



Not sure what you are trying to do though.

I need to work on my communication skills :D. Need a select statement that works on the data that I shown as input and my output should be the stuff on the output. In my example I have 21 records that I can choose from (that is the total number of records). And my resulting set should have 10 records. The problem statement is the same as the one stated by lehighwiz. I need unique values of the first three characters. May be the below code that does not work will explain further.

select tcmcs003.cwar(1;3)
from tcmcs003
group by tcmcs003.cwar(1;3)

hmmm Vamsi, I had few ideas to deal with it but they didnt work.

I guess the problem chewed my brain now... :mad:

I give up for now, if I find a solution I will let you know.

It's ugly as hell, but this would work:


long lngTF
long x
long y
string strList(3;10) | 10 being the number of max expected results

x=1
select tcmcs003.cwar
from tcmcs003
selectdo
lngTF = 0
| test if value already in list
for y=1 to x-1
if strList(y,1)=tcmcs003.cwar(1;3) then
lngTF = 1
endif
endfor

| if new value then add to list
if lngTF = 0 then
strList(x,1)=tcmcs003.cwar
x=x+1
endif
endselect

| you now have all possible values in your list

for y=1 to x-1
print strList(y,1)
endfor


Not recommended for large transactions though.

Hey .. seems like the o'le 1 record behind processing to simulate the grouping. Have used this for many occasions, and ok for large processes.

rough Pseudocode:


set totals to 0
prev.cwar = ""
select tcmcs003 records
order by tcmcs003._index1
selectdo
if tcmcs003.cwar(1;3) <> prev.cwar(1;3) then
if not isspace(prev.cwar) then
Print records based on Prev Record values & totals
set totals to 0
endif
endif
set.prev.record.values()
increment totals
selecteos
Print records based on Prev Record Values & totals
endselect

...
function set.prev.record.values()
{
prev.cwar = tcmcs003.cwar
prev..... = tcmcs003.....
}



Probably forgetting something, but you get the picture.

Lisa/Francesco,

Thanks for the responses. I have a working solution on those lines. Was curious about doing it in a better way. Since Prashanth had kindly posted the manual contents of select.bind to the forum I thought he had a solution which used select.bind in his mind.

Prashanth,

Look at the confusion you caused. <Vamsi dons his moderator hat> Stop posting those manual contents unless they are relevant to the topic at hand :D!

Vamsi,

Not sure why selectbind is being suggested as the solution. It doesn't allow you to select only a portion of the field. But allows you to bind a psuedo variable to a field.

EG:
select tcmcs003.cwar:cwar
is the same as
select tcmcs003.cwar:1
selectbind(1, cwar)

However, to get around your warehouse problem, why not just create a variable that is only 3 characters in length and bind that variable to the warehouse code. Thus, the variable will only hold the first 3 characters of the warehouse. For example;


string my.cwar(3)
string hold.cwar(3)

select tcmcs003.cwar:1
from tcmcs003
order by tcmcs003.cwar
selectbind(1, my.cwar)
selectdo
if hold.cwar <> my.cwar then
hold.cwar = my.cwar
| Perform functions here
| rprt_send()
endif
endselect

Vamsi,

I wanted to try out something which might work but it didnt... I didnt want to be lead in a known path so I wanted to explore a different solution.

I know you are too smart and I knew you had a solution for your problem.

Here is a possible solution/example if you like -

if spool.open("","", 1) then
select ttadv200.cses
from ttadv200
where ttadv200.cpac <> "tt"
group by ttadv200.cses
order by ttadv200.cses
selectdo
if name <> ttadv200.cses(1;5) then
name = ttadv200.cses(1; 5)
select ttadv200.*
from ttadv200
where ttadv200.cses(1;5) = :name
order by ttadv200._index1
selectdo
spool.pr.line = ttadv200.cpac & ttadv200.cmod &
ttadv200.cses & ttadv200.vers &
ttadv200.rele & ttadv200.cust
spool.line()
endselect
endif
selecteos
spool.close()
endselect
endif


I post the contents of manual here, and its relevant because all the commands/options are not available on all BaaN Versions.

Refer to a post today -

Application Locking (http://www.baanboard.com/baanboard/showthread.php?s=&postid=21483#post21483)


Application Locking
Thanks for everyone's input. Basically you have confirmed my interpretation.

Just one more thing to note. The documentation that I have on appl.delete does not mention that only the owner of a lock can remove a lock.

It is helpful to know such things. - Joy


We all know how good BaaN documentation is. :D

I guess I dont have a hat but a cute hair style and not bald, so you can (don) your hat ... ;) hehehe

And if we have learned anything today, it's that if vamsi claims not to know something, he is most likely setting you up ;)