PB 6.5 & MS SQL 6.5

This is probably an MS SQL issue but I figured it was worth presenting here
for some possible insight. Any help is greatly appreciated.

- I have two tables: BATCH & EMPLOYER_GROUP

- BATCH is a child of EMPLOYER_GROUP

- EMPLOYER_GROUP_ID is the primary key column in EMPLOYER_GROUP and a
foreign key column in BATCH

- The EMPLOYER_GROUP_ID column in BATCH may be null

- There is current only one record in BATCH. The EMPLOYER_GROUP_ID column
is null in that record.

The following SELECT does not (in my opinion) work correctly:

SELECT *  
  FROM BATCH, EMPLOYER_GROUP  
 WHERE ( BATCH.EMPLOYER_GROUP_ID *= EMPLOYER_GROUP.EMPLOYER_GROUP_ID) and  
       (	
         (EMPLOYER_GROUP.EMPLOYER_GROUP_ID = 0) OR  
         (BATCH.BATCH_NUMBER = '00081738' )
       )    ;

This is returning the one row in BATCH, however the BATCH_NUMBER in the
record is 00081739. I do not believe any rows should have been returned. I
believe the problem is related to the use of an OUTER JOIN and an OR in the
WHERE clause based on the following test results:

Test 1: 1st part of WHERE clause only.

SELECT *  
  FROM BATCH, EMPLOYER_GROUP  
 WHERE ( BATCH.EMPLOYER_GROUP_ID *= EMPLOYER_GROUP.EMPLOYER_GROUP_ID) and  
       (	
         (EMPLOYER_GROUP.EMPLOYER_GROUP_ID = 0)
       )    ;

Returns 0 rows. Correct.

Test 2: 2nd part of WHERE clause only with incorrect BATCH_NUMBER

SELECT *  
  FROM BATCH, EMPLOYER_GROUP  
 WHERE ( BATCH.EMPLOYER_GROUP_ID *= EMPLOYER_GROUP.EMPLOYER_GROUP_ID) and  
       (	
         (BATCH.BATCH_NUMBER = '00081738' )
       )    ;

Returns 0 rows. Correct.

Test 3: 2nd part of WHERE clause only with correct BATCH_NUMBER

SELECT *  
  FROM BATCH, EMPLOYER_GROUP  
 WHERE ( BATCH.EMPLOYER_GROUP_ID *= EMPLOYER_GROUP.EMPLOYER_GROUP_ID) and  
       (	
         (BATCH.BATCH_NUMBER = '00081739' )
       )    ;

Returns 1 row. Correct.


Bob Bowles
Medco Review
bob@medconet.com
0
Bob
1/5/1999 12:05:07 AM
sybase.powerbuilder.database 9855 articles. 2 followers. Follow

1 Replies
688 Views

Similar Articles

[PageSpeed] 39
Get it on Google Play
Get it on Apple App Store

I found the answer. ANSI SQL & Transact SQL behave differently. Refer to
http://support.microsoft.com/support/kb/articles/q176/4/80.asp?FR=0

0
Bob
1/5/1999 12:49:39 AM
Reply:

Similar Artilces:

MS SQL 6.5 and PowerBuilder 6.5
I have an application in PB6.5 that interfaces with MSSQL 6.5 database. I can run this app WITHIN PB to make a connection the database and retrieve data without a problem, but after I built the EXE file and trying to run from that EXE file, I get a "-1" as the return code from the SetTransObject statement. Sample code is like : datastore lds_datastore lds_datastore = CREATE datastore lds_datastore.DataObject = "d_test" li_return = lds_datastore.SetTransObject(SQLCA) The d_test datawindow object consists of a simple SQL SELECT statement. Has anyone seen this ...

PB 6.5 AND MS SQL SERVER 6.5
Hi, I have recently converted an 16-bit PB5.0 application to PB6.5. This application goes against a MS SQL SERVER 6.5 database. I have noticed now that the new application won't release locks on tables when I do an insert or update. This application has never had explicit commits coded and I haven't seen soo many locks on tables before until the recent powerbuilder upgrade. Does anyone have some insight on this situation? Chris Edwards Saks Incorporated 331 W. Wisconsin Ave Milwaukee, WI 53203 chris_edwards@saksinc.com ...

PB 6.5 Connection To MS SQL Server 6.5
We have three servers running MS SQL Server 6.5. PB 5.0 applications (both PB client apps and PB apps running on the servers at night as part of batch jobs) have been running on them for a long time. Native drivers are used. I'm starting to replace these apps with PB 6.5 versions of them. With respect to two of the servers, I can connect to the databases from all the new apps - both client PC apps and batch apps. On the third server, I can connect to the databases using the client PC apps only. When the apps in the night job run, the CONNECT statement gets the following error ...

PB 6.5 and MS SQL Server 6.5 SP5a
We are currently running apps in PB 5.0.04 and PB 6.5 with a MS SQL Server database, ver 6.5 SP4. Our DBA wants to apply SP5a to the database. I see many issues regarding a required update for PB 5.0.04. Fine, we'll deal with that. Is there any documentation in existence that verifies that PB 6.5 is compliant with MS SQL Server 6.5 SP5a? Also, does anyone have test results using the new MS SQL Server ODBC driver ver 3.7? Thanks! If its of any help, I couldn't find any hits in the infobase on service pack 5 or ODBC driver 3.7. Either nobody is having problems wit...

PB 6.5 Apps Connection to MS SQL Server 6.5
We have three servers running MS SQL Server 6.5. PB 5.0 applications (both PB client apps and PB apps running on the servers at night as part of batch jobs) have been running on them for a long time. Native drivers are used. I'm starting to replace these apps with PB 6.5 versions of them. With respect to two of the servers, I can connect to the databases from all the new apps - both client PC apps and batch apps. On the third server, I can connect to the databases using the client PC apps only. When the apps in the night job run, the CONNECT statement gets the following error ...

