I have spent a bit of time fighting with a SQL statement to get it to work.

I am not sure what is going on & wanted some clarification. What I am showing is only a small part of the whole statement, which contains multiple SELECTs, but this segment demonstrates the error.

The following SQL works:

SELECT
 '1. Late Sales' as "Heading",
 NiceDate(SI.DateWanted) +
 '<a HREF = ' + COALESCE(CAST(SI.ID AS VARCHAR), '') + '>'
 + ' FMID:' + COALESCE(CAST(SI.FMID as VARCHAR), '') + ' ' +  COALESCE(O.FullName, '-no customer-') + '</a> '
 + COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')
as "Content",
DateWanted
FROM
 SalesInvoices SI
 LEFT JOIN Organisations O ON O.ID = SI.CustomerPayingID
 LEFT JOIN SalesInvoiceItems SII ON SII.SalesInvoicesID = SI.ID
 LEFT JOIN Products P ON P.ID = SII.ProductsID
WHERE StatusID IN (43, 44, 45)
AND CAST(DateWanted as Date) BETWEEN Current_Date - INTERVAL '14' DAY AND Current_Date - INTERVAL '1' DAY
GROUP BY SI.ID
ORDER BY Heading, Content, DateWanted

---------------------

But the following one gives an error:

SELECT
 '1. Late Sales' as "Heading",
 NiceDate(SI.DateWanted) +
 '<a HREF = ' + COALESCE(CAST(SI.ID AS VARCHAR), '') + '>'
 + ' FMID:' + COALESCE(CAST(SI.FMID as VARCHAR), '') + ' ' +  COALESCE(O.FullName, '-no customer-') + '</a> '
 + COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')
as "Content"

FROM
 SalesInvoices SI
 LEFT JOIN Organisations O ON O.ID = SI.CustomerPayingID
 LEFT JOIN SalesInvoiceItems SII ON SII.SalesInvoicesID = SI.ID
 LEFT JOIN Products P ON P.ID = SII.ProductsID
WHERE StatusID IN (43, 44, 45)
AND CAST(DateWanted as Date) BETWEEN Current_Date - INTERVAL '14' DAY AND Current_Date - INTERVAL '1' DAY
GROUP BY SI.ID
ORDER BY Heading, Content

The only difference is the ommission of the "DateWanted" field.

----------------------------------

To confuse matters further the following SQL does NOT give an error:

SELECT
 '1. Late Sales' as "Heading",
 NiceDate(SI.DateWanted) +
 '<a HREF = ' + COALESCE(CAST(SI.ID AS VARCHAR), '') + '>'
 + ' FMID:' + COALESCE(CAST(SI.FMID as VARCHAR), '') + ' ' +  COALESCE(O.FullName, '-no customer-') + '</a> '
--  + COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')
as "Content"

FROM
 SalesInvoices SI
 LEFT JOIN Organisations O ON O.ID = SI.CustomerPayingID
 LEFT JOIN SalesInvoiceItems SII ON SII.SalesInvoicesID = SI.ID
 LEFT JOIN Products P ON P.ID = SII.ProductsID
WHERE StatusID IN (43, 44, 45)
AND CAST(DateWanted as Date) BETWEEN Current_Date - INTERVAL '14' DAY AND Current_Date - INTERVAL '1' DAY
GROUP BY SI.ID
ORDER BY Heading, Content

i.e., the same statement but with the short clause that includes a "LIST" command removed.

I am confused that a longer statement works, but a shorter statement (which is fully contained in the longer version) gives an error.

Have I found a bug or a feature?

Adam


Whilst I have no real idea I do have a guess as to what's causing it:

The phrase

 '<a HREF = ' + COALESCE(CAST(SI.ID AS VARCHAR), '') + '>'
 + ' FMID:' + COALESCE(CAST(SI.FMID as VARCHAR), '') + ' ' +  COALESCE(O.FullName, '-no customer-') + '</a> '
 + COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')
as "Content",

cannot be fully determined by ElevateDB - ie it can't work out the length and will default to (I think) 60 - and that may be causing the problem. Try explicitly CASTing the who;e phrase to a VARCHAR of the length you want.

Roy Lambert

Adam,

What is the error that you are receiving?

Have you tried replacing the line below

+ COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')

with something really basic such as

 + ' Fred' AS VARCHAR(500)

Richard

Richard & Roy

The issue seems to be the "LIST( ..." function / statement, which seems to confuse EDB enough to make it unhappy to ORDER BY. Removing this portion of the statement makes it work.

The truly bizarre thing was the fact that adding the "DateSent" field (without changing the SQL) suddenly allowed EDB to work!

