Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 17 total |
Updating a field depending on other fields |
Wed, Oct 11 2006 4:27 AM | Permanent Link |
Calivers | 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 |
Wed, Oct 11 2006 5:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Oct 11 2006 6:05 AM | Permanent Link |
Calivers | 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 |
Wed, Oct 11 2006 7:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Oct 11 2006 9:30 AM | Permanent Link |
"Robert" | "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 |
Wed, Oct 11 2006 10:28 AM | Permanent Link |
Calivers | "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 |
Wed, Oct 11 2006 11:29 AM | Permanent Link |
"Robert" | "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. |
Wed, Oct 11 2006 12:07 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Oct 11 2006 12:17 PM | Permanent Link |
"Robert" | "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 |
Wed, Oct 11 2006 4:57 PM | Permanent Link |
calivers | "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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |