Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: Order by Unknown SQL Datattype


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


Permlink Replies: 3 - Last Post: Dec 24, 2016 11:09 PM Last Post By: Eric ten Westen... Threads: [ Previous | Next ]
Eric ten Westen...

Posts: 8
Registered: 4/4/10
Order by Unknown SQL Datattype  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 16, 2016 4:54 AM
Hi

I have a query like this:

SELECT PRIO,
(SELECT B FROM TO_INT(:TRANSACTIONDATEINT)) - STARTDATEINT
FROM ORGANIZATIONREWARD

This works fine

Now i want to order on the two fields

SELECT PRIO,
(SELECT B FROM TO_INT(:TRANSACTIONDATEINT)) - STARTDATEINT
FROM ORGANIZATIONREWARD
ORDER BY 1, 2

i got the error unknown sql datatype

SELECT CAST( (SELECT B FROM TO_INT(:TRANSACTIONDATEINT)) - STARTDATEINT) AS Integer

gives other error

Any help is appreciated!

Eric
quinn wildman

Posts: 856
Registered: 12/2/99
Re: Order by Unknown SQL Datattype  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 21, 2016 1:03 PM   in response to: Eric ten Westen... in response to: Eric ten Westen...
TO_INT isn't making sense to me here. InterBase has no such function. InterBase also does not allow parameters for table names. However, perhaps you are performing some sort of FireDac magic? I suggest your query should look like: select (select cast(b as integer) ...

Eric ten Westenend wrote:
Hi

I have a query like this:

SELECT PRIO,
(SELECT B FROM TO_INT(:TRANSACTIONDATEINT)) - STARTDATEINT
FROM ORGANIZATIONREWARD

This works fine

Now i want to order on the two fields

SELECT PRIO,
(SELECT B FROM TO_INT(:TRANSACTIONDATEINT)) - STARTDATEINT
FROM ORGANIZATIONREWARD
ORDER BY 1, 2

i got the error unknown sql datatype

SELECT CAST( (SELECT B FROM TO_INT(:TRANSACTIONDATEINT)) - STARTDATEINT) AS Integer

gives other error

Any help is appreciated!

Eric
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Order by Unknown SQL Datattype  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 21, 2016 3:09 PM   in response to: quinn wildman in response to: quinn wildman
quinn wildman wrote:
TO_INT isn't making sense to me here. InterBase has no such function. InterBase also does not allow parameters for table names. However, perhaps you are performing some sort of FireDac magic? I suggest your query should look like: select (select cast(b as integer) ...

No TO_INT is a selectable stored procedure that that returns an integer column
to get around the drawback that parameters in the select portion and used in
arithmetic results in an unknown datatype error.

--
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: 8
Registered: 4/4/10
Re: Order by Unknown SQL Datattype  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 24, 2016 11:09 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Yes Jeff,

i got this solution from you.

But is there any way to sort on such a column?

Eric

Jeff Overcash (TeamB) wrote:
quinn wildman wrote:
TO_INT isn't making sense to me here. InterBase has no such function. InterBase also does not allow parameters for table names. However, perhaps you are performing some sort of FireDac magic? I suggest your query should look like: select (select cast(b as integer) ...

No TO_INT is a selectable stored procedure that that returns an integer column
to get around the drawback that parameters in the select portion and used in
arithmetic results in an unknown datatype error.

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