0

I'm new to PowerBuilder 10.5 so I have a question... I created a graph DataWindow. It's a usual graph model, with a simple SQL code. 

SELECT data1, data2 FROM table1, table2;

Now I need to get into my "START" command button clicked event and write such a statement which would call my DataWindow under a simple condition:

WHERE key1=key2.

Any advice? I'm trying with the "SETsql" statements?

Terry
  • 6,160
  • 17
  • 16
John Blue
  • 51
  • 1
  • 2
  • 12
  • HI. It's not completly clear what do you mean. Would you like to get a record with an id, than you need a retrieval argument. Or what do you want. Could you explain it more detailed? – DARKinVADER Jun 09 '13 at 18:46
  • I need to get a simple x-y graph. Y value being number of products, x city where they origin. I call does two values in an SQL syntax of my datawindow. Now in powerbuilder on the cb_start clicked event I need something like... "dw_name.SetSqlSelect()" and "GetSqlSelect" and somewhere in those line I need to add condition for the original SQL statement. So no, I don't have any retrieval arguments. – John Blue Jun 10 '13 at 06:24
  • Hi, it is still not fully clear to me (what this means: call does two values in an SQL syntax of my datawindow), BUT if you would like to add "where" criteria to your datawindow, you can use the SetSQLSlecet, but if these arguments are mandatory, you should use Retrieval arguments because using that is much more simpler than manually modifying the sql statement. – DARKinVADER Jun 10 '13 at 06:27
  • hi, I'll show you what I'm doing at this moment: SQL code od my datawindow (that I've named dw_1) is the one written in the main question. In my cb_start I'm trying to write something like: "ls_sql= dw_1.getSqlSelect() / ls_sql="'WHERE city1=city2 AND value1=value2" / dw_1.SetSqlSelect(ls_sql) / dw_1.Retrieve()". The error that I have is ORA 00900 - ORA-00900: invalid SQL statement. – John Blue Jun 10 '13 at 06:37

2 Answers2

1

if the above code is true, the problem is that you overwrite the ls_sql variable with the where criteria. The ls_sql should contain the whole sql statement including the select * from xytable where key = 1 So you should APPEND the where criteria (but I think you should you retrieval arguments, it is easyier)

DARKinVADER
  • 640
  • 3
  • 11
0

There is a simpler way than using the SetSQL() statement, because the SetSQL must match the columns that are defined in the DW (you can create them dynamically, but as you state being new to PB I will skip this possibility):

  • If your SQL statement is always the same and does not need a key provided by code or by the user, you can simply define the SELECT directly in the DW painter (menu Design / Data source, then in your cb_start.clicked() you just call your_dw.retrieve()

  • if you need a key that may vary, define it in the Retrieval arguments of the DW when in the data source view: choose a datatype and a name (e.g. thing) and in the DW SELECT syntax you can say WHERE key1=:thing (notice the colon) and in the pbscript code in the clicked() event add the value of the argument in the retrieve arguments your_dw.retrieve("42"). Obviously you can replace the literal by a variable of the correct type that can be defined and populated formerly in the code. If you do not provide the value of retrieval arguments via the retrieve() statement, the DW will ask for it (or them) in dialog box at the beginning of the retrieval.

Seki
  • 11,135
  • 7
  • 46
  • 70
  • Thanks for the answer, unfortunately I was obligated to use SetSQL. You did help a lot for the another part of my assignment, thou :) – John Blue Jun 10 '13 at 07:22
  • Although, I have to say I can't see why one would use "SetSQL" statement when it's much simpler to just write the entire SQL code in Data Source of DataWindow and retrieve it. – John Blue Jun 10 '13 at 07:35