DataWindow SQL Painter Syntax

Does anyone know what the proper syntax is for the DataWindow Painter SQL syntax
to accept SQL 6.5 style JOIN statements.
For example I was trying to get the SQL style statement such as this to create
my datawindow :
SELECT distinct CoABC_order.order_id,
    CoABC_order_ids.cust_order_id,
    CoABC_order.order_date,
    CoABC_order_item.prod_id,
    CoABC_member_a.sip_tag,
    CoABC_member_a.CoABC_mem_alias,
    CoABC_member_a.CoABC_mem_alias,
    CoABC_member_b.CoABC_mem_alias,
    CoABC_billing.last_name,
    CoABC_billing.first_name,
    CoABC_shipping.last_name,
    CoABC_shipping.first_name,
    CoABC_billing.e_mail,
    CoABC_shipping.e_mail,
    CoABC_order_item.store_code,
    CoABC_order_item.item_qty,
    CoABC_order_item.list_price,
    CoABC_order_item.title,
    CoABC_order.final_price
 FROM  CoABC_order
 JOIN  CoABC_order_item
 ON    CoABC_order.order_id = CoABC_order_item.order_id
 LEFT OUTER JOIN CoABC_order_ids
 ON   CoABC_order_ids.db_order_id = CoABC_order_item.order_id
 LEFT OUTER JOIN  CoABC_member CoABC_member_a
 ON   CoABC_member_a.CoABC_id = CoABC_order.CoABC_id
 LEFT OUTER JOIN CoABC_member CoABC_member_b
 ON   CoABC_member_a.sip_ref_id = CoABC_member_b.CoABC_id
 LEFT OUTER JOIN CoABC_billing
 ON   CoABC_billing.order_id = CoABC_order.order_id
 LEFT OUTER JOIN CoABC_shipping
 ON   CoABC_shipping.order_id = CoABC_order.order_id
 WHERE Convert(char(8),CoABC_order.order_date,1) = '06/09/98'
 ORDER BY CoABC_order.order_date



0
Joe
6/18/1998 3:35:21 AM
sybase.powerbuilder.datawindow 28057 articles. 4 followers. Follow

2 Replies
993 Views

Similar Articles

[PageSpeed] 27

Joe,

The MS SQL 6.5 outer join operator is "*=".  I may have the columns on
the wrong sides of the "*=", but try this:

SELECT distinct CoABC_order.order_id,
    CoABC_order_ids.cust_order_id,
    CoABC_order.order_date,
    CoABC_order_item.prod_id,
    CoABC_member_a.sip_tag,
    CoABC_member_a.CoABC_mem_alias,
    CoABC_member_a.CoABC_mem_alias,
    CoABC_member_b.CoABC_mem_alias,
    CoABC_billing.last_name,
    CoABC_billing.first_name,
    CoABC_shipping.last_name,
    CoABC_shipping.first_name,
    CoABC_billing.e_mail,
    CoABC_shipping.e_mail,
    CoABC_order_item.store_code,
    CoABC_order_item.item_qty,
    CoABC_order_item.list_price,
    CoABC_order_item.title,
    CoABC_order.final_price
 FROM  CoABC_order, CoABC_order_item, CoABC_order_ids,
    CoABC_member CoABC_member_a, CoABC_member CoABC_member_b,
    CoABC_billing, CoABC_shipping
 WHERE CoABC_order.order_id = CoABC_order_item.order_id AND
     CoABC_order_ids.db_order_id *= CoABC_order_item.order_id AND
     CoABC_order.CoABC_id *= CoABC_member_a.CoABC_id AND
     CoABC_member_a.sip_ref_id *= CoABC_member_b.CoABC_id AND
     CoABC_order.order_id *= CoABC_billing.order_id AND
     CoABC_order.order_id *= CoABC_shipping.order_id AND
     Convert(char(8),CoABC_order.order_date,1) = '06/09/98'
 ORDER BY CoABC_order.order_date

Dean Drackley
Associate Consultant
Enterprise Information Solutions, Inc.

Joe Shea wrote:

