SQL Question

[Bart Pietercil]

Hi Thorsten,

well this crashed VS :-)

your suggestion translated to this for me (it is part of a stored proc)
---------------
create or replace procedure mobistar_advita_import_prepare()
begin
set property DateTimeFormat of database to 'kYMD';
--replace 'Mobistar' with objptr information
declare mobistarPtr llong;
set mobistarPtr = select recid from tbl_organisations where  
tbl_organisations.objptr_org is null and org_name like '%Mobistar%';

update advita_import set org_id = mobistarPtr;

-- identify new users and existing users by verifying the hr_db_id

update advita_import set user_recid = ( select tbl_users.recid from  
tbl_users where advita_import.employee_id = tbl_users.hr_db_id);


select ** from advita_import;
end;
------

This is the line I translated your suggestion to:
update advita_import set user_recid = ( select tbl_users.recid from  
tbl_users where advita_import.employee_id = tbl_users.hr_db_id);

It was accepted by the parser but calling the proc --> crash

What version were you using ?

BArt



On 30 Jan 2009, at 23:26, Thorsten Hohage wrote:

> Hi Bart,
>
> I understand what you want ;-)
>
> On 2009-01-30, at 22:58, Bart Pietercil wrote:
>> This query is not accepted by Valentina but explains what I want to  
>> do
>>
>> update a set user_recid = u.recid FROM advita_import a
>> join tbl_users u on a.employee_id = u.hr_db_id;
> I often want to use the exact same style of query. Having two related
> tables and then update one field of one of these tables with value(s)
> from the other.
>
>
>> some database accept this syntax but ok, so I considered making a  
>> view
> yes, some more IMHO
>
>
>> But it seems valentina views are only readonly so that won't work
>> either.
> Yes - and guess what - I asked the exact same question for exact same
> reason 3 days ago - "brother in way of thinking" :-)
>
>
>> I know I can do this using subselects but just can't get my head
>> around it. Probably just too late...
>
> I was not able to use any style of subselects (because Valentina
> doesn't allow relations here - as many other DBs allow), so I ended up
> with two different options depending on the task
>
> a) use a temp table, do the necessary join and export the result to
> the temp table
> b) use a stored procedure
>
>
> A now the stunning surprise at the end !!!!!!!!!!!!!!!! As always
> these guys are faster then you may think of a wish :-)
>
>
> THIS statement works (for me surprisingly in the new v4 Beta)
>
> 	update insertJoin1 set newID =
> 		(select insertJoin2.toInsertId from insertJoin2 where
> insertJoin1.toLinkId = insertJoin2.linkedId)
>
> someone must have done some excellent improvements.
>
>
> regards,
>
> Thorsten Hohage
> -- 
>
> Valentina Technology Evangelist
> generic objects  GmbH - Leiter Solution Center Nord
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina