SQL help

[Bart Pietercil]

Hmm, it seems to me that your db schema is suboptimal here but without knowing the schema this remains of course guessing.
I would  expect your schema to be something like this

tbl_voyages

voyage_id,
fk_vessel_id,
from,
to,
departure,
arrival,
….


tbl_vessels

vessel_id,
vessel_name,
vessel_capacity,
…

both voyage_id and vessel_id are sequences.
fk_vessel_id is the foreign key holding the vessel_id for the vessel that is doing the voyage (voyage_id).

If this is your schema then please elaborate on the problem your having.

regards

bart


— 
Our office will be closed on:
24 (afternoon) - 25 - 31/12/2013 & 01/01/2014

Bart Pietercil
Sent with aMac

On 3 Jan 2014 at 23:55:08, william humphrey (bill at bluewatermaritime.com) wrote:

Bart  

Thank you for the clear description. I'm still trying to decide which  
would be more useful for me. There are like thirty vessels and each  
new voyage will be for one of the vessels. Only one vessel in each new  
voyage. But if I just auto increment for voyages it is not as useful  
as auto increment that is unique only to each vessel for that voyage.  
That would mean the field would not be a unique value for the voyages  
as it would only be the combination of vessel number and voyage number  
only that is unique.  

Bill  

On Fri, Jan 3, 2014 at 5:56 PM, Bart Pietercil  
<bart.pietercil at cognosis.be> wrote:  
> Theoretically you can. What I don’t know is whether the Valentina Engine  
> already supports it.  
> The sequence generator has been added to vStudio in order to support the  
> Postgresql engine.  
> In Postgresql you would do  
>  
> add field vessel_nr serial (or big serial).  
> This is shorthand for  
>  
> add field vessel_nr integer (or bigint) unique  
> create sequence seq_vessel_nr  
> set default value of field vessel_nr to nextval(seq_vessel_nr)  
>  
> so now you have a field vessel_nr with an autoincrementing value on every  
> insert (creation of vessel trip?)  
> the only thing left to do is , if you already have data in your vessel  
> table, generate numbers for the existing values.  
> And the last thing to do is set the startvalue of seq_vessel_nr to  
> max(vessel_nr)+1.  
>  
> From hereon your vessel_nr will autoincrement with the correct number  
>  
> hth  
>  
> bart  
> —  
> Our office will be closed on:  
> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014  
>  
> Bart Pietercil  
> Sent with aMac  
>  
> On 3 Jan 2014 at 16:47:59, william humphrey (bill at bluewatermaritime.com)  
> wrote:  
>  
> Can that kind of create sequence be done after the fact of table  
> finished and data already in?  
>  
> On Fri, Jan 3, 2014 at 11:23 AM, Ruslan Zasukhin  
> <ruslan_zasukhin at valentina-db.com> wrote:  
>> On 1/3/14, 4:46 PM, "william humphrey" <bill at bluewatermaritime.com> wrote:  
>>  
>>> I've been thinking that a sequence maintained with a trigger might be  
>>> very useful for other things too. I never thought of numbering  
>>> sequentially the voyages of each vessel. It solves some other  
>>> problems. Right now voyage numbers increment from zero at the start of  
>>> each new year (which is what caused the problem) but there is no  
>>> reason why I can't have an extra field which has every voyage for that  
>>> vessel down through the years.  
>>  
>> Do you talk about CERATE SEQUENCE?  
>>  
>>  
>> For simple case, consider SERIAL32 or SERIAL64 pseudo-types,  
>> As even more simple things.  
>>  
>>  
>> --  
>> Best regards,  
>>  
>> Ruslan Zasukhin  
>> VP Engineering and New Technology  
>> Paradigma Software, Inc  
>>  
>> Valentina - Joining Worlds of Information  
>> http://www.paradigmasoft.com  
>>  
>> [I feel the need: the need for speed]  
>>  
>>  
>> _______________________________________________  
>> Valentina mailing list  
>> Valentina at lists.macserve.net  
>> http://lists.macserve.net/mailman/listinfo/valentina  
>  
>  
>  
> --  
> http://www.bluewatermaritime.com  
> _______________________________________________  
> Valentina mailing list  
> Valentina at lists.macserve.net  
> http://lists.macserve.net/mailman/listinfo/valentina  
>  
>  
> _______________________________________________  
> Valentina mailing list  
> Valentina at lists.macserve.net  
> http://lists.macserve.net/mailman/listinfo/valentina  
>  



--  
http://www.bluewatermaritime.com  
_______________________________________________  
Valentina mailing list  
Valentina at lists.macserve.net  
http://lists.macserve.net/mailman/listinfo/valentina  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20140104/2df706e5/attachment-0001.html>