Programming with dBASE - Select.... substring

Author

Select.... substring

2006-12-11, 7:14 pm

Please consider the following:

table1 has got field1 (char) and field2 (numeric(4,2)

def15 1.00
abc12 2.00
abc56 3.00
abc35 4.00
abc42 5.00
ghi89 6.00

Now I need to get the following:

abc 14.00
def 1.00
ghi 6.00

the is to say, I need to sum up field2
for LEFT(field1, 3)... or SUBSTRING(field1 FROM 1 FOR 3)...
or whatever.

I played a bit with substring but could not make it to work.
Could anyone please help with a working SELECT?

Thank you very much

Carlos

Lysander

2006-12-11, 7:14 pm

ME schrieb:

> I played a bit with substring but could not make it to work.
> Could anyone please help with a working SELECT?

you would need to mix DATA-querying with STRING-Operations.
This - in general - is possible. But it is different from dataformat
to dataformat.

With Firebird it would be looking different from MS-SQL and from
DBF, and......

Especially the part of separating the first 3 letters from "field1"
and using that as a cursor for building the sums would be different
in every flavour of dataformat.

So, what format are you using?
If it's DBF, I would probably create a new field (for the group).
[replace all field3 with left(field1, 3)]

Instead of creating the field3, it would be even better to put the
grouping-factor into a separate table. But though it is more clean,
it is also more work to do :)

With Firebird, you could go without creating that additional field,
and without the additional table; just using a stored procedure.

With DBF you could play with self-joining, after you have created a
field3 in the same table. Like this:

select
a.field3,
sum(b.field2)
from
table1 a
join
table1 b
on b.field1 LIKE a.field3 || '%'
group by
a.field3

(untested!)

2006-12-11, 7:14 pm

Using .DBF files.
I see, things get a bit complicated, or at least require some more
programing.
I was hoping that a "complex" SELECT would do the job... ;-)
Thank you for your input
Carlos

> you would need to mix DATA-querying with STRING-Operations.
> This - in general - is possible. But it is different from dataformat
> to dataformat.
...........
> select
> a.field3,
> sum(b.field2)
> from
> table1 a
> join
> table1 b
> on b.field1 LIKE a.field3 || '%'
> group by
> a.field3

Ivar B. Jessen

2006-12-11, 7:14 pm

On Mon, 11 Dec 2006 19:17:35 +0200, in dbase.programming,
Subject: Select.... substring,
Message-ID: <jRBPVpUHHHA.1976@news-server>,
"ME" <bigpig@webmail.co.za> wrote:

>Please consider the following:
>
>table1 has got field1 (char) and field2 (numeric(4,2)
>
>def15 1.00
>abc12 2.00
>abc56 3.00
>abc35 4.00
>abc42 5.00
>ghi89 6.00
>
>
>Now I need to get the following:
>
>abc 14.00
>def 1.00
>ghi 6.00
>
>the is to say, I need to sum up field2
>for LEFT(field1, 3)... or SUBSTRING(field1 FROM 1 FOR 3)...
>or whatever.
>
>I played a bit with substring but could not make it to work.
>Could anyone please help with a working SELECT?

Try the code below my signature.

Ivar B. Jessen

//-----
close tables
if file("bigPig.dbf")
drop table bigPig
endif

create table bigPig(f1 char(5), f2 numeric(4,2))
insert into bigPig values("def15", 1.00)
insert into bigPig values("abc12", 2.00)
insert into bigPig values("abc56", 3.00)
insert into bigPig values("abc35", 4.00)
insert into bigPig values("abc42", 5.00)
insert into bigPig values("ghi89", 6.00)
*insert into bigPig values("def16", 1.00)

clear

select substring(f1 from 1 for 3) newf1, f2 from bigPig save to newPig
select newf1, sum(f2) newf2 from newPig group by newf1 order by newf1

list
drop table newPig
//-----

2006-12-11, 7:14 pm

> select substring(f1 from 1 for 3) newf1, f2 from bigPig save to newPig
> select newf1, sum(f2) newf2 from newPig group by newf1 order by newf1

I see, you suggest a temporary/intermediate table and then use it. Great.
I will adapt it to my case.
Thank you for that.
Carlos

Sponsored Links