Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: is ther any change getting this SQL to work?


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 4 - Last Post: Dec 7, 2016 3:55 AM Last Post By: Eric ten Westen...
Eric ten Westen...

Posts: 78
Registered: 12/8/99
is ther any change getting this SQL to work?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 2, 2016 12:13 AM
Hi

Is there any way to get this working:

select (:TRANSACTIONDATEINT - STARTDATEINT)
from COMPANYREWARD

STARTDATEINT is an INTEGER field

Eric

quinn wildman

Posts: 856
Registered: 12/2/99
Re: is ther any change getting this SQL to work?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 5, 2016 2:14 PM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Your query looks OK. What error do you get? What is the context (FireDac, IBX or part of Stored procedure or trigger)?

Eric ten Westenend wrote:
Hi

Is there any way to get this working:

select (:TRANSACTIONDATEINT - STARTDATEINT)
from COMPANYREWARD

STARTDATEINT is an INTEGER field

Eric

Eric ten Westen...

Posts: 10
Registered: 6/1/15
Re: is ther any change getting this SQL to work?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 6, 2016 5:55 AM   in response to: quinn wildman in response to: quinn wildman
Hi

IBX

Eric

quinn wildman wrote:
Your query looks OK. What error do you get? What is the context (FireDac, IBX or part of Stored procedure or trigger)?

Eric ten Westenend wrote:
Hi

Is there any way to get this working:

select (:TRANSACTIONDATEINT - STARTDATEINT)
from COMPANYREWARD

STARTDATEINT is an INTEGER field

Eric

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: is ther any change getting this SQL to work?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 6, 2016 10:08 AM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Eric ten Westenend wrote:
Hi

IBX

Eric

quinn wildman wrote:
Your query looks OK. What error do you get? What is the context (FireDac, IBX or part of Stored procedure or trigger)?

Eric ten Westenend wrote:
Hi

Is there any way to get this working:

select (:TRANSACTIONDATEINT - STARTDATEINT)
from COMPANYREWARD

STARTDATEINT is an INTEGER field

Eric


InterBase will give you either an Expression Evaluation error there or datatype
unknown. You can not set the Data type on the client side, the server has to be
able to prepare it first and the error is happening on the prepare before you
get a whack at fixing it up. Casts to a data type do not seem to work.

Two possible workarounds to this

First is convert this to a stored procedure. Pass the parameter and then select
the raw data and adjust it before returning the column.

Pro - Less work on the client side
Con - A lot of extra SP's for this (although with Execute Statement you might be
able to do a generic single one).

This can also be done where you return the type you want in a SP and then write
your SQL like

select (select b from to_int(:a)) - qty_ordered
from sales

Where TO_INT simply looks like

CREATE PROCEDURE TO_INT
(
   A INTEGER
)
RETURNS
(
   B INTEGER
)
AS
begin
   B = A;
   suspend;
end


---

Second use Calculated fields. Add an extra adjusted calculated field. Instead
of setting a parameter, just set a form/DM variable to the param value and use
it in the OnCalField event to adjust your column and use the Calculated column
where you would have used the column you are trying to show.

Pro no real server side work needed.
Cons - you have to persist the fields which stops reuse of a single qry to be
reused multiple times without a lot of hassle and is fragile to DB meta changes.

--
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)

Eric ten Westen...

Posts: 10
Registered: 6/1/15
Re: is ther any change getting this SQL to work?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 7, 2016 3:55 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thanks Jeff!


Jeff Overcash (TeamB) wrote:
Eric ten Westenend wrote:
Hi

IBX

Eric

quinn wildman wrote:
Your query looks OK. What error do you get? What is the context (FireDac, IBX or part of Stored procedure or trigger)?

Eric ten Westenend wrote:
Hi

Is there any way to get this working:

select (:TRANSACTIONDATEINT - STARTDATEINT)
from COMPANYREWARD

STARTDATEINT is an INTEGER field

Eric


InterBase will give you either an Expression Evaluation error there or datatype
unknown. You can not set the Data type on the client side, the server has to be
able to prepare it first and the error is happening on the prepare before you
get a whack at fixing it up. Casts to a data type do not seem to work.

Two possible workarounds to this

First is convert this to a stored procedure. Pass the parameter and then select
the raw data and adjust it before returning the column.

Pro - Less work on the client side
Con - A lot of extra SP's for this (although with Execute Statement you might be
able to do a generic single one).

This can also be done where you return the type you want in a SP and then write
your SQL like

select (select b from to_int(:a)) - qty_ordered
from sales

Where TO_INT simply looks like

CREATE PROCEDURE TO_INT
(
   A INTEGER
)
RETURNS
(
   B INTEGER
)
AS
begin
   B = A;
   suspend;
end


---

Second use Calculated fields. Add an extra adjusted calculated field. Instead
of setting a parameter, just set a form/DM variable to the param value and use
it in the OnCalField event to adjust your column and use the Calculated column
where you would have used the column you are trying to show.

Pro no real server side work needed.
Cons - you have to persist the fields which stops reuse of a single qry to be
reused multiple times without a lot of hassle and is fragile to DB meta changes.

--
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)

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02