1. Sivaprakash BKR
  2. PowerBuilder
  3. Tuesday, 11 December 2018 10:04 AM UTC

Hello,

We create dynamic dw, passing required sql statements, using SyntaxFromSQL.   The issue is whenever there are two or more tables involved (Join), we get the table name prefixed to the columns of the field.  We don't want this prefix.  

Is there any way, not to add this prefix (table names) from the resultant dw ?

Happiness Always
BKR Sivaprakash

 

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 11 December 2018 16:31 PM UTC
  2. PowerBuilder
  3. # 1

Hi BKR;

  That is the default way that PB works when handling a DW object - whether it be in the DW Painter or via a SyntaxFromSQL command.

  My suggestion would be to let the DW run-time handle the SyntaxFromSQL as it normally would but after the Create () method, use the "GetSQLSelect" to extract the current SQL and then remove the table prefixes via a PowerScript "replace". Then use the "SetSQLSelect" command to update the SQL back into the DWO (without the prefix names).

HTH

Regards ... Chris

Comment
  1. Sivaprakash BKR
  2. Friday, 14 December 2018 10:15 AM UTC
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.



  1. Helpful
  1. Sivaprakash BKR
  2. Friday, 14 December 2018 10:49 AM UTC
And after creating dw, Modify command doesn't allow to change the name of the dwo ! It's a read only property.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 14 December 2018 17:11 PM UTC
I see your point as the SQL could also "alias" the table name. :-(
  1. Helpful
There are no comments made yet.
Michael Hartnett Accepted Answer Pending Moderation
  1. Tuesday, 11 December 2018 11:29 AM UTC
  2. PowerBuilder
  3. # 2

Hi Sivaprakash,

You could name the columns in your SQL statement.

E.G.

SELECT table1.ID AS ID, table2.name as NAME, etc...

The DW will then expose the columns using the name you specify in the SQL Statement.

HTH

Michael

Comment
  1. Sivaprakash BKR
  2. Thursday, 13 December 2018 12:55 PM UTC
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.

  1. Helpful
  1. Sivaprakash BKR
  2. Thursday, 13 December 2018 14:06 PM UTC
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

  1. Helpful
  1. Michael Hartnett
  2. Thursday, 13 December 2018 14:28 PM UTC
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'))
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.