records not loading in correct order for concatenation

[raynac]

Hi all,

Our database stores a block of text in 60 character lines by reservation number, using suffix number as a sort so the lines are able to be presented correctly.

So the table has:

Res 1234  Suffix 10  Text

Res 1234  Suffix 20  2nd line of Text

Res 1235  Suffix 10  Text

Res 1235  Suffix 20  2nd line of Text

Res 1235  Suffix 30  3rd line of Text

Etc.

I want to concatenate all the lines of text into a single field.  Which seemed simple enough:

load
reservation_number as "Reservation Text Res Num",
Concat(sba_text,' ') as ResAbText

Group by reservation_number;

SQL SELECT * from fdresabtxt order by reservation_number, suffix_no asc;

But the lines concatenate in no particular order, making them nonsensical.

Then I tried:

resAbTxt:

load
reservation_number as "Reservation Text Res Num",
suffix_no as "Res Txt Suffix",
sba_text as "ResAbText1"
;

SQL SELECT * from fdresabtxt;

resAbTxt2:

load
"Reservation Text Res Num",
Concat(ResAbText1,' ') as ResAbText

resident resAbTxt
Group by "Reservation Text Res Num"
Order by "Reservation Text Res Num", "Res Txt Suffix";

drop table resAbTxt;

Still not working.

There must be a way around this.  Can anyone please tell me what it is?  What am I missing?

Thanks!

[Frank_Hartmann]

Maybe this:

Concat(YOURFIELD,' ',RowNo()) as ###Text

[raynac]

Hi Frank, thank you so much for taking the time to assist.  I implemented your suggestion, but it did not have any effect, unfortunately!

[StarinieriG · Accepted solution]

Hi,

you could try something like this

resAbTxt:

load
reservation_number as "Reservation Text Res Num",
suffix_no as "Res Txt Suffix",
sba_text as "ResAbText1"
;

SQL SELECT * from fdresabtxt;

resAbTxt2:

load
"Reservation Text Res Num",
Concat(ResAbText1,' ',"Res Txt Suffix") as ResAbText

resident resAbTxt
Group by "Reservation Text Res Num"
Order by "Reservation Text Res Num", "Res Txt Suffix";

drop table resAbTxt;

[raynac]

Brilliant!!!  Worked like a charm!  Thanks ever so much!