bcolladay
asked on
Stored Procedure Not Doing Update
This is the log from a stored procedure in Pervasive SQL. It shows the SP being created & Executed. It doesn't update as expected. The next query in the log is an update that does what I want, and it successfully updates the Table. BillBach was helping me on the original question but I think this is beyonfd the scope of the original question.
<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail8() WITH DEFAULT HANDLER
AS BEGIN
DECLARE :BillToEmail CHAR(40);
DECLARE :PatientEmail CHAR(40);
DECLARE :BTBilltoID INTEGER;
DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS FOR UPDATE;
OPEN curs;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
WHILE(SQLSTATE = '00000') DO
IF :BillToEmail = '' THEN
SET :PatientEMail = '';
SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
UPDATE SET Email = :PatientEmail WHERE CURRENT OF curs;
END IF;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
END WHILE;
CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
Execute SyncEmail8()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
update BILLTOS
set BILLTOS.Email = PATIENTS.Email
from PATIENTS, BILLTOS
where PATIENTS.BillToID = BILLTOS.BillToID
and PATIENTS.Relationship = '1'
and PATIENTS.Email like '%@%'
and not(BILLTOS.Email like '%@%')
SQL statement(script) has executed successfully.
9 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail8() WITH DEFAULT HANDLER
AS BEGIN
DECLARE :BillToEmail CHAR(40);
DECLARE :PatientEmail CHAR(40);
DECLARE :BTBilltoID INTEGER;
DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS FOR UPDATE;
OPEN curs;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
WHILE(SQLSTATE = '00000') DO
IF :BillToEmail = '' THEN
SET :PatientEMail = '';
SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
UPDATE SET Email = :PatientEmail WHERE CURRENT OF curs;
END IF;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
END WHILE;
CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
Execute SyncEmail8()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
update BILLTOS
set BILLTOS.Email = PATIENTS.Email
from PATIENTS, BILLTOS
where PATIENTS.BillToID = BILLTOS.BillToID
and PATIENTS.Relationship = '1'
and PATIENTS.Email like '%@%'
and not(BILLTOS.Email like '%@%')
SQL statement(script) has executed successfully.
9 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
The "rows were affected" line is added after the statement is executed by the PCC. It does not return the number of records affected when a stored procedure is executed. As far as the PCC is concerned, no records were affected by the "EXEC" statement. Records may have been affected within the SP but not by the EXEC.
Check the table after to see if the records were updated.
Check the table after to see if the records were updated.
ASKER
knightEknight: I tried it with and without the Table name in the update statement in the SP. same result.
mirtheil: in the example of text output above those are run within a few seconds of each other. between the SP Execute and the regular UPDATE statement I looked at the table and the rows are not getting updated, You can see the UPDATE statement correctly reports the number of rows updates as I setup and expected in the test data. I then look and those rows I had previously cleared are re-populated.
mirtheil: in the example of text output above those are run within a few seconds of each other. between the SP Execute and the regular UPDATE statement I looked at the table and the rows are not getting updated, You can see the UPDATE statement correctly reports the number of rows updates as I setup and expected in the test data. I then look and those rows I had previously cleared are re-populated.
Can you give an example of the data you get with this query:
SELECT BillToID, Email FROM BILLTOS
Looking at the stored procedure, the only time the Update is run is when the :BillToEmail = ''.
SELECT BillToID, Email FROM BILLTOS
Looking at the stored procedure, the only time the Update is run is when the :BillToEmail = ''.
ASKER
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ========================== ========== ====
64
65 bill@gates.com
66
67
68
69
71
72
80
55
56 bill@gates.com
57
58
59
60
61
62 bill@gates.com
63
46
47
48
49
50
51
52
53
54
37
38
39 bill@gates.com
40 bill@gates.com
41
42
43
44
45 bill@gates.com
28
29
30
31
32
33
34
35
36
19
20
21
22
23
24 bill@gates.com
25
26
27
10 bill@gates.com
11
12
13 bill@gates.com
14
15
16
17 bill@gates.com
18
1
2
3
4
5
6
7
8 bill@gates.com
9
84 bill@gates.com
85
86
88
89
90
92 bill@gates.com
93
94
101 bill@gates.com
102
103 bill@gates.com
104
105
106
107
110 bill@gates.com
111 bill@gates.com
112
113
114
116
117
118
119
120
121
122
123
124
125
126
127
128 bill@gates.com
129
130
131
132 bill@gates.com
133 bill@gates.com
111 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
BillToID Email
=========== ==========================
64
65 bill@gates.com
66
67
68
69
71
72
80
55
56 bill@gates.com
57
58
59
60
61
62 bill@gates.com
63
46
47
48
49
50
51
52
53
54
37
38
39 bill@gates.com
40 bill@gates.com
41
42
43
44
45 bill@gates.com
28
29
30
31
32
33
34
35
36
19
20
21
22
23
24 bill@gates.com
25
26
27
10 bill@gates.com
11
12
13 bill@gates.com
14
15
16
17 bill@gates.com
18
1
2
3
4
5
6
7
8 bill@gates.com
9
84 bill@gates.com
85
86
88
89
90
92 bill@gates.com
93
94
101 bill@gates.com
102
103 bill@gates.com
104
105
106
107
110 bill@gates.com
111 bill@gates.com
112
113
114
116
117
118
119
120
121
122
123
124
125
126
127
128 bill@gates.com
129
130
131
132 bill@gates.com
133 bill@gates.com
111 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
ASKER
SELECT BillToID, Email FROM PATIENTS where email like '%@%' and relationship = '1'
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ========================== ========== ====
17 bill@gates.com
39 bill@gates.com
62 bill@gates.com
45 bill@gates.com
65 bill@gates.com
8 bill@gates.com
10 bill@gates.com
40 bill@gates.com
56 bill@gates.com
13 bill@gates.com
84 bill@gates.com
92 bill@gates.com
101 bill@gates.com
103 bill@gates.com
110 bill@gates.com
13 bill@gates.com
24 bill@gates.com
128 bill@gates.com
132 bill@gates.com
133 bill@gates.com
20 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ==========================
17 bill@gates.com
39 bill@gates.com
62 bill@gates.com
45 bill@gates.com
65 bill@gates.com
8 bill@gates.com
10 bill@gates.com
40 bill@gates.com
56 bill@gates.com
13 bill@gates.com
84 bill@gates.com
92 bill@gates.com
101 bill@gates.com
103 bill@gates.com
110 bill@gates.com
13 bill@gates.com
24 bill@gates.com
128 bill@gates.com
132 bill@gates.com
133 bill@gates.com
20 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
ASKER
Those results are actually after a successful update statement. Before I try the SP, I delete some of the BILLTOS email addresses so they would be eligible for updating.
ASKER
In the following tables, I would expect the following rows to be updated:
BILLTOS.BillToID 8, 10, 13, 17
SELECT BillToID, Email FROM PATIENTS where email like '%@%' and relationship = '1' order by billtoid
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ========================== ========== ====
8 bill@gates.com
10 bill@gates.com
13 bill@gates.com
17 bill@gates.com
24 bill@gates.com
39 bill@gates.com
40 bill@gates.com
45 bill@gates.com
56 bill@gates.com
62 bill@gates.com
65 bill@gates.com
84 bill@gates.com
92 bill@gates.com
101 bill@gates.com
103 bill@gates.comm
110 bill@gates.com
128 bill@gates.com
132 bill@gates.com
133 bill@gates.com
20 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
SELECT BillToID, Email FROM BILLTOS
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ========================== ========== ====
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 bill@gates.com
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39 bill@gates.com
40 bill@gates.com
41
42
43
44
45 bill@gates.com
46
47
48
49
50
51
52
53
54
55
56 bill@gates.com
57
58
59
60
61
62 bill@gates.com
63
64
65 bill@gates.com
66
67
68
69
71
72
80
84 bill@gates.com
85
86
88
89
90
92 bill@gates.com
93
94
101 bill@gates.com
102
103 bill@gates.com
104
105
106
107
110 bill@gates.com
111 bill@gates.com
112
113
114
116
117
118
119
120
121
122
123
124
125
126
127
128 bill@gates.com
129
130
131
132 bill@gates.com
133 bill@gates.com
111 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
BILLTOS.BillToID 8, 10, 13, 17
SELECT BillToID, Email FROM PATIENTS where email like '%@%' and relationship = '1' order by billtoid
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ==========================
8 bill@gates.com
10 bill@gates.com
13 bill@gates.com
17 bill@gates.com
24 bill@gates.com
39 bill@gates.com
40 bill@gates.com
45 bill@gates.com
56 bill@gates.com
62 bill@gates.com
65 bill@gates.com
84 bill@gates.com
92 bill@gates.com
101 bill@gates.com
103 bill@gates.comm
110 bill@gates.com
128 bill@gates.com
132 bill@gates.com
133 bill@gates.com
20 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
SELECT BillToID, Email FROM BILLTOS
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ==========================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 bill@gates.com
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39 bill@gates.com
40 bill@gates.com
41
42
43
44
45 bill@gates.com
46
47
48
49
50
51
52
53
54
55
56 bill@gates.com
57
58
59
60
61
62 bill@gates.com
63
64
65 bill@gates.com
66
67
68
69
71
72
80
84 bill@gates.com
85
86
88
89
90
92 bill@gates.com
93
94
101 bill@gates.com
102
103 bill@gates.com
104
105
106
107
110 bill@gates.com
111 bill@gates.com
112
113
114
116
117
118
119
120
121
122
123
124
125
126
127
128 bill@gates.com
129
130
131
132 bill@gates.com
133 bill@gates.com
111 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is just weird isn't it? Here is the Text Output from the various steps. I am clearing all of the BILLTOS.Email values before I start now. I refresh the grid view after each step. The Exceute or call SyncEmail process is leaving them all blank. The UPDATE statement works as expected. I even tired changing the qualifier from null to a specific string and putting that string in one BILLTOS.Email record. it didn't update that either.
<<<<<<<<<<<<<<<<<<<<<<<<
Drop Procedure SyncEmail
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
DECLARE :BillToEmail CHAR(40);
DECLARE :PatientEmail CHAR(40);
DECLARE :BTBilltoID INTEGER;
DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE Email is null FOR UPDATE;
OPEN curs;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
WHILE(SQLSTATE = '00000') DO
IF :BillToEmail is NULL THEN
SET :PatientEMail = NULL;
SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
UPDATE SET Email = :PatientEmail WHERE CURRENT OF curs;
END IF;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
END WHILE;
CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
execute SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
call SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
update BILLTOS
set BILLTOS.Email = PATIENTS.Email
from PATIENTS, BILLTOS
where PATIENTS.BillToID = BILLTOS.BillToID
and PATIENTS.Relationship = '1'
and PATIENTS.Email like '%@%'
and not(BILLTOS.Email like '%@%')
SQL statement(script) has executed successfully.
19 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
Drop Procedure SyncEmail
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
DECLARE :BillToEmail CHAR(40);
DECLARE :PatientEmail CHAR(40);
DECLARE :BTBilltoID INTEGER;
DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE Email is null FOR UPDATE;
OPEN curs;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
WHILE(SQLSTATE = '00000') DO
IF :BillToEmail is NULL THEN
SET :PatientEMail = NULL;
SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
UPDATE SET Email = :PatientEmail WHERE CURRENT OF curs;
END IF;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
END WHILE;
CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
execute SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
call SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
update BILLTOS
set BILLTOS.Email = PATIENTS.Email
from PATIENTS, BILLTOS
where PATIENTS.BillToID = BILLTOS.BillToID
and PATIENTS.Relationship = '1'
and PATIENTS.Email like '%@%'
and not(BILLTOS.Email like '%@%')
SQL statement(script) has executed successfully.
19 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
FYI -- when the query ran correctly, I got a return code of 0, not -1, even if it did not update any records.
Try creating a LOG file with the three fields in it of the appropriate length) and add the INSERT statement back into the SP. Also, I have no way of seeing your table definitions -- so please verify the spelling of all field names, as well as the data types of each field. There might be a minor typo somewhere...
The SP I provided above worked just fine with the following definitions:
Try creating a LOG file with the three fields in it of the appropriate length) and add the INSERT statement back into the SP. Also, I have no way of seeing your table definitions -- so please verify the spelling of all field names, as well as the data types of each field. There might be a minor typo somewhere...
The SP I provided above worked just fine with the following definitions:
CREATE TABLE "BillTos"(
"BillToID" IDENTITY DEFAULT '0',
"Email" CHAR(40),
UNIQUE ("BillToID"));
CREATE TABLE "Patients"(
"BillToID" INTEGER,
"Email" CHAR(40),
"Relationship" CHAR(1));
CREATE TABLE "Log"(
"BilltoId" INTEGER,
"BillEmail" CHAR(40),
"PatientEmail" CHAR(40));
ASKER
Just a quick question that may clarify this. My Tables have "other" columns in them in addidion to the ones I am specifying, does that changes anything?
other than that here are the relevant parts of my tables:
CREATE TABLE "BILLTOS"(
"BillToID" IDENTITY DEFAULT '0',
"Email" CHAR(40),
);
CREATE UNIQUE INDEX "PrimaryKey" ON "BILLTOS"("BillToID");
CREATE TABLE "PATIENTS"(
"PatientID" IDENTITY DEFAULT '0',
"BillToID" UINTEGER,
"Relationship" CHAR(1),
"Email" CHAR(40),
);
CREATE INDEX "BillToKey" ON "PATIENTS"("BillToID", "BirthDate");
CREATE UNIQUE INDEX "PrimaryKey" ON "PATIENTS"("PatientID");
I will try the log fil, thanks
other than that here are the relevant parts of my tables:
CREATE TABLE "BILLTOS"(
"BillToID" IDENTITY DEFAULT '0',
"Email" CHAR(40),
);
CREATE UNIQUE INDEX "PrimaryKey" ON "BILLTOS"("BillToID");
CREATE TABLE "PATIENTS"(
"PatientID" IDENTITY DEFAULT '0',
"BillToID" UINTEGER,
"Relationship" CHAR(1),
"Email" CHAR(40),
);
CREATE INDEX "BillToKey" ON "PATIENTS"("BillToID", "BirthDate");
CREATE UNIQUE INDEX "PrimaryKey" ON "PATIENTS"("PatientID");
I will try the log fil, thanks
ASKER
Log file also empty
<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
DECLARE :BillToEmail CHAR(40);
DECLARE :PatientEmail CHAR(40);
DECLARE :BTBilltoID INTEGER;
DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE Email is null FOR UPDATE;
OPEN curs;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
WHILE(SQLSTATE = '00000') DO
IF :BillToEmail is NULL THEN
SET :PatientEMail = NULL;
SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
INSERT Into Log VALUES (:BTBilltoID, :BillToEmail, :PatientEMail);
UPDATE SET Email = :PatientEmail WHERE CURRENT OF curs;
END IF;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
END WHILE;
CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
execute SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
DECLARE :BillToEmail CHAR(40);
DECLARE :PatientEmail CHAR(40);
DECLARE :BTBilltoID INTEGER;
DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE Email is null FOR UPDATE;
OPEN curs;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
WHILE(SQLSTATE = '00000') DO
IF :BillToEmail is NULL THEN
SET :PatientEMail = NULL;
SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
INSERT Into Log VALUES (:BTBilltoID, :BillToEmail, :PatientEMail);
UPDATE SET Email = :PatientEmail WHERE CURRENT OF curs;
END IF;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
END WHILE;
CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
execute SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
Still getting a -1. Something is wrong with the cursor, I think. Try running this query by itself:
SELECT BillToID, Email FROM BILLTOS WHERE Email is null
Then, try running the second query, too:
SELECT TOP 1 EMail FROM PATIENTS WHERE BillToID = <#> AND Relationship = '1' AND Email like '%@%';
Be sure to replace <#> with a BillToId from your original query.
Additionally, post your version. I tested this (successfully) on PSQLv11.01.
SELECT BillToID, Email FROM BILLTOS WHERE Email is null
Then, try running the second query, too:
SELECT TOP 1 EMail FROM PATIENTS WHERE BillToID = <#> AND Relationship = '1' AND Email like '%@%';
Be sure to replace <#> with a BillToId from your original query.
Additionally, post your version. I tested this (successfully) on PSQLv11.01.
ASKER
Email is definitely blank and not null:
SELECT BillToID, Email FROM BILLTOS WHERE Email = ''; 101 rows
SELECT BillToID, Email FROM BILLTOS WHERE Email is null; 0 rows
SELECT BillToID, Email FROM BILLTOS WHERE Email = ''; 101 rows
SELECT BillToID, Email FROM BILLTOS WHERE Email is null; 0 rows
OK -- change all of these back from "IS NULL" to "= ''", then.
ASKER
SELECT TOP 1 EMail FROM PATIENTS WHERE BillToID = 5 AND Relationship = '1' AND Email like '%@%';
SELECT BillToID, Email FROM BILLTOS WHERE Email = '';
SELECT BillToID, Email FROM BILLTOS WHERE Email is null;
<<<<<<<<<<<<<<<<<<<<<<<<
Email
========================== ========== ====
bill@gates.com
1 row was affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ========================== ========== ====
64
65
66
67
68
69
71
72
80
55
56
57
58
59
60
61
62
63
46
47
48
49
50
51
52
53
54
37
38
39
40
41
42
43
44
45
28
29
30
31
32
33
34
35
36
19
20
21
22
23
24
25
26
27
10
11
12
13
14
15
16
18
1
2
3
4
5
6
7
9
85
86
88
89
90
93
94
102
104
105
106
107
111
112
113
114
116
117
118
119
120
121
122
123
124
125
126
127
129
130
131
101 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ========================== ========== ====
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
SELECT BillToID, Email FROM BILLTOS WHERE Email = '';
SELECT BillToID, Email FROM BILLTOS WHERE Email is null;
<<<<<<<<<<<<<<<<<<<<<<<<
==========================
bill@gates.com
1 row was affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ==========================
64
65
66
67
68
69
71
72
80
55
56
57
58
59
60
61
62
63
46
47
48
49
50
51
52
53
54
37
38
39
40
41
42
43
44
45
28
29
30
31
32
33
34
35
36
19
20
21
22
23
24
25
26
27
10
11
12
13
14
15
16
18
1
2
3
4
5
6
7
9
85
86
88
89
90
93
94
102
104
105
106
107
111
112
113
114
116
117
118
119
120
121
122
123
124
125
126
127
129
130
131
101 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<
BillToID Email
=========== ==========================
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
ASKER
Yep, something in between there, did it....!
This is what I ended up with:
This is what I ended up with:
Drop Procedure TestUpdate;
CREATE PROCEDURE TestUpdate() WITH DEFAULT HANDLER
AS BEGIN
DECLARE :BillToEmail CHAR(40);
DECLARE :PatientEmail CHAR(40);
DECLARE :BTBilltoID INTEGER;
DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE Email = '' FOR UPDATE;
OPEN curs;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
WHILE(SQLSTATE = '00000') DO
IF :BillToEmail = '' THEN
SET :PatientEMail = '';
SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
INSERT Into Log VALUES (:BTBilltoID, :BillToEmail, :PatientEMail);
UPDATE BILLTOS SET Email = :PatientEmail WHERE CURRENT OF curs;
END IF;
FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
END WHILE;
CLOSE curs;
END;
execute TestUpdate();
Empty in that there are no records in the Log table or that there are records but they are empty values?
Take off the "WHERE Email is null" in the original SELECT in your procedure and try again.
Take off the "WHERE Email is null" in the original SELECT in your procedure and try again.
ASKER
Ok, this is weird again, but...if I take out the line:
INSERT Into Log VALUES (:BTBilltoID, :BillToEmail, :PatientEMail);
it fails, put it back in, it works?
INSERT Into Log VALUES (:BTBilltoID, :BillToEmail, :PatientEMail);
it fails, put it back in, it works?
Well that's just nuts! Same thing happens here! Perhaps there's a bug in the engine??? Which version are you running?
ASKER
10.30.017.000
ASKER
I will try it on the client with 9.71 when I get it tested on a bigger data set. For now I will just add the Log file delete it when It is finished. Thanks.
UPDATE TABLENAME SET Email = :PatientEmail WHERE CURRENT OF curs;