As I have a working version I haven't really dug into this any deeper ... but I do wonder what might be going on under the hood.

Adam


I'm interested as well. If you can send me (or post in the binaries) a sample database I'd be willing to do some digging.

Roy Lambert

Adam,

<< I have spent a bit of time fighting with a SQL statement to get it to work.

I am not sure what is going on & wanted some clarification. What I am showing is only a small part of the whole statement, which contains multiple SELECTs, but this segment demonstrates the error. >>

Please email the database that you're using, and I'll take a look.  I suspect that it may be something internal, but I'd have to trace it to be sure.

Tim Young
Elevate Software
www.elevatesoft.com

Tim & Roy,

Thanks for the interest. I will need a couple of days to find time to pull out a subset of the database which still demonstrates the problem. The whole DB is over 1gig & not really practical to post.

I am curious about what is going on!

Adam,

<< Thanks for the interest. I will need a couple of days to find time to pull out a subset of the database which still demonstrates the problem. The whole DB is over 1gig & not really practical to post. >>

Just to clarify - I only need the database catalog and any customizations, I don't need the actual data.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Here is code to produce the error in EDB 2.24b3 using EDB Manager:

--------------------------------------------------------------------

1. At level of EDB Manager "Session" run this:

CREATE DATABASE "Test" PATH 'Test'!
                             

----------------------------------------------------------------------

2. Now refresh the EDB manager database view, click on the "Test" database and run this:

CREATE FUNCTION "UID" ()
RETURNS INTEGER
BEGIN
 DECLARE Result INTEGER DEFAULT 1;
 SET Result = 1;
 RETURN Result;
END!

CREATE FUNCTION "FMID" (IN aTableName VARCHAR)
RETURNS INTEGER
BEGIN
 DECLARE Result INTEGER DEFAULT 1;
 SET Result = 1;
 RETURN Result;
END!

CREATE TABLE "Products"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
"Image" BLOB,
"ProdCode" VARCHAR(20) COLLATE "ANSI" NOT NULL,
"Name" VARCHAR(100) COLLATE "ANSI" NOT NULL,
"PublicDescription" CLOB COLLATE "ANSI",
"PrivateDescription" CLOB COLLATE "ANSI",
"QualityConcerns" CLOB COLLATE "ANSI",
"StatusID" INTEGER DEFAULT 3 DESCRIPTION 'ReadOnly, 50',
"ProductsTypeID" INTEGER,
"ForecastsCode" VARCHAR(30) COLLATE "ANSI" DESCRIPTION 'Distinct',
"UnitWeight" FLOAT DEFAULT 1 NOT NULL,
"CountUnits" FLOAT DEFAULT 1 NOT NULL,
"BarCode" VARCHAR(13) COLLATE "ANSI",
"Weight" FLOAT COMPUTED ALWAYS AS CountUnits * UnitWeight,
"TareWeight" FLOAT DEFAULT 0 NOT NULL,
"ShelfLife" FLOAT NOT NULL DESCRIPTION 'ReuseLast',
"BarCodeOuter" VARCHAR(15) COLLATE "ANSI",
"BarCodeCase" VARCHAR(15) COLLATE "ANSI",
"FLOCert" BOOLEAN DEFAULT False NOT NULL,
"Organic" BOOLEAN DEFAULT False NOT NULL,
"RegionsID" INTEGER,
"ScalesTypeID" INTEGER,
"StorageInfoID" INTEGER DESCRIPTION 'ReuseLast',
"DateCreated" TIMESTAMP DEFAULT Current_TimeStamp,
"Current" BOOLEAN DEFAULT true NOT NULL,
"FullName" VARCHAR(100) COLLATE "ANSI" COMPUTED ALWAYS AS IF(Current = true  OR Current is NULL then '' else 'zzz/')
  + IF(ProdCode IS NULL THEN '' ELSE "ProdCode")
  + IF("Name" is NULL THEN '' ELSE ' ('+"Name" + ')')
  + IF(Weight = 0 THEN '' ELSE  ' '
       + IF(UnitWeight = 1 AND CountUnits = 1 THEN '' ELSE
            CAST(CountUnits AS VARCHAR) + 'x' +
            IF(UnitWeight<1 THEN CAST(UnitWeight *1000  AS VARCHAR) +'g' ELSE
               CAST(UnitWeight AS VARCHAR)+'kg') +', ')
       + CAST(Weight AS VARCHAR) +'kg'),
"MinOrderSize" FLOAT DEFAULT 0 NOT NULL,
"WarehouseConcerns" CLOB COLLATE "ANSI",
CONSTRAINT "PrimaryKey" PRIMARY KEY ("ID")
)!