> Does anyone know what the proper syntax is for the DataWindow Painter
> SQL syntax
> to accept SQL 6.5 style JOIN statements.
> For example I was trying to get the SQL style statement such as this
> to create
> my datawindow :
> SELECT distinct CoABC_order.order_id,
>     CoABC_order_ids.cust_order_id,
>     CoABC_order.order_date,
>     CoABC_order_item.prod_id,
>     CoABC_member_a.sip_tag,
>     CoABC_member_a.CoABC_mem_alias,
>     CoABC_member_a.CoABC_mem_alias,
>     CoABC_member_b.CoABC_mem_alias,
>     CoABC_billing.last_name,
>     CoABC_billing.first_name,
>     CoABC_shipping.last_name,
>     CoABC_shipping.first_name,
>     CoABC_billing.e_mail,
>     CoABC_shipping.e_mail,
>     CoABC_order_item.store_code,
>     CoABC_order_item.item_qty,
>     CoABC_order_item.list_price,
>     CoABC_order_item.title,
>     CoABC_order.final_price
>  FROM  CoABC_order
>  JOIN  CoABC_order_item
>  ON    CoABC_order.order_id = CoABC_order_item.order_id
>  LEFT OUTER JOIN CoABC_order_ids
>  ON   CoABC_order_ids.db_order_id = CoABC_order_item.order_id
>  LEFT OUTER JOIN  CoABC_member CoABC_member_a
>  ON   CoABC_member_a.CoABC_id = CoABC_order.CoABC_id
>  LEFT OUTER JOIN CoABC_member CoABC_member_b
>  ON   CoABC_member_a.sip_ref_id = CoABC_member_b.CoABC_id
>  LEFT OUTER JOIN CoABC_billing
>  ON   CoABC_billing.order_id = CoABC_order.order_id
>  LEFT OUTER JOIN CoABC_shipping
>  ON   CoABC_shipping.order_id = CoABC_order.order_id
>  WHERE Convert(char(8),CoABC_order.order_date,1) = '06/09/98'
>  ORDER BY CoABC_order.order_date



0
Dean
6/18/1998 1:56:17 PM
I'm not sure what you're trying to achieve, and I'm not a SQLServer expert,
but here's some code from our SQLServer version which works:

  SELECT a.title
<snip>
    FROM ((SQSDBA.M_NOMINAL AS N
   JOIN SQSDBA.M_ACCOUNT AS A ON (A.LEDGER = N.LEDGER)
   JOIN SQSDBA.M_LEDGER AS L ON (L.LEDGER = N.LEDGER))
   LEFT OUTER JOIN DSDBA.POSTING_NOMINALS AS PN ON (PN.LEDGER = L.LEDGER AND
                    PN.COMPANY = :ra_company AND
                    PN.MODULE = :ra_module))
   LEFT OUTER JOIN DSDBA.USERGROUP_ACCOUNTS AS UA ON (UA.LEDGER = A.LEDGER
AND
                      UA.ACCOUNT = A.ACCOUNT AND
                    UA.MODULE = :ra_module AND
                    UA.COMPANY = :ra_company)
   WHERE <snip>

If you still can't get it to work by adding appropriate brackets (and I
would have thought that if it works in ISQL/W it should work in PB), it
might be your driver?  Are you using ODBC?  I think maybe you need an ODBC
2.0 (or 3.0?) driver.

Hope this helps

Simon



0
Simon
6/22/1998 9:20:56 AM
Reply:

Similar Artilces:

DataWindow This, DataWindow That...
Blah blah blah You've heard it enough that you don't even want to comment on how many times... The DataWindow. ....but it can't be enough anymore...can it? This from one of our colleagues (and yes my apologies for taking it out of context)... "...PB covers all you need to do that in ONE Tool. This includes Windowprogramming ( meaning the interface to the user: Windows, Sheets, diallougeboxes, etc.), Database operability AND Reporting! Well, I see als well as many other PB users that some of the implementations and features PB offers are improvable. But thi...

Datawindow Syntax (Composit Datawindow)
Would you help me to create datawindow layout for the report of composite datawindow from DWsyntax at runtime? ...

PB8 Datawindow Painter error on valid SQL syntax
I have had trouble ever since upgrading to PB8 with pre-existing datawindows utilizing a SQL syntax similar to: SELECT FNAME, LNAME FROM (SELECT FNAME AS FNAME, LNAME AS LNAME FROM SH.USER) AS RECORDSET Any datawindow utilizing this subquery style syntax cannot be edited without generating a PB error and erasing the syntax changes. This same syntax works fine in prior versions of PB, works fine from database painter, and works fine when initially creating a datawindow. The error I get is: Database error: Error parsing command: Unexpected token in ...

Problems within Datawindow Painter (SQL Painter)
When in the SQL Painter I receive a Database error when exiting and any changes to the SQL are lost. I just installed the EBF Build 8046. Within the documentation for this EBF it speaks to not being able to use '*=' any longer. We do not use that syntax but rather 'LEFT OUTER JOIN'. I copied the syntax that was in the EBF notes but this also gave the same error. Looking for someone who has had this problem and has a work around. There are things within the 8046 build that we require, but if this can not be fixed I guess I will have to go back to 8011. Than...

Datawindow painter excruciatingly slow on some datawindows
PB 7.0.03 Bld 10009, Oracle 7.3 What kinds of things make the datawindow and/or SQL painters "stop and think"? The client has some datawindows that are *painfully* slow when opening the SQL painter for the datawindow. The one I'm waiting on right now is in grid style. The SQL is in non-graphic mode and has a select with 14 tables with appropriate joins and a long list in the GROUP BY section. Preview mode is off. I've been waiting for 5 minutes. I've had these take 8 - 10 minutes before. This takes a mean bite out of productivity. jshark I wo...

SQL
pb 7.0.3 I cant seem to make the following query in SQL data source painter: SELECT coa.coa_code, coa.account_group1, accountgroups.ac_group_code FROM coa INNER JOIN accountgroups ON coa.account_group1=accountgroups.ac_group_code; it appears that the SQL data source painter in dw allows INNER JOIN where names of columns are same in both the tables. The above works when i issues Raw select command in my scripts or use createfromsynatx() .. Is there a way to write manual queries that can be made as data source of a datawindow ? Thanks Nohman oh well i didnt look at the...

Powerbuilder Datawindow Painter.
We have developed an application that allows user to create custom data entry windows using datawindow painter dw60.exe (PB 6.5.1). This works very well for our users who are non IT people. The problem we have is that dw60.exe is distributed with the Powerbuilder but not sold as a separate purchasable product. After discussions with Sybase representatives we have each user purchase a copy of Infomaker to make them 'legal'. We are not totally happy with this situation as we move forward to PB 8 also we are not sure if there is a dw80.exe or whether this will be supp...

