Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
Maybe this:
Concat(YOURFIELD,' ',RowNo()) as ###Text
Hi Frank, thank you so much for taking the time to assist. I implemented your suggestion, but it did not have any effect, unfortunately!
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;
Brilliant!!! Worked like a charm! Thanks ever so much!