Michael Hartnett,

We pass the query as a string to a nvo. Here is that query.



ls_sql = " Select '' as hand, b.accountnumber as accountnumber, b.accountsubname as accountsubname , "

ls_sql += " a.billbranchname as billbranchname, a.accountnamesubbranchpk as accountnamesubbranchpk"

ls_sql += " From gl_account_name_sub_branch a "

ls_sql += " Join gl_account_name_sub b "

ls_sql += " On b.accountnamesubpk = a.accountnamesubfk "



IN the NVO

ls_present_str = "style( type=Grid &

Horizontal_spread = 25 &

Header_bottom_margin = 15 &

Header_top_margin = 15 ) &

datawindow( units=2 &

Color= 16777215) &

column( Font.Face='Palatino Linotype' &

Font.Height=-10 &

Font.Weight=400) &

text( Font.Face='Palatino Linotype' &

Font.Height=-10 &

Font.Weight=400 &

Border=8"



ls_syntax_str = SQLCA.SyntaxFromSQL(is_sql, ls_present_str, ls_msg)

If ls_syntax_str = "" Then

MessageBox('No Syntax', ls_msg)

Return

End If



dw_list.Create(ls_syntax_str, ls_msg)

dw_list.SetTransObject(Sqlca)

dw_list.Retrieve()



When the query contains only one table with no JOIN, then it works as expected.

Michael Hartnett,



How we tested the field name, might be important.

ls_object_name = dw_list.Describe("datawindow.objects")



With the Describe commands and parsing the result, what we get is the fieldname prefixed with tablename.



Happiness Always

BKR Sivaprakash

Yes using the Describe could be an issue although I cannot confirm as I do not use that way to reference the field.



Below is a sample of my SQL (with JOIN). I generate the Datastore using the SQLSyntax. Once retrieved I simply reference the fields using the GetItem function on the Datastore. There are no table prefixes. This works whether I exclusively name the columns or not.



CurrentSeasonSireName, LastServiceDate, CurrentStatusFormatted are all from the JOINed table and do not have the table prefix.



Michael



//Code Sample

Datastore ids_horse

String ls_ds_sql , ls_errors

n_horse ln_horse_current



ls_ds_sql = 'SELECT HorseID as HorseID,HorseName as HorseName,Colour as Colour,Sex as Sex,Class as Class,DateOfBirth as DateOfBirth,Age as Age'

ls_ds_sql +=' ,CurrentSeasonSireName as CurrentSeasonSireName,LastServiceDate as LastServiceDate,CurrentStatusFormatted as CurrentStatusFormatted'

ls_ds_sql +=' FROM View_DAO_Horse LEFT OUTER JOIN '

ls_ds_sql +=' View_DAO_Mare ON View_DAO_Mare.MareID = View_DAO_Horse.HorseID AND View_DAO_Mare.Season = ' + String(f_current_season())



ls_ds_sql = sqlca.SyntaxFromSQL(ls_ds_sql,'',ls_errors)

ids_horse = Create Datastore

ids_horse.Create(ls_ds_sql,ls_errors)

ids_horse.SetTransObject(sqlca)

ids_horse.Retrieve()



ln_horse_current.setid(ids_horse.GetItemString(ll_h,'HorseID'))

ln_horse_current.setname(ids_horse.GetItemString(ll_h,'HorseName'))

ln_horse_current.setcol(ids_horse.GetItemString(ll_h,'Colour'))

ln_horse_current.setsex(ids_horse.GetItemString(ll_h,'Sex'))

ln_horse_current.setdateofbirth(Date(ids_horse.GetItemDateTime(ll_h,'DateOfBirth')))

ln_horse_current.setage(ids_horse.GetItemNumber(ll_h,'Age'))



ln_horse_current.setlastservicedate(Date(ids_horse.GetItemDateTime(ll_h,'LastServiceDate')))

ln_horse_current.setCurrentSeasonSireName(ids_horse.GetItemString(ll_h,'CurrentSeasonSireName'))

ln_horse_current.setCurrentStatusFormatted(ids_horse.GetItemString(ll_h,'CurrentStatusFormatted'))

Chris,

GetSQLSelect doesn't return with table name prefixed. It returns the same sql which we used to create the dw. Sample



s_sql = " Select '' as hand, b.accountnumber as accountnumber, b.accountsubname as accountsubname , "

ls_sql += " a.billbranchname as billbranchname, a.accountnamesubbranchpk as accountnamesubbranchpk"

ls_sql += " From gl_account_name_sub_branch a "

ls_sql += " Join gl_account_name_sub b "

ls_sql += " On b.accountnamesubpk = a.accountnamesubfk "



Issue is when I retrieve the field list with

ls_object_name = dw_list.Describe("datawindow.objects")

With the Describe commands and parsing the result, what we get is the fieldname prefixed with tablename.

And after creating dw, Modify command doesn't allow to change the name of the dwo ! It's a read only property.

I see your point as the SQL could also "alias" the table name. :-(