Hello

I have a Table1:
Pos   AH
1     True
2     False
3     True

and Table2:
String CHAR(3) = '   '  


Now I should update the stringfield in Table2 with '* *' (Only 1 Record).
That means I have a '*' on Position 1 and 3 when AH = True otherwise a Space.
Is this possible with SQL?

Thanks

Calivers


Yes if you have something to join the tables on. If they are only one column each then no.

Please post the full structures.

Roy Lambert

Roy,

Maybe you didn't quite understand my concern.
Maybe I have to use navigational code. (Pos is an Integer)

Pos   AH
1     True
2     False
3     True
4     True
5     False

Froms this I should build a single string like this: '* ** '

Thanks anyway

Calivers

Calivers


You're right I didn't understand - sorry. I don't think DBISAM has anything to allow that so you will (as you guessed) have to use navigational code.


Roy Lambert

"Calivers" <calivers@hotmail.com> wrote in message
news:D97F5D2B-1214-4454-AE41-B61202E5980E@news.elevatesoft.com...
> Hello
>
> I have a Table1:
> Pos   AH
> 1     True
> 2     False
> 3     True
>
> and Table2:
> String CHAR(3) = '   '
>
>
> Now I should update the stringfield in Table2 with '* *' (Only 1 Record).
> That means I have a '*' on Position 1 and 3 when AH = True otherwise a
> Space.
> Is this possible with SQL?
>

You do a cartesian join, where all the cols in each table are joined.

update t2 set s = if (AH then s + '*' else s + ' ') from t2, t1

If you insist on using space to indicate false (IMO a bad idea, because it
is hard or impossible to tell if the last space represents a value when
looking at the result) then the s field in table 2 has to be fixedchar, not
string.

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"Calivers" <calivers@hotmail.com> wrote in message
news:D97F5D2B-1214-4454-AE41-B61202E5980E@news.elevatesoft.com...
> Hello
>
> I have a Table1:
> Pos   AH
> 1     True
> 2     False
> 3     True
>
> and Table2:
> String CHAR(3) = '   '
>
>
> Now I should update the stringfield in Table2 with '* *' (Only 1 Record).
> That means I have a '*' on Position 1 and 3 when AH = True otherwise a
> Space.
> Is this possible with SQL?
>

You do a cartesian join, where all the cols in each table are joined.

update t2 set s = if (AH then s + '*' else s + ' ') from t2, t1

If you insist on using space to indicate false (IMO a bad idea, because it
is hard or impossible to tell if the last space represents a value when
looking at the result) then the s field in table 2 has to be fixedchar, not
string.

Robert



Hello Robert

Thanks for your suggestion, but unfortunaltely it seem DBISAM
reorders the Fields while doing the update, so
the Result in the above Example is ' **', the '*' are always at the end int the resulting field.
(I have Pos defined as PRIMARY KEY ASC)

Calivers

"Calivers" <calivers@hotmail.com> wrote in message
news:3B6ADFF2-6F9C-47F1-9342-EBD96EDA0FED@news.elevatesoft.com...
>
> Hello Robert
>
> Thanks for your suggestion, but unfortunaltely it seem DBISAM
> reorders the Fields while doing the update, so
> the Result in the above Example is ' **', the '*' are always at the end
> int the resulting field.
> (I have Pos defined as PRIMARY KEY ASC)
>

I don't know how (or if it's even possible) to determine the order of
updates using a cartesian join. Sorry.

Robert


I can't figure out what's going on behind the scenes even. A cartesian join will result in three rows so presumably the update runs for each which is why you expected the result you did (damn sneaky of you).

The problem you forgot (well you hinted at it) is that DBISAM trims trailing blanks so

Row 1 String is set to '*'
Row 2 String is set to '* ' but DBISAM trims it back to '*'
Row 3 String is set to '**'

The solution is not to use space but some other character

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:09C8E20B-9FD8-492A-AECB-50473DA1EA38@news.elevatesoft.com...
>
> The problem you forgot (well you hinted at it) is that DBISAM trims
> trailing blanks so
>

Moi forget? Pleeez. I specifically said that if you wanted to use spaces,
you had to use a fixed character field, not string. Still, I have no idea
how to force the update to occur in a specific sequence.

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:09C8E20B-9FD8-492A-AECB-50473DA1EA38@news.elevatesoft.com...
>
> The problem you forgot (well you hinted at it) is that DBISAM trims
> trailing blanks so
>

Moi forget? Pleeez. I specifically said that if you wanted to use spaces,
you had to use a fixed character field, not string. Still, I have no idea
how to force the update to occur in a specific sequence.

Robert

Robert

Yes, but the spaces are no problem. They always appear at the beginning and the '*' at the end.
Maybe Tim should fix this, that the update occurs in a specific sequence

Greets Calivers