I know the sql below doesn't work. Is there a way to achieve the same result with different code?

INSERT INTO Calls
(_fkCompanies,
_fkSites,
_fkProjects,
_fkContacts,
_fkCareer,
_DateAdded,
_Priority,
_Done)

VALUES
(:CompanyID,
:SiteID,
:ProjectID,
:ContactID,
(select _ID FROM Career WHERE _fkContacts = 100011 and _fkcompanies = 105632 and _fkSites = 0 TOP 1),  <<<<<<<<<<<<<<<
CURRENT_DATE,
3,
False)

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in
news:2E5CFC51-96B4-490B-89B8-C5DF0EF369CB@news.elevatesoft.com:

> INSERT INTO Calls
> (_fkCompanies,
> _fkSites,
> _fkProjects,
> _fkContacts,
> _fkCareer,
> _DateAdded,
> _Priority,
> _Done)
>
> VALUES
> (:CompanyID,
>:SiteID,
>:ProjectID,
>:ContactID,
> (select _ID FROM Career WHERE _fkContacts = 100011 and _fkcompanies =
> 105632 and _fkSites = 0 TOP 1), ÿ<<<<<<<<<<<<<<< CURRENT_DATE,
> 3,
> False)
>

Roy,

What about this:

INSERT INTO Calls
(_fkCompanies,
_fkSites,
_fkProjects,
_fkContacts,
_fkCareer,
_DateAdded,
_Priority,
_Done)

SELECT
 :CompanyID,
 :SiteID,
 :ProjectID,
 :ContactID,
 _ID,
 CURRENT_DATE,
 3,
 False
FROM Career
WHERE _fkContacts = 100011 and _fkcompanies = 105632 and _fkSites = 0
TOP 1

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Chris


Sneaky - I like it, and it works.

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in news:904B1234-0925-465F-
B538-4C6B161C7F4B@news.elevatesoft.com:

> Sneaky - I like it, and it works.

Good!

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Roy,

<< Sneaky - I like it, and it works. >>

Actually, it shouldn't - the parameters shouldn't be allowed in the SELECT
statement.   However, if it works, who am I to change it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Actually, it shouldn't - the parameters shouldn't be allowed in the SELECT
>statement. However, if it works, who am I to change it.

In that case will it be allowed in ElevateDB? If not I might as well look for other ways now.

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in
news:6B89258F-A562-4E8C-BD7A-706482D952ED@news.elevatesoft.com:

Tim,

>>Actually, it shouldn't - the parameters shouldn't be allowed in the
>>SELECT statement.

When you say shouldn't, do you mean SQL2003 or DBISAM technology?

> In that case will it be allowed in ElevateDB? If not I might as well
> look for other ways now.

Could you make that:

In that case COULD IT PLEASE be allowed in ElevateDB - it's a pretty handy
technique.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Roy,

<< In that case will it be allowed in ElevateDB? If not I might as well look
for other ways now. >>

No, but EDB allows you to use your original version (except for the TOP,
which isn't needed anyways):

INSERT INTO Calls
(_fkCompanies,
_fkSites,
_fkProjects,
_fkContacts,
_fkCareer,
_DateAdded,
_Priority,
_Done)

VALUES
(:CompanyID,
:SiteID,
:ProjectID,
:ContactID,
(select _ID FROM Career WHERE _fkContacts = 100011 and _fkcompanies = 105632
and _fkSites = 0), CURRENT_DATE,
3,
False)

--
Tim Young
Elevate Software
www.elevatesoft.com

Chris,

<< When you say shouldn't, do you mean SQL2003 or DBISAM technology? >>

SQL2003 - you can't use parameters as SELECT column expressions.

<< Could you make that:

In that case COULD IT PLEASE be allowed in ElevateDB - it's a pretty handy
technique. >>

You don't need it - see my reply to Roy.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


<Arrrrrgh>

Its going to be real fun tracking down all these little weirdo's when I get to EDB'ing an app.
</Arrrrrgh>
Roy Lambert