Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010

    Unanswered: SQL statement works only every other time


    I'm using a Progress database and an ASP.NET page that returns an error exactly every other time it is requested.

    SQL statement:
    SELECT SlsLine.LineNum, CASE WHEN SlsLine.AllDelivered = 1 THEN 'X' ELSE '0' END AS 'AllDelivered', Item.ItemName, Item.TechName, SlsLine.IntDlvDate, SlsLine.OrderNum, SlsLine.ItemCode, SlsLine.Version, CEILING(SlsLine.Qty) AS 'Qty', Item.QtyUnit, ROUND((SlsLine.Price*SlsLine.Qty),2) AS 'rowSum', ROUND(SlsLine.Price,2) AS 'Hinta', IFNULL(SysText.InfoText, '-') AS 'InfoText' FROM PUB.SlsLine INNER JOIN PUB.Item ON (SlsLine.ItemCode=Item.ItemCode) LEFT OUTER JOIN PUB.SysText ON (SlsLine.SlsLineKey=SysText.SysTextKey) AND SysText.NoteType = 'OrdConfRowE' AND SysText.ConsTxt = '' WHERE (SlsLine.OrderNum = '" & o & "') ORDER BY SlsLine.LineNum

    The error returned with .NET page:
    ERROR [23000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Integrity constraint violation:uniqueness constraint violation (7518)

    I tried the SQL in ODBCTest and it returned this error (and eventually crashed):
    [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Associated statement is not prepared. I once got it to return the rows.

    What could be wrong with my SQL? And how can it work half the time?

    I've tried removing bits of the SQL for testing, like the CASE function and some JOINs but to no avail.

    Thanks for any help!

  2. #2
    Join Date
    Feb 2010


    Ok, so I've isolated the problem to the right tables and columns. This SQL behaves in the same way:

    SELECT SlsLine.OrderNum, SysText.InfoText FROM PUB.SlsLine LEFT JOIN PUB.SysText ON SlsLine.SlsLineKey=SysText.SysTextKey AND SysText.NoteType = 'OrdConfRowE' AND SysText.ConsTxt= ' ' WHERE SlsLine.SlsLineKey = '1623405'

    But I still don't get it...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts