Thank you once again Elke. It works perfectly. I have a lot to learn about what you can get away with in the syntax.. Arthur
"Zabach, Elke" <[EMAIL PROTECTED]> wrote in message B9A2DDA03044D311BBD40008C75D69680DBAB60C@dewdfx24">news:B9A2DDA03044D311BBD40008C75D69680DBAB60C@dewdfx24... > 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
