Problem with Union Syntax in DataWindow

[Former Member]

I am converting the old PowerBuilder code to the latest version and having some problem with a datawindow code which uses Union function.

The old code makes union of 4 tables with 3 tables. The old code works fine but there is syntax error with the new code. The error is caused by the 0 and 'X' in the following example. Can someone tell me what should be the right syntax. Thanks.

retrieve="
    PBSELECT( VERSION(400)

    TABLE(NAME=~"table1~" )  
    TABLE(NAME=~"table2~" )  
    TABLE(NAME=~"table3~" )  
    TABLE(NAME=~"table4~" )

    COLUMN(NAME=~"table1.a~") 
    COLUMN(NAME=~"table2.b~") 
    COLUMN(NAME=~"table2.c~") 
    COLUMN(NAME=~"table3.d~") 
    COLUMN(NAME=~"table4.e~")

    UNION(
    PBSELECT( VERSION(400)

    TABLE(NAME=~"table1~" )  
    TABLE(NAME=~"table3~" )  
    TABLE(NAME=~"table4~" )

    COLUMN(NAME=~"table1.a~") 
    0
    'X'
    COLUMN(NAME=~"table3.d~") 
    COLUMN(NAME=~"table4.e~")

    )

    )

[Breck_Carter]

Try asking this question on the sybase.public.powerbuilder.datawindow newsgroup on the forums.sybase.com NNTP server.

Suggestion: Switch to using SQL syntax instead of the PBSELECT syntax, then you can code an actual UNION.

[Former Member]

@Breck Carter thanks for the suggestion. Could you please tell how to switch to using SQL Syntax instead of PBSELECT? Can I simply write SQL syntax in the datawindow?

[reimer_pods]

Open the DataWindow painter, switch to SQL mode and select Design->Convert to Syntax from the menu bar.

[chris_keating · Accepted solution]

The syntax from 12.5 Build 2511 for a union of the PB Demo DB department table looks like:

retrieve=" PBSELECT( VERSION(400) TABLE(NAME=~"department~" ) COLUMN(NAME=~"department.dept_id~") COLUMN(NAME=~"department.dept_name~") COLUMN(NAME=~"department.dept_head_id~"))
UNION PBSELECT( VERSION(400) TABLE(NAME=~"department~" ) COLUMN(NAME=~"department.dept_id~") COMPUTE(NAME=~"'X'~") COMPUTE(NAME=~"1~"))) " update="department" updatewhere=1 updatekeyinplace=no )

Notice that the 'X' and 1 column values are tagged with COMPUTE.

[Former Member · Accepted solution]

@Chris Keating Thanks, if it was an answer, I'd accept it. The problem was because of missing COMPUTE tag.

[Former Member · Accepted solution]

@Chris Keating Just a small question, what would be syntax if i want to write NULL. It won't accept COMPUTE(NAME=~"NULL~"). Would the NULL be exactly same as COMPUTE(NAME=~"0~")?