How do I combine two columns

[NigelS]

I have a function that does a SQL call and return a dataset into a variable.

Code: Select all

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

command 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 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
   
end populatePropertyDetail 

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

The question is, how do I convert this list into an array taken note that there is a couple of columns there that make up a description field for example "Special Features" and "Is stove in full working order"

I've tried a few things such as using "Split" and looping through the list but with not much help.
I'm aware one can use revDataFromQuery to place the result into an array but the "JOIN" is causing a mental problem and cant get the syntax correct.

Any pointers would be appreciated or where this has been addressed somewhere on this forum.

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

The result set return from the revDataFromQuerey

[dave_probertGA6e24]

Hi Nigel,

I'm not an SQL guru, but I think this might be the equivalent to what you are trying to achieve:
Just the SQL part...

Code: Select all

"SELECT pd2st.propSubTypeID, pdt.datatype, pd.* 
FROM propertydetail AS pd, propdetail2propsubtype AS pd2st, propertydetailstype AS pdt 
WHERE pd.propSubTypeID = " & pPropertySubType & " \
 AND pd.ID = pd2st.propDetailID  
 AND pdt.ID = pd.propDetailsType  
 AND pd.active = 1"

I don't really use JOIN as I don't fully understand the reasons for it!

Not sure if the 'pd.' bits in the WHERE are the correct ones - you should probably check if this gives the results you are expecting first.

The results from the database call via revDataFromQuery should be TAB separated lines, so you can probably use:

Code: Select all

 split lgPropertyDetailList by cr and tab

which should convert the variable 'lgPropertyDetailList' into a 2 dimensional array, though you might have more control by looping through the row data and grabbing the data individually - which will allow you to better index the resultant array data (in my opinion).

In all cases you will need to know which index of the columns of data returned go with which db table field. If you split each row manually then you can choose to name the data in the array:

Code: Select all

put 1 into crow
repeat for each line lrow in lgPropertyDetailList
  set the itemdelimiter to tab
  put empty into aa  // important - clears the array ready for the next row  
  put item 1 of lrow into aa["propSubTypeID"]
  put item 2 of lrow into aa["datatype"]
  put item 3 of lrow into aa["xx"]
  put item 4 of lrow into aa["yy"]
  // joining fields from the data
  put (item 6 of lrow) && (item 9 of lrow) into aa["desc"]
  // etc
  put aa into fulldata[crow]
  add 1 to crow
end repeat

This looks long-winded, but it does give a lot of control over the final data and will allow a little easier manipulation after the database call.

Cheers,
Dave

[NigelS]

Dave - Thank you for a possible solution. I will attempt your suggestion.