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.

[t1950]

depending on your version of pb, this should be available.
while in the dw painter, click the data source option.
one of the tabs should be Syntax.
copy the syntax.
convert to syntax as shown above.
paste what you copied from the syntax tab.
you should be good to go.