BUG!!!! ?? Using Stored Procedure with PB 6.5 and MS SQL Server 6.5.
I have some stored procedures on my DBMS, MS SQL Server 6.5. After upgrading to PB 6.5 I cannot assign any of my stored procedures to a datawindow. What the .... is wrong. Is it my installation? Is it PB 6.5? In fact it is serious as my reports are not running. Any comments are appreciated. Thomas Door Maersk Data AS What is the error you are getting, maybe we could help you then. Randy Thomas D��r <thd@maerskdata.dk> wrote in article <CoL4foOz9GA.276@forums.powersoft.com>... > I have some stored procedures on my DBMS, MS SQL Server 6.5. After upgrading ...

Powerbuilder 6.5 & SQL-server 6.5 <> 7.0
Hi everyone, I'm using PB 6.5 with SQS 6.5 Everything is normal until I port the program build in this environment to a similar environment with the only difference that the database is SQS 7.0 This database is slightly different - in some tables, the fields are larger than in the SQS 6.5 database (but the same type - char). When the program retrieves rows in that environment I get the error message 'Data conversion resulted in overflow ...' I have several other environments with sqs 6.5 with the same difference (larger fields) where the program doesn't rep...

Executing a MS SQL server 6.5 procedure with no result sets from PB 6.5...
Hi friends, I have a stored procedure created in MS SQL Server 6.5. The procedure has 4 arguments. The procedure will do some calculations & then finally will insert some data into a table. That's it. It won't return any resultset (no select statement). Now I need to execute the procedure from within a PB application(verion 6.5). Which is te best method for this. Dynamic SQL format 1/2/.... Thanks in advance. Thanks. Bye. With Regards, Jeinutheen Mohamed. In your PB script, declare the procedure : DECLARE sp_procedure PROCEDURE F...

PB 6.5 / MS SQL 6.5 / Stored Procedue dw creation problem
We are beginng to have trouble creating stored procedure datawindows that us an argument in an if statement. Powerbuilder is returning an error message: "Requested result set number 1 not found". The whole message from the trace file is: "PROCEDURE PREPARE: execute dbo.trf_sm_ltl_orders_to_route;1 @as_all = '0' (30 MilliSeconds) Error 999 (rc -1) : Requested result set number 1 not found" If you comment out the IF @as_all = "N" then it will create the dw fine. We can't figure out why this just started because we have been doing this...

MS SQL Server 6.5 & PB 5.0.x
Is it necessary to install the pb stored procedures in the master db table ? The help says that it is not necessary, but PB gives me an error msg ("Catalog tables could not be created and are not available for use.") when trying to connect to a db. Scott...... The first person to connect to the database through PB must have dbo or a subset of dbo privileges or PB won't be able to create the catalog tables. Mickey pottsgf@swbell.net >Is it necessary to install the pb stored procedures in the master db table >? The help says that it is not necessary, but PB...

PB 5 & 6.5 w/ MS SQL 7.0
Can anyone speak to any compatibility problems w/ using PB 5 & 6.5 w/ Microsoft SQL Server 7.0? We are planning on upgrading to SQL 7.0, but have a few apps in both PB6.5 & PB5. Unfortunately, one of these is vendor supplied so we can't upgrade to PB7. I was specifically wondering if there were any problems using native SQL 7.0 drivers w/ the PB6.5 & 5 dlls (that were made before SQL 7.0 was a reality.) Or are there updated drivers for this? any help/experiences offered would be appreciated. thanks!! -- Travis Hettenbach hettenb@kochind.com Me too have th...

Identity Column
Using PB 5.0 and MS SQL 6.5 After an embeded Insert Statement, I use the following to get the value assigned by the Identity column : INT emp_id DECLARE get_value DYNAMIC CURSOR FOR SQLSA ; PREPARE SQLSA FROM "SELECT @@identity"; OPEN DYNAMIC get_value ; FETCH get_value INTO :emp_id ; CLOSE get_value; The emp_id returned seems to always be NULL. It is supposed to be the last (autoincrement) number assigned to the record inserted into the Employees table. Does anyone know why I am getting a NULL instead of a real value? The column is defined as an Identity in ...

Client 6.5.5 and 6.5.6
I am testing this before rolling it out to users--in the item lists, is there a reason that the year no longer displays in the date column (We are upgrading from SP2 and it is fine there)? It now reads mm/dd and the time instead of mm/dd/yy and the time. This is also true of the header when you open an item. It is particulalry a problem when looking at document references. This is not unique to me, is it? Found it--in the Options for date and Time--now, is it possible that the SP5 update changed the settings? "PatM" <pmuellerNOSPAM@cmt-law.com> wrote in ...

upgrade from 6.5.5 to 6.5.6
Hi, We are going to upgrade from 6.5.5 to 6.5.6 to hopefully solve the problem with sending mail to yahoo. My question is do I have to upgrade everything? On one server we have the MTA, GWIA, POA for our main postoffice and webaccess. On the second server we have 4 other postoffice's for our student email. Can I just upgrade the main server with the MTA, GWIA, POA and webaccess and leave the other server alone at 6.5.5? If so will any reconfiguration be neccessary on the old post offices? > Hi, > We are going to upgrade from 6.5.5 to 6.5.6 to hopefully solve th...

Web resources about - PB 6.5 & MS SQL 6.5 - sybase.powerbuilder.database

Resources last updated: 1/23/2016 11:49:44 PM