Problem with Union Syntax in DataWindow

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~")

    )

    )

union powerbuilder datawindow

asked 14 Dec '11, 03:08

sam
315●14●16●24
accept rate: 0%

edited 14 Dec '11, 08:24

Volker Barth
40.2k●362●550●822

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.

(14 Dec '11, 07:25) Breck Carter

Replies hidden

@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?

(14 Dec '11, 08:14) sam

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

alt text

(14 Dec '11, 08:51) Reimer Pods

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 14 Dec '11, 15:13

Chris Keating
7.8k●49●128
accept rate: 32%

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

(15 Dec '11, 03:39) sam

@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~")?

(15 Dec '11, 07:36) sam

flat view

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.

permanent link

answered 14 Dec '11, 10:02

Tom Mangano
672●24●27●38
accept rate: 8%

edited 14 Dec '11, 10:04

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

powerbuilder ×39
union ×12
datawindow ×4

question asked: 14 Dec '11, 03:08

question was seen: 4,330 times

last updated: 15 Dec '11, 12:37

SAP SQL Anywhere

Problem with Union Syntax in DataWindow

Follow this question

Related questions