Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: SQL error code -607



Permlink Replies: 3 - Last Post: Nov 8, 2017 12:43 AM Last Post By: Joachim Breuer
Joachim Breuer

Posts: 14
Registered: 4/10/03
SQL error code -607
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 23, 2017 2:45 AM
Hello,

this SQL statement is from an Advantage Database Server. IBConsole with Interbase 2017 give me this error.
Which columns do i have to specify?

Error at line 1
Dynamic SQL Error
SQL error code = -607
Invalid command
must specify column name for view select expression
SQL - select KADS1.* from ADS as KADS1
inner join (select kdKundenNr, max(kdBestelldatum) as dBestelldatum from ADS group by kdKundenNr) as KADS2
on KADS1.kdKundenNr = KADS2.kdKundenNr and KADS1.kdBestelldatum = KADS2.dBestelldatum
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: SQL error code -607
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 24, 2017 12:02 PM   in response to: Joachim Breuer in response to: Joachim Breuer
Joachim Breuer wrote:
Hello,

this SQL statement is from an Advantage Database Server. IBConsole with Interbase 2017 give me this error.
Which columns do i have to specify?

Error at line 1
Dynamic SQL Error
SQL error code = -607
Invalid command
must specify column name for view select expression
SQL - select KADS1.* from ADS as KADS1
inner join (select kdKundenNr, max(kdBestelldatum) as dBestelldatum from ADS group by kdKundenNr) as KADS2
on KADS1.kdKundenNr = KADS2.kdKundenNr and KADS1.kdBestelldatum = KADS2.dBestelldatum

You might want to ask this in the IB group as this will be 100% a server issue.

One thing you can try though is alias inside the derived table and qualify those
columns. Like

(select kad.kdKundenNr, max(kad.kdBestelldatum) as dBestelldatum from ADS kad
group by kad.kdKundenNr) as KADS2

Derived tables are new in 2017 so there are bound to be some issues in them.
You can also try the with version of it

WITH KADS2 as (select kdKundenNr, max(kdBestelldatum) as dBestelldatum from ADS
group by kdKundenNr)
select KADS1.*
from ADS as KADS1 join KADS2 on
KADS1.kdKundenNr = KADS2.kdKundenNr and
KADS1.kdBestelldatum = KADS2.dBestelldatum

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: SQL error code -607
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 24, 2017 12:04 PM   in response to: Joachim Breuer in response to: Joachim Breuer
Joachim Breuer wrote:
Hello,

this SQL statement is from an Advantage Database Server. IBConsole with Interbase 2017 give me this error.
Which columns do i have to specify?

Error at line 1
Dynamic SQL Error
SQL error code = -607
Invalid command
must specify column name for view select expression
SQL - select KADS1.* from ADS as KADS1
inner join (select kdKundenNr, max(kdBestelldatum) as dBestelldatum from ADS group by kdKundenNr) as KADS2
on KADS1.kdKundenNr = KADS2.kdKundenNr and KADS1.kdBestelldatum = KADS2.dBestelldatum

Also when you have issues like this giving the table structure metadata
(including indexes) helps because we can recreate this fast and easy and then
play with the syntax.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Joachim Breuer

Posts: 14
Registered: 4/10/03
Re: SQL error code -607
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 8, 2017 12:43 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
Joachim Breuer wrote:
Hello,

this SQL statement is from an Advantage Database Server. IBConsole with Interbase 2017 give me this error.
Which columns do i have to specify?

Error at line 1
Dynamic SQL Error
SQL error code = -607
Invalid command
must specify column name for view select expression
SQL - select KADS1.* from ADS as KADS1
inner join (select kdKundenNr, max(kdBestelldatum) as dBestelldatum from ADS group by kdKundenNr) as KADS2
on KADS1.kdKundenNr = KADS2.kdKundenNr and KADS1.kdBestelldatum = KADS2.dBestelldatum

Also when you have issues like this giving the table structure metadata
(including indexes) helps because we can recreate this fast and easy and then
play with the syntax.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Hello Jeff,
thanks for your response. WITH did not helped.

Here is the table structure:

CREATE TABLE "ADS"
(
"KDKUNDENNR" INTEGER,
"KDBESTELLDATUM" DATE,
"KDVKPREIS" DOUBLE PRECISION,
"KDEKPREIS" DOUBLE PRECISION,
"KDDATUM" DATE,
"KDAP" SMALLINT,
"KDSYSTEM" SMALLINT,
"KDVERSION" SMALLINT,
"KDSERIENNR" VARCHAR(7) COLLATE DE_DE,
"KDVALIDATION" VARCHAR(5) COLLATE DE_DE,
"KDBEMERKUNG" BLOB SUB_TYPE TEXT SEGMENT SIZE 80
);

CREATE UNIQUE INDEX "IDX_KDPRIMARY" ON "ADS"("KDKUNDENNR", "KDBESTELLDATUM");
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02