concatenating columns in list

[NigelS]

Greetings

I've been mucking around for a the last two days trying to figure a way of extracting the description from a list.(see attachment) e.g Special Feature, and placing the extracted value into an array. The problem I'm having is that only the first portion of the description is extracted, i.e "Special". I've tried using the 'split', 'word' commands and just can get it right. Any guidelines would be appreciated.

What happens is that although the column that has the various description, whatever command I use, it appears that the command sees the space between the words as a column and therefore only brings the first portion of the word. I've tried different styles of code to but quotes around the description put with no avail

This is the code that generates the list

Code: Select all

# -------------

function populatePropertyDetail pPropertySubType
   
   local tSql
   local tSqlQuery, tConnectionID, tSqlResult, tLoginFailed 
   
   put false into tLoginFailed
   put empty into lgPropertyDetailList
   
   if GetDataBaseID() is empty then
      openDatabase GetPlatformDesc()
   end if 
   
   put GetDataBaseID() into tConnectionID
 
   put "SELECT pd2st.propSubTypeID, pdt.datatype, pd.* " & \ 
        "FROM propertydetail pd " & \ 
        "JOIN propdetail2propsubtype pd2st ON pd.ID = pd2st.propDetailID " & \ 
        "JOIN propertydetailstype pdt ON pdt.ID = pd.propDetailsType " & \ 
        "WHERE pd2st.propSubTypeID = " & pPropertySubType & \
        " AND active = 1" into tSqlQuery
   
   put revDataFromQuery(,,tConnectionID,tSqlQuery) into tSqlResult
   if tSqlResult begins with "revdberr" then
      put true into tLoginFailed
   else
      put tSqlResult into lgPropertyDetailList
end if
   
   closeDatabase
   
   return tLoginFailed
   
end populatePropertyDetail 

Thx
Nigel

[dunbarx]

Hi.

What are the delimiters for the data returned from your SQL query? Are they tab and return?

If so, did you split accordingly:

split yourData by return and tab

But it looks like the "Special" and the "Features" are separated by a space, as they would be if they came from the same field in the database record. In that case, you should:

split yourData by return and space.

This may be more complex, in that the two words mentioned are likely within a tab delimited item.

Just a few thoughts, that the words are getting lost in the delimiters somehow. Write back with those delimiters.

Craig Newman

[NigelS]

Thx Craig, I will try your suggestion an see what results I get. Other than that I suppose I could do two queries one with the details and the other with the description and then loop through the one while using a unique key to get the description. A bit long winded I know. Gota get this thing conquered, been at it for to long. I'm expecting my son to phone any time know wondering what's happening to his program even thou I'm the old man, he holds the whip presently. - I'm kidding of course.

[NigelS]

After a couple of days in trying to resolve this issue I have come to a satisfactory result.
Instead of querying the database via the revDataFromQuery which return a list in a variable I used revQueryDatabase then used revDatabaseColumnNames which places the contents into an array. I can now interrogate the array for the data I need to create the objects with.

The answer of course comes courtesy of RunRev lesson titled "Converting a Database To a Data Grid Array". I modified the code to suit my own purpose. I've included the code for those interested.

To those who assisted in my query I can only say "Thank You" and feel it only fair to post the end results as this may help some else with a similar problem.

Code: Select all

# -------------

function populatePropertyDetail pPropertySubType
   
   local tSqlQuery, tConnectionID, tSqlResult, tFunctionFailed 
   
   put false into tFunctionFailed
   put empty into lgPropertyDetailList
   
   if GetDataBaseID() is empty then
      openDatabase GetPlatformDesc()
   end if 
   
   put GetDataBaseID() into tConnectionID
   
   put "SELECT pd2st.propSubTypeID, pdt.datatype, pd.* " & \ 
         "FROM propertydetail pd " & \ 
         "JOIN propdetail2propsubtype pd2st ON pd.ID = pd2st.propDetailID " & \ 
         "JOIN propertydetailstype pdt ON pdt.ID = pd.propDetailsType " & \ 
         "WHERE pd2st.propSubTypeID = " & pPropertySubType & \
         " AND active = 1" into tSqlQuery
   
   put revQueryDatabase( tConnectionId, tSqlQuery) into tSqlResult
   if tSqlResult is an integer then
      put empty into lgPropertyDetailArray
      ConvertSQLResultToArray tSqlResult, lgPropertyDetailArray 
   else
      put true into tFunctionFailed
   end if

   closeDatabase
   
   return tFunctionFailed
   
end populatePropertyDetail 

# -------------

command convertSQLResultToArray pSqlResult @lgPropertyDetailArray
   
   local tColNames, tColName, tError
   local tArrayElementNo
   
   put revDatabaseColumnNames( pSqlResult ) into tColNames
   if tColNames begins with "revdberr" then
      
   else
      put 0 into tArrayElementNo
      repeat until revQueryIsAtEnd(pSqlResult)
         add 1 to tArrayElementNo
         repeat for each item tColName in tColNames
            put revDatabaseColumnNamed(pSqlResult, tColName) into \
                  lgPropertyDetailArray[tArrayElementNo][tColName]
         end repeat
         revMoveToNextRecord pSqlResult
      end repeat
   end if
   
end convertSQLResultToArray

# -------------