CREATE TABLE "Organisations"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
"FMCode" VARCHAR(20) COLLATE "ANSI",
"Name" VARCHAR(45) COLLATE "ANSI" NOT NULL,
"WebsiteAddress" VARCHAR(250) COLLATE "ANSI",
"Memo" CLOB COLLATE "ANSI",
"ColourID" INTEGER DESCRIPTION 'Color',
"FLOID" INTEGER,
"DateCreated" TIMESTAMP DEFAULT CURRENT_Timestamp,
"Current" BOOLEAN DEFAULT true NOT NULL,
"FullName" VARCHAR(80) COLLATE "ANSI" COMPUTED ALWAYS AS IF(Current=true  OR Current is NULL then '' else 'zzz/')
+ IF(Name IS NULL THEN '' ELSE "Name")
+ IF("FMCode" is NULL THEN '' ELSE ' ('+"FMCode" + ')'),
CONSTRAINT "PrimaryKey" PRIMARY KEY ("ID")
)!

CREATE TABLE "SalesInvoices"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
"FMID" INTEGER DEFAULT FMID('SalesInvoices'),
"CustomerReceivingID" INTEGER,
"CustomerPayingID" INTEGER,
"CustOrderRef" VARCHAR(60) COLLATE "ANSI",
"DispatchRef" VARCHAR(20) COLLATE "ANSI",
"DateWanted" DATE NOT NULL,
"DateSent" DATE,
"DatePaid" DATE,
"StatusID" INTEGER DEFAULT 43 NOT NULL,
"CustomerComment" CLOB COLLATE "ANSI",
"PrivateComment" CLOB COLLATE "ANSI",
"TotalQtyOrdered" FLOAT DEFAULT 0 NOT NULL,
"TotalQtySent" FLOAT DEFAULT 0 NOT NULL,
"TotalWeight" FLOAT DEFAULT 0 NOT NULL,
"TotalGross" DECIMAL(19,4) DEFAULT 0 NOT NULL,
"TotalDiscount" DECIMAL(19,4) DEFAULT 0 NOT NULL,
"TotalVAT" DECIMAL(19,4) DEFAULT 0 NOT NULL,
"TotalNet" DECIMAL(19,4) COMPUTED ALWAYS AS "TotalGross" - "TotalDiscount" + "TotalVAT",
"TotalPaid" DECIMAL(19,4) DEFAULT 0 NOT NULL,
"CarriersID" INTEGER DESCRIPTION 'Custom, Organisations, CarriersLUList',
"ServiceTypeID" INTEGER DESCRIPTION 'ReuseLast',
"ItemTypeID" INTEGER DESCRIPTION 'ReuseLast',
"CountItems" FLOAT DEFAULT 0 NOT NULL,
"EnteredInSage" BOOLEAN DEFAULT false NOT NULL,
"VATFree" BOOLEAN DEFAULT false NOT NULL,
"DateCreated" TIMESTAMP DEFAULT Current_TimeStamp,
"Complete" BOOLEAN DEFAULT False NOT NULL,
"FullName" VARCHAR(80) COLLATE "ANSI" COMPUTED ALWAYS AS 'SI'
 +IF("DateWanted" IS NULL THEN '-no date-' ELSE ' ' + CAST("DateWanted" as VARCHAR(10)))
  +IF("CustOrderRef" IS NULL THEN '-no cust order ref-' ELSE ' ' + "CustOrderRef")
  +IF("TotalNet" IS NULL THEN '-no value-' ELSE ' Value: ' + CAST(CAST(TotalNet as FLOAT(10,2)) as VARCHAR(19))),
"CarriageCost" DECIMAL(19,2) DEFAULT 0 NOT NULL,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("ID")
)!

CREATE TABLE "SalesInvoiceItems"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
"SalesInvoicesID" INTEGER,
"ProductsID" INTEGER DESCRIPTION 'Dependent, SIIDependentLU, SalesInvoicesID',
"ProductionID" INTEGER DESCRIPTION 'Dependent, SIIProductionLUList, ProductsID',
"QtyOrdered" FLOAT DEFAULT 0 NOT NULL,
"QtySent" FLOAT DEFAULT 0 NOT NULL,
"Weight" FLOAT DEFAULT 0 NOT NULL,
"TotalValue" DECIMAL(19,4) DEFAULT 0 NOT NULL,
"Discount" DECIMAL(19,4) DEFAULT 0 NOT NULL DESCRIPTION 'ReadOnly',
"VAT" DECIMAL(19,4) DEFAULT 0 NOT NULL,
"BatchCodesUsed" VARCHAR(250) COLLATE "ANSI",
"LeagueTableExcluded" BOOLEAN DEFAULT false NOT NULL,
"DateCreated" TIMESTAMP DEFAULT Current_TimeStamp,
"Complete" BOOLEAN DEFAULT false NOT NULL,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("ID"),
CONSTRAINT "SalesInvoicesID" FOREIGN KEY ("SalesInvoicesID") REFERENCES "SalesInvoices" ("ID"),
CONSTRAINT "ProductsID" FOREIGN KEY ("ProductsID") REFERENCES "Products" ("ID")
)!

CREATE FUNCTION "NiceDate" (IN "aDate" DATE)
RETURNS VARCHAR COLLATE "ANSI"
BEGIN
 DECLARE Result VARCHAR;
SET Result =
 IF(CAST(aDate AS DATE) = Current_Date + INTERVAL '1' DAY THEN 'Tomorrow' ELSE
 IF(CAST(aDate AS DATE) = Current_Date THEN 'Today' ELSE
   IF(CAST(aDate AS DATE) = Current_Date - INTERVAL '1' DAY THEN 'Yesterday' ELSE
     IF( EXTRACT(DAY FROM aDate) > 9 THEN CAST(EXTRACT(DAY FROM aDate) as VARCHAR) ELSE
                '0' + CAST(EXTRACT(DAY FROM aDate) as VARCHAR)
                ) + '/'
       +  IF( EXTRACT(MONTH FROM aDate) > 9 THEN CAST(EXTRACT(MONTH FROM aDate) as VARCHAR) ELSE
             '0' + CAST(EXTRACT(MONTH FROM aDate) as VARCHAR)
             ) + '/'
       +  IF( EXTRACT(YEAR FROM aDate) > 9 THEN CAST(EXTRACT(YEAR FROM aDate) as VARCHAR) ELSE
             '0' + CAST(EXTRACT(YEAR FROM aDate) as VARCHAR)))));
RETURN Result;
END
VERSION 1.00!

-------------------------------------

3. Run the following SQL: The SQL will run without error, returning an empty result set:

SELECT
'1. Late Sales' as "Heading",
NiceDate(SI.DateWanted) +
'<a HREF = ' + COALESCE(CAST(SI.ID AS VARCHAR), '') + '>'
+ ' FMID:' + COALESCE(CAST(SI.FMID as VARCHAR), '') + ' ' +  COALESCE(O.FullName, '-no customer-') + '</a> '
+ COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')
as "Content"
, DateWanted
FROM
SalesInvoices SI
LEFT JOIN Organisations O ON O.ID = SI.CustomerPayingID
LEFT JOIN SalesInvoiceItems SII ON SII.SalesInvoicesID = SI.ID
LEFT JOIN Products P ON P.ID = SII.ProductsID
WHERE StatusID IN (43, 44, 45)
AND CAST(DateWanted as Date) BETWEEN Current_Date - INTERVAL '14' DAY AND Current_Date - INTERVAL '1' DAY
GROUP BY SI.ID
ORDER BY Heading, Content

-----------------------------------------------

4. Run the following SQL. An error is reported, even though the only change to the SQL is the REMOVAL of 1 field which was present in the SQL for step 3.

SELECT
'1. Late Sales' as "Heading",
NiceDate(SI.DateWanted) +
'<a HREF = ' + COALESCE(CAST(SI.ID AS VARCHAR), '') + '>'
+ ' FMID:' + COALESCE(CAST(SI.FMID as VARCHAR), '') + ' ' +  COALESCE(O.FullName, '-no customer-') + '</a> '
+ COALESCE(REPLACE(',' WITH ', ' IN CAST(LIST(DISTINCT ORDERED P.FullName) as VARCHAR(500))), '')
as "Content"
--, DateWanted
FROM
SalesInvoices SI
LEFT JOIN Organisations O ON O.ID = SI.CustomerPayingID
LEFT JOIN SalesInvoiceItems SII ON SII.SalesInvoicesID = SI.ID
LEFT JOIN Products P ON P.ID = SII.ProductsID
WHERE StatusID IN (43, 44, 45)
AND CAST(DateWanted as Date) BETWEEN Current_Date - INTERVAL '14' DAY AND Current_Date - INTERVAL '1' DAY
GROUP BY SI.ID
ORDER BY Heading, Content

---------------------------------------------

Good luck figuring out what is going on here Tim!

> Good luck figuring out what is going on here Tim!

I'm not Tim, and I don't even play one on TV.   But I'm pretty sure what's going on is that you have to have at least 1 "real" column in the result set.  Add ANY physical column from the SalesInvoice table and it will run.   Not sure if it's a bug or just a requirement of the engine, but at least it's an easy limitation to deal with.