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 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
