Hello

Is there a option to create a sql  with select statement that will return
a field that increase the a given number by 1. ?
Something like AutoInc field

Thanks
Sorin

No, but you could write a function to do it.  Just for fun, I threw one
together:

FUNCTION "IncField" (IN "ID" INTEGER)
RETURNS INTEGER
BEGIN
  DECLARE CurrValue INTEGER;

  EXECUTE IMMEDIATE 'SELECT MyField INTO ? FROM MyTable WHERE ID=?'
             USING CurrValue, ID;
  SET CurrValue = CurrValue + 1;
  EXECUTE IMMEDIATE 'UPDATE MyTable SET MyField=? WHERE ID=?'
             USING CurrValue, ID;

  RETURN CurrValue;
END



--
David Cornelius
Cornelius Concepts

On 8/8/2010 8:48 AM, Sorin H wrote:
> Hello
>
> Is there a option to create a sql  with select statement that will return
> a field that increase the a given number by 1. ?
> Something like AutoInc field
>
> Thanks
> Sorin
>
>

David


Or something slightly whacky

select runsum(1),_id from contacts group by _id

Roy Lambert [Team Elevate]

Roy,

My understanding is that he wanted to leave the field actually updated
in the database with the incremented value.  A SELECT will not
accomplish that without a user-written function.

But, if he only needs it for the life of the result set, then yes, you
have a great solution.

--
David Cornelius
Cornelius Concepts

On 8/8/2010 11:59 PM, Roy Lambert wrote:
> Or something slightly whacky
>
> select runsum(1),_id from contacts group by _id

David


My interpretation was based on <<  with select statement that will return>> but I can also come up with your version of reality

For me the bad news about your function is that its table & field specific. It could be extended to have the table and column names as part of the input but then its getting to the point of its easier to use separate update and select statements.

Roy Lambert

Yes, I realize that, but I just threw the function together as a
proof-of-concept and example.  He can take it from there and morph it to
whatever he needs.

--
David Cornelius
Cornelius Concepts

On 8/9/2010 3:24 AM, Roy Lambert wrote:
> My interpretation was based on<<   with select statement that will return>>  but I can also come up with your version of reality
>
> For me the bad news about your function is that its table&  field specific. It could be extended to have the table and column names as part of the input but then its getting to the point of its easier to use separate update and select statements.

Thanks

Roy solution doesn't suit because the sql is grouped by a diferrent field.
David solution raise an error or I don't know how to call it on the select
statement.

What I need is on the sql:
SELECT tbl2.Name, SUM(tbl1.SumPay), IncField(100) AS No
FROM tbl1
LEFT JOIN tbl2 ON ...
GROUP BY tbl2.Name

the result of fields No will be from 100 increase by 1.

thanks
Sorin


"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:DD04E755-8DAB-4743-97F7-A854669575A2@news.elevatesoft.com...
> David
>
>
> My interpretation was based on << with select statement that will return>>
> but I can also come up with your version of reality
>
> For me the bad news about your function is that its table & field
> specific. It could be extended to have the table and column names as part
> of the input but then its getting to the point of its easier to use
> separate update and select statements.
>
> Roy Lambert
>

David


I wonder how feasible a CURRENT_TABLE variable would be? I can never see a CURRENT_COLUMN but table might be doable.

Roy Lambert

Sorin

>Roy solution doesn't suit because the sql is grouped by a diferrent field.

Why is that a problem? Just extend it a little eg a totally nonsense query in terms of my tables

SELECT Contacts._Forename,_Surname,SUM(Career._fkSites),100+RUNSUM(1)
FROM Career
JOIN Contacts ON Contacts._ID = Career._fkContacts
GROUP BY Contacts._Surname

does what it is I think you want

>David solution raise an error or I don't know how to call it on the select
>statement.

David's would have needed a bit of work to make it functional since it had fictional table and column names in it.

Roy Lambert [Team Elevate]

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message

> Why is that a problem? Just extend it a little eg a totally nonsense query
> in terms of my tables
>
> SELECT Contacts._Forename,_Surname,SUM(Career._fkSites),100+RUNSUM(1)
> FROM Career
> JOIN Contacts ON Contacts._ID = Career._fkContacts
> GROUP BY Contacts._Surname
>
> does what it is I think you want

Thanks but no.

The counter jump by the number of records inside the group
not by 1.

Thanks
Sorin