Datawindow SQL Painter sometimes crashes when opening SQL in PB9
We recently migrated from PB7 to PB9. I have a few datawindows that open fine in PB7 but when I open the SQL in PB9, PowerBuiler crashes. These datawindows work fine in the executable PB9 application, but I can't open the SQL. Termination error reads: "AppName: pb90.exe AppVer: 9.0.0.5507 ModName: pbsql90.dll ModVer: 9.0.0.6066 Offset: 0005c8f" I don't know of any common thread in these few datawindows that would make them have problems while most of the other datawindows do not. I noticed that a few other people have posted this (or a s...

How to share the datawindow to inner datawindow of nested datawindow
How to share the datawindow to inner datawindow of nested datawindow. Ex -------- dw_1 is normal datawindow dw_nes is nested datawindow, both are placed one window dw_nes contain dw_child datawindow Question ---------------- i want to share dw_1 and dw_child. I have tried the below code, getting error dw_1.sharedata( dw_nes.object.dw_child) It's the dot notation. You will need to dw_nes.GetChild("dw_child", ldwc) where ldwc is a _local_ datawindowchild. <kzganesan@gmail.com> wrote in message news:4c64c402-bac5-4ee0-83aa-4ac08ffe43e5@r66g2000hsg.go...

changing datawindow connection is breaking my datawindow SQL
Hi I have had to move a number of datawindows from Oracle 9i running on a windows 2003 to Oracle 9i on AIX 4.3.3 (which is also on a private network, and requires a VPN to connect to). In the datawindow designer, I have also had to change the database interface from O90 Oracle 9i to an ODBC datasource (because I was not able to connect). In doing so, I have had to graphically re-create all the DW SQL again because I get this error: Table "XYZ" has no columns, possible invalid table format. Do you want to continue in graphic mode? If I select "NO", the SQL is...

Datawindow SQL Painter crashes sometimes when opening the SQL in PB 9
I have datawindows that open fine in PB 8 and when I open the SQL in PB 9, Powerbuilder crashes. It has happened with several windows in an application that I migrated to Powerbuilder 9. These datawindows seem to work fine in the executable application but I can't open the SQL. I can't see any common thread in them. None of them are trying to open in graphics view. What database? On Fri, 30 May 2003 18:06:01 -0400, in powersoft.public.powerbuilder.datawindow <jmoen> wrote: >I have datawindows that open fine in PB 8 and when I open the SQL in PB 9, &...

Datawindows with child datawindows
I want to create a datawindow whose only data is three columns that will themselves be drop-down data windows. These child datawindows will be populated in PB script with dw_1.GetChild(). When I create that parent datawindow (New | Datawindow | Freeform), what do I choose in the next dialog box ("Which datasource would you like to use?") External? Thanks. That depends on how you want to populate and use the columns of the main DW. The DDDW is an edit style that assists the user in selecting a value for the column in the main DW. If you want to retrieve the values ...

datawindow
Add direct setting and getting capability for the datawindow where clause. This would have to be a string array in case of unions. Return ONLY the where clause(s) in the main select (in case of sub-selects, ignore the where clause or somehow list it as a subselect in the where or a subselect in the select). It would be great if I could just get and set this as a value and have the datawindow automatically change (or add) the where clause as specified. This is for setting the where clause dynamically at run time. Currently have to parse through the datawindow.table.select looking f...

Changed DataWindow in SQL syntax!
I have problem with data window. When I create new DW and save this, it seems OK. Then if I open it DW, and click on SQL (data source) change something in syntax and then save this DW it seems ok too. Problem: if I have to open this DW and click on SQL the system goes into a loop and I cannot get out of the field either! The only way out is to KILL the application with the Task Manager. Has anyone seen this problem? I need help!! PB 7.0 build 5031, MS NT 4.0 and ASE 11.5.1. Thanks in advance, Ljiljana Tomic! Start by upgrading to release C3 or C4. The origi...

Web resources about - DataWindow SQL Painter Syntax - sybase.powerbuilder.datawindow

PowerBuilder - Wikipedia, the free encyclopedia
PowerBuilder is an integrated development environment owned by Sybase , a division of SAP . It has been in use since 1991, peaking around 1998 ...

Intelligent Versus Surrogate Keys
Should I use business columns as primary key fields for tables in thedatabase, or generate artificial primary key values? When business-related ...

chevy volt
ProEXR File Description =Attributes= channels (chlist) compression (compression): Zip dataWindow (box2i): [0, 0, 5119, 2475] displayWindow (box2i): ...

Enable - A comprehensive solution for the localization of PowerBuilder applications Untitled Document ...
Enable is localization/ translation tool used by PowerBuilder developers to make their applications multilingual.Enable is a powerful tool that ...

comp.lang.clipper.visual-objects - Google Groups
comp.lang.clipper.visual-objects 1-20 von 33006 Themen werden angezeigt Integrity of a file marcosm...@gmail.com 09:35 Combobox value Lu 08:12 ...

Sybase PowerBuilder tool readied for Microsoft's .Net
Developers can choose Win32 or newer style .Net development

PowerToTheBuilder - PowerBuilder UI Controls
HOME - COMPONENTS - PB UI DESIGNER - DOWNLOAD\PURCHASE - SUPPORT - FAQ - ABOUT Take Control of your UI with PB Ultimate Suite PowerToTheBuilder.com ...

Beautiful iPad Mini 3 Concept Reimagines Appleā€™s Tablet With iPhone 6 Design, Gold Color [Images]
The iPhone 6 may be grabbing all of the recent headlines due to its impending announcement, but spare a thought for those who prefer to stay ...

Java Networking - Real's HowTo
Real's HowTo Custom Search Java Language String and Number Applet AWT Swing Environment IO Javascript interaction JDBC Thread Networking JSP ...

Business Intelligence Products - Database Management, Data Warehousing & Mobility Software - Sybase Inc ...
Sybase offers a variety of Business Intelligence (BI) software like products for database management, data warehousing and mining, data integration ...

Resources last updated: 11/21/2015 6:34:52 PM