• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How to create a new column dynamically in a datawindow (does not exist in the select)

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved How to create a new column dynamically in a datawindow (does not exist in the select)

  1. Issue
0
Votes
Undo
  1. David Vasconcelos
    David Vasconcelos
  2. PowerBuilder
  3. Monday, 18 December 2017 16:46 PM UTC

How to dynamically add a column which is not part of the select statement during runtime.  I don't see an option for dw-modify for it, all I see is the table.select which then brings up more issues of if I have to add a column in the select then how to find the last column in the select in order to add/change the sql.  Especially if there could be sub-selects, unions or selects within the where clause.

Thanks

Dave V.

  1. TIME
dll assembly works with Runtime 22.1.0.2819 but fa...
PB2022 R3 Runtime Breaking PB2021 Application
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 18 December 2017 17:54 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi David;

   To add an extra column to the DW's SQL result set, add code to the RetrieveStart event or SQLPreview event to change the SQL statement and amend the extra column(s) that you need. That will extend the DW's Primary Buffer at run-time to include the extra column(s) that you need.

   The second step is to add the column(s) to the DW's appropriate band layer if you need the App user to see it visually. To do this, you would use the Create command within a DW Modify() method, as follows (for example):

.Modify( &
"create  column( id= tabsequence= accelerator='' moveable=<0 - False, 1 - True> resizeable=<0 - False, 1 - True> pointer='' band=, Summary, Trailer., Background, Foreground>"  +  & .

.......

No> edit.autoselect= edit.vscrollbar= edit.name='') ")

HTH

Regards ... Chris

TIP: Use the DW Syntax utility to help you code this.

Comment
Mike S
  1. Mike S
  2. Monday, 18 December 2017 22:58 PM UTC
"add code to the RetrieveStart event or SQLPreview event to change the SQL statement and amend the extra column(s) that you need. That will extend the DW's Primary Buffer at run-time to include the extra column(s) that you need."



wow, really? that is LOT easier than what i had thought had to be done.  changing the buffer via syntax is such a huge pain .

  1. Helpful
David Vasconcelos
  1. David Vasconcelos
  2. Tuesday, 19 December 2017 12:06 PM UTC
Chris just to make sure I am doing this appropriately.  When you mean "add code:" to RETRIEVESTART or SQLPREVIEW are we talking about a dw modify using TABLE.SELECT (i.e Modify("DataWindow.Table.Select=)  to add the column?  Or is there a better/different way?



Dave V.

  1. Helpful
There are no comments made yet.
Responses (4)
Mike S
Mike S Accepted Answer Pending Moderation
  1. Monday, 18 December 2017 17:48 PM UTC
  2. PowerBuilder
  3. # 1

there is no standard feature for this.  however, it can be done.

You have to modify the datawindow syntax extensively during runtime.  You need to be able to change the sql dynamically to add the column to the select, then add the column definition to the datawindow correctly.  then you add the column to the display portion of the datawindow. 

when this is all done you create the datawindow from the syntax.

 

 

 

Comment
There are no comments made yet.
Nik Seger
Nik Seger Accepted Answer Pending Moderation
  1. Monday, 15 April 2024 14:21 PM UTC
  2. PowerBuilder
  3. # 2

Hi Chris

I don't quite understand how this is supposed to work.

I am trying to add a number of columns to my datawindow, but adding the columns to my sql is not enough.

My modify fails, because the table columns for the ids don't exist.

in the ls_test string is no 'col_' column. 

Do you have a solution for this?

ls_select = this.getsqlselect()
ls_from = mid(ls_select, pos(ls_select, 'from'))
ls_select = left(ls_select, pos(ls_select, 'from') -2)
ll_field_id = long(this.describe('last_col.id'))

for ll_i = 1 to il_col_count
	ls_col = 'col_'+ string(ll_i)
	
	ls_select += ',cast (0 as decimal(5,2)) ' + ls_col
	ll_col_id ++
	ls_modify += 'create column(band=detail id='+string(ll_col_id)+' ...... ) '
next

this.setsqlselect(ls_select + ' ' + ls_from)

ls_test = this.describe('datawindow.table.columns')

modify(ls_modify)
Comment
Load more comments
mike S
  1. mike S
  2. Monday, 15 April 2024 15:51 PM UTC
you need 3 things to occur:

1. change the sql for the new columns

2. add the new columns to the buffer

3. create the column for display (assuming you want it to display)



using setsqlselect will not add the columns to the datawindow buffer. it just does #1.



However, you might get #1 & 2 to work using setsqlpreview (according to what chris said in a prior post - i have no idea if that works) in the SQLPreview event:

"Changing the SQL to be executed (you can get the value of sqlsyntax, modify it, and call SetSQLPreview)"

let us know if it works either way.

Also, does it keep the datawindow sql and buffer changed at that point? I assume so.



The other way to do this is to manually change the dw syntax buffer.





#3 is sort of easy - just create the display column.

  1. Helpful
mike S
  1. mike S
  2. Monday, 15 April 2024 15:58 PM UTC
https://www.youtube.com/watch?v=-5ZbrAJnmW0&list=PL2Eo5uXB4T3lvcLV_G9UMTxa1va0c33bk&index=5
  1. Helpful 1
Nik Seger
  1. Nik Seger
  2. Tuesday, 16 April 2024 14:35 PM UTC
SetSQLPreview doesn't help, but I could create a solution with manually changing the dw syntax buffer
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 15 April 2024 15:59 PM UTC
  2. PowerBuilder
  3. # 3

RunTime FunTime - Adding Column(s) to Your DataWindow SQL and Display at Runtime (youtube.com)

Comment
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.