I have here a Code-Example:
(Strangely the result is '*', different from my app).

drop table if exists Table1
;
CREATE TABLE "\Memory\Table1"
(
Pos1 Integer,
AH Boolean,
PRIMARY KEY (Pos1)
)
;
Insert INTO Table1 (Pos1,AH) VALUES (1,True);
Insert INTO Table1 (Pos1,AH) VALUES (2,True);
Insert INTO Table1 (Pos1,AH) VALUES (3,False);
Insert INTO Table1 (Pos1,AH) VALUES (4,True);
Insert INTO Table1 (Pos1,AH) VALUES (5,False);

drop table if exists Table2
;
CREATE TABLE "\Memory\Table2"
(
Star CHAR(10)
)
;
Insert INTO Table2 (Star) VALUES ('''');

update Table2 set Star = if (AH = True then Star + '*' else Star + '_') from Table2, Table1  
;
update Table2 set Star = Star + '''';  


SELECT Star FROM Table2

Why are you mixing memory tables and disk tables?

R

"calivers" <calivers@hotmail.com> wrote in message
news:7BAA3106-2165-48FE-8FB3-522FC51FDE37@news.elevatesoft.com...
>I have here a Code-Example:
> (Strangely the result is '*', different from my app).
>
> drop table if exists Table1
> ;
> CREATE TABLE "\Memory\Table1"
> (
> Pos1 Integer,
> AH Boolean,
> PRIMARY KEY (Pos1)
> )
> ;
> Insert INTO Table1 (Pos1,AH) VALUES (1,True);
> Insert INTO Table1 (Pos1,AH) VALUES (2,True);
> Insert INTO Table1 (Pos1,AH) VALUES (3,False);
> Insert INTO Table1 (Pos1,AH) VALUES (4,True);
> Insert INTO Table1 (Pos1,AH) VALUES (5,False);
>
> drop table if exists Table2
> ;
> CREATE TABLE "\Memory\Table2"
> (
> Star CHAR(10)
> )
> ;
> Insert INTO Table2 (Star) VALUES ('''');
>
> update Table2 set Star = if (AH = True then Star + '*' else Star + '_')
> from Table2, Table1
> ;
> update Table2 set Star = Star + '''';
>
>
> SELECT Star FROM Table2
>
>
>
>

calivers


If you use DBSys, set the database to Memory, get rid of all the \memory\ references you get '**_*_'
Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in
news:891604B0-5092-40A0-BCCB-9CEA958EEDDD@news.elevatesoft.com:

> If you use DBSys, set the database to Memory, get rid of all the
> \memory\ references you get '**_*_' Roy Lambert

and since the order doesn't depend on the primary key (perhaps it's just
raw-data-order?) when doing a cartesian join,  you can get around that
with a little tweaking:

drop table if exists Table1
;

CREATE TABLE Table1
(
Pos1 Integer,
AH Boolean,
PRIMARY KEY (Pos1)
)
;

/* insertion order not important here,
  but changes the result in original version */

Insert INTO Table1 (Pos1,AH) VALUES (2,True);
Insert INTO Table1 (Pos1,AH) VALUES (5,False);
Insert INTO Table1 (Pos1,AH) VALUES (3,False);
Insert INTO Table1 (Pos1,AH) VALUES (1,True);
Insert INTO Table1 (Pos1,AH) VALUES (4,True);

drop table if exists Table2
;

CREATE TABLE Table2
(
Star CHAR(10)
)
;

Insert INTO Table2 (Star) VALUES ('');

UPDATE Table2
SET Star = SUBSTRING(Star+REPEAT(' ' FOR Pos1),1,Pos1-1) + IF (AH = True
THEN '*' ELSE ' ') + SUBSTRING(Star,Pos1+1,LENGTH(Star))
FROM Table2, Table1  
;


SELECT Star, LENGTH(Star) LenStar FROM Table2

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

Chris,

Thanks a lot, it works like a Charm !

Calivers

Calivers <calivers@hotmail.com> wrote in news:6E95441D-DF60-402F-801C-
DE30D6D11AA2@news.elevatesoft.com:

> Chris,
>
> Thanks a lot, it works like a Charm !

Well, it was very definitely a joint effort - I agreed with Roy's initial
comments about it not being possible, until I saw your own code!

And now I know it's possible, I think it may come in very handy for me too.

so thanks everybody
--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

Chris,

<< and since the order doesn't depend on the primary key (perhaps it's just
raw-data-order?) >>

Yep, DBISAM reads the table in raw form without any indexes, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com