Arthur Brand wrote: > Thank you for your help Elke. > > The client application I was using for my test script is SQL > Studio that > installs with SAPDB. > The square brackets [] would work well if it only prompted > once for each > unique parameter. > > I looked at the Correlated subquery, but I am still a bit lost. > Both of the following scripts gives me the same error: > > "General error;-7015 POS(1) More than one result row not allowed." > //Attempt 1 > UPDATE Child > SET ChildPhoneNum = (Select DISTINCT P.ParentPhoneNum FROM > Child C, Parent P > WHERE C.ParentId = P.ParentId AND C.ChildPhoneNum IS NULL) > WHERE ChildPhoneNum IS NULL > > //Attempt 2 > UPDATE Child > SET ChildPhoneNum = (Select P.ParentPhoneNum FROM Parent P, Child C > WHERE C.ParentId = P.ParentId AND C.ChildID= ChildID) > WHERE ChildPhoneNum IS NULL
Hm, I was talking about a correlated subquery, a subquery which uses the outer table in the inner select WITHOUT specifying it in the from-part of the inner-select: UPDATE Child C SET ChildPhoneNum = (Select DISTINCT P.ParentPhoneNum FROM Parent P WHERE C.ParentId = P.ParentId AND C.ChildPhoneNum IS NULL) WHERE ChildPhoneNum IS NULL You asked (in the inner select, which is a non-correlated subquery) for ALL ParentPhoneNums where at least one Child has no telephone. But you want for each row to be updated the PhoneNum of the parent of the child you are just updating. Elke SAP Labs Berlin > Should I be using CURRENT OF? I can not figure out how to > implement this > from the reference_74eng.pdf manual. > Are there any other documents with more example scripts perhaps. > > Thank you > Arthur > > "Zabach, Elke" <[EMAIL PROTECTED]> wrote in message > B9A2DDA03044D311BBD40008C75D69680DBAB5F3@dewdfx24">news:B9A2DDA03044D311BBD40008C75D69680DBAB5F3@dewdfx24... > > Arthur Brand wrote: > > > > > > > > Good day all. Can anyone help please. > > > My apologies if this is a stupid question or FAQ. I did scan > > > the archive > > > first. > > > > > > I am new to SAPDB and trying to adjust to the syntax > > > differences from MS > > > SQL. I'm sure this basic task can be done in sapdb, it's the > > > how I'm stuck > > > on. > > > > > > I have two questions regarding translating MS SQL to SAPDB. > > > > > > Q1. Temporary Variables outside Stored procedures > > > How do I declare a temporary variables outside a stored procedure? > > > Below is a simplified version of a test script I use to check > > > data. When > > > using Sap's square brackets [] technique, I get 3 prompts > for the same > > > variable. I have also tried $. > > > > > > DECLARE @Period INT > > > > > > SET @Period= 147 //<--Change this value to run for new period > > > > > > SELECT COUNT(*) AS Qty, 'Active' AS Status FROM CLIENTS > WHERE STAUS = > > > 'Active' AND PERIOD = @Period > > > UNION > > > SELECT COUNT(*) AS Qty, 'On Hold' AS Status FROM CLIENTS > > > WHERE STAUS = 'On > > > Hold' AND PERIOD = @Period > > > UNION > > > SELECT COUNT(*) AS Qty, 'Cancelled' AS Status FROM CLIENTS > > > WHERE STAUS = > > > 'Cancelled' AND PERIOD = @Period > > > ------------------------------- > > > > > > In SQL there is no place to declare variables outside > stored procedures. > > You will have to check the syntax of the client/application > you want to > use how to declare it. > > > > But within SQL-statements you can USE variables. But they > are prefixed > with :, not with @. > > > > > > > > Q2. Update one table with select from two tables. > > > SAPDB does not seem to like more than one table in the FROM > > > > > > simplified example below: > > > UPDATE C > > > SET C.ChildPhoneNum = P.ParentPhoneNum > > > FROM Child C, Parent P > > > WHERE C.ParentId = P.ParentId AND C.ChildPhoneNum IS NULL > > > > As you found out correctly, SAP DB does not allow two tables in the > top-part > > of an update-statement. > > You will need a correlated subquery. > > > > Elke > > SAP Labs Berlin > > > > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
