SQL command not properly ended

[fatherofthree]

Below is some update code I created to update a field with one of two values; either T.LOCKIN_EXPIRES or T.LOCKIN_EXPIRES + 9

I keep get the error SQL command not properly ended and I think it is because I am missing a parenthesis but when I put in a parenthesis where I think it is needed I then get the error SQL missing right parenthesis.

What am I missing??

<code>

UPDATE XREF2 SET XREF2.INVESTOR_SUSPEND = (SELECT (T.LOCKIN_EXPIRES + 9) FROM T WHERE T.LOCKIN_EXPIRES IS NOT NULL AND T.ORIG_TYPE IN('B','C','W') AND T.LOCKIN_DATE >= '23-SEP-03' AND T.AP_NUM IN(SELECT XREF.AP_NUM FROM XREF WHERE XREF.BROKER = 'INTEGRITY MTG') AND T.AP_NUM LIKE XREF2.AP_NUM)
OR
(SELECT T.LOCKIN_EXPIRES FROM T WHERE (T.LOCKIN_EXPIRES IS NOT NULL AND T.ORIG_TYPE IN('B','C','W') AND T.AP_NUM IN(SELECT XREF.AP_NUM FROM XREF WHERE XREF.BROKER != 'INTEGRITY MTG') AND T.AP_NUM LIKE XREF2.AP_NUM) OR (T.LOCKIN_EXPIRES IS NOT NULL AND T.ORIG_TYPE IN('B','C','W') AND T.LOCKIN_DATE < '23-SEP-03' AND T.AP_NUM IN(SELECT XREF.AP_NUM FROM XREF WHERE XREF.BROKER = 'INTEGRITY MTG') AND T.AP_NUM LIKE XREF2.AP_NUM)
</code>


Thanks

Chad

 

[hubud]

First thought, is T.LOCKIN_DATE a varchar or a date?

If it's a date you may need to run:

LOCKIN_DATE >= to_date('23-SEP-03', 'dd-mon'yy').

Same goes for T.LOCKIN_DATE < '23-SEP-03'

Hope that helps a bit.



cheers

simmo

 

[fatherofthree]

T.LOCKIN_DATE is a date/time field. I tried using the TO_DATE function and it did not resolve the error.

The error did not start until I added the second update value. Meaning, SET XREF2.INVESTOR_SUSPEND = (SELECT (T.LOCKIN_EXPIRES + 9)) OR (SELECT T.LOCKIN_EXPIRES).

If I take the second set value off the update query, it works fine but trying to set the field value equal to one or the other is where I seem to be running into the problem. This is possible to do correct?

Thanks

Chad

 

[SantaMufasa]

Chad (FatherOfThree),

In addition to Hubud's excellent suggestion [of converting string-literal dates into true DATE expressions], you have an additional code problem:

Your code is trying to bestow upon Oracle something that God bestowed only upon thinking beings: FREE CHOICE. Your code is basically saying &quot;UPDATE a column from EITHER source-A [bold]OR[/bold] from source-B, and you (Oracle) choose which source&quot;. Oracle gets confused with that much freedom.

I propose that to avoid getting tangled up in your underwear on this, that you create a user-defined FUNCTION called &quot;GET_EXPIRY(...)&quot; that returns the proper LOCKIN_EXPIRES value. Your UPDATE code could then read:
&quot;UPDATE XREF2 SET XREF2.INVESTOR_SUSPEND = GET_EXPIRY (...);&quot;

This way, all the logic complexity of your UPDATE statement appears in your FUNCTION definition and you have the latitude to use the &quot;IF..THEN..ELSE&quot;/&quot;CASE..WHEN..ELSE&quot; logic that your current code is screaming for.

If you do not know how to write an Oracle user-defined function to do what your need, then please advise and one will magically appear here.

Dave
Sandy, Utah, USA @ 17:54 GMT, 10:54 Mountain Time

 

[fatherofthree]

Dave (SantaMufasa)

I have never written an Oracle user defined function. I am new to Oracle code. I would be most appreciative for an example.

Thanks

Chad

 

[SantaMufasa]

Chad (FatherOfThree),

While writing a user-defined function for you, I discovered what may be the cause of your original problem: Your parentheses don't match. Below, I show your original code, visually re-aligned to show execution grouping, plus the addition of &quot;--&quot; commenting to show your paren matching (and lack thereof). Notice that open paren &quot;(&quot; #5 has no matching close paren. [This could be from an extraneous &quot&quot; #6, but that is only a guess on my part.] Please confirm the logic you REALLY wanted, then I can write your user-defined function. Here is your original code, re-aligned and commented:

UPDATE XREF2 SET XREF2.INVESTOR_SUSPEND =
	(SELECT (T.LOCKIN_EXPIRES + 9) -- "(" #1, "(" #2, ")" #2
		FROM T
		WHERE T.LOCKIN_EXPIRES IS NOT NULL
		  AND T.ORIG_TYPE IN('B','C','W') -- "(" #3, ")" #3
		  AND T.LOCKIN_DATE >= '23-SEP-03'
		  AND T.AP_NUM
			IN(SELECT XREF.AP_NUM -- "(" #4
				FROM XREF
				WHERE XREF.BROKER = 'INTEGRITY MTG') -- ")" #4
		  AND T.AP_NUM LIKE XREF2.AP_NUM) -- ")" #1
	OR
	(SELECT T.LOCKIN_EXPIRES -- &quot;(&quot; #5 <-- no matching &quot;)&quot;
		FROM T
		WHERE (T.LOCKIN_EXPIRES IS NOT NULL -- &quot;(&quot; #6
			AND T.ORIG_TYPE IN('B','C','W') -- &quot;(&quot; #7, &quot;)&quot; #7
			AND T.AP_NUM
				IN(SELECT XREF.AP_NUM -- &quot;(&quot; #8
					FROM XREF
					WHERE XREF.BROKER != 'INTEGRITY MTG') -- &quot;)&quot; #8
			AND T.AP_NUM LIKE XREF2.AP_NUM) -- &quot;)&quot; #6
	OR (T.LOCKIN_EXPIRES IS NOT NULL -- &quot;(&quot; #9
		AND T.ORIG_TYPE IN('B','C','W') -- &quot;(&quot; #10, &quot;)&quot; #10
		AND T.LOCKIN_DATE < '23-SEP-03'
		AND T.AP_NUM
			IN(SELECT XREF.AP_NUM -- &quot;(&quot; #11
				FROM XREF
				WHERE XREF.BROKER = 'INTEGRITY MTG') -- &quot;)&quot; #11
		AND T.AP_NUM LIKE XREF2.AP_NUM); -- &quot;)&quot; #9

Dave (FatherOfSix, GrandfatherOfSix)
Sandy, Utah, USA @ 20:51, 13:51 Mountain Time

 

[fatherofthree]

Dave (SantaMufasa,FatherOfSix,GrandFatherOfSix)

The #5 &quot&quot; should come after the closing #9 &quot&quot;


Thanks

Chad

 

[SantaMufasa]

Chad,

I must run to a 19:00 (Mountain) meeting. I'll work on this again following my return.

Dave

 

[SantaMufasa]

Chad,

(Sorry, the meeting ran late.) Following is your user-defined function, GET_EXPIRY. Following the function definition is the revised UPDATE statement that accesses the function. Invocation of the function expects an &quot;AP_NUM&quot; as the incoming argument.

The functional narrative for the function is:
1) Determine how many rows meet your First WHERE criteria.
2) Determine how many rows meet your Second WHERE criteria.
3) If FirstWhere = 1 and SecondWhere = 0, RETURN T.LOCKIN_EXPIRES + 9;
If FirstWhere = 0 and SecondWhere = 1, RETURN T.LOCKIN_EXPIRES;
If any other result, return NULL;

Here are the function definition and UPDATE statement:

create or replace function get_expiry (ap_num_arg in varchar2) return date is
	Select_cnt1	number;
	select_cnt2	number;
	date_hold	date;
begin
	select count(*) into select_cnt1
		FROM T
		WHERE T.LOCKIN_EXPIRES IS NOT NULL
		  AND T.ORIG_TYPE IN('B','C','W')
		  AND T.LOCKIN_DATE >= to_date('23-SEP-03','dd-mon-rr')
		  AND T.AP_NUM
			IN(SELECT XREF.AP_NUM
				FROM XREF
				WHERE XREF.BROKER = 'INTEGRITY MTG')
		  AND T.AP_NUM LIKE ap_num_arg;
	select count(*) into select_cnt2
		FROM T
		WHERE (T.LOCKIN_EXPIRES IS NOT NULL
			AND T.ORIG_TYPE IN('B','C','W')
			AND T.AP_NUM
				IN(SELECT XREF.AP_NUM
					FROM XREF
					WHERE XREF.BROKER != 'INTEGRITY MTG')
			AND T.AP_NUM LIKE ap_num_arg)
			OR (T.LOCKIN_EXPIRES IS NOT NULL
				AND T.ORIG_TYPE IN('B','C','W')
				AND T.LOCKIN_DATE < to_date('23-SEP-03','dd-MON-rr')
				AND T.AP_NUM
					IN(SELECT XREF.AP_NUM
						FROM XREF
						WHERE XREF.BROKER = 'INTEGRITY MTG')
				AND T.AP_NUM LIKE ap_num_arg);
	if select_cnt1 = 1 and select_cnt2 = 0 then
		SELECT (T.LOCKIN_EXPIRES + 9) into date_hold
			FROM T
			WHERE T.LOCKIN_EXPIRES IS NOT NULL
			  AND T.ORIG_TYPE IN('B','C','W')
			  AND T.LOCKIN_DATE >= to_date('23-SEP-03','dd-mon-rr')
			  AND T.AP_NUM
				IN(SELECT XREF.AP_NUM
					FROM XREF
					WHERE XREF.BROKER = 'INTEGRITY MTG')
			  AND T.AP_NUM LIKE ap_num_arg;
	elsif select_cnt1 = 0 and select_cnt2 = 1 then
		SELECT T.LOCKIN_EXPIRES into date_hold
			FROM T
			WHERE (T.LOCKIN_EXPIRES IS NOT NULL
				AND T.ORIG_TYPE IN('B','C','W')
				AND T.AP_NUM
					IN(SELECT XREF.AP_NUM
						FROM XREF
						WHERE XREF.BROKER != 'INTEGRITY MTG')
				AND T.AP_NUM LIKE ap_num_arg)
				OR (T.LOCKIN_EXPIRES IS NOT NULL
					AND T.ORIG_TYPE IN('B','C','W')
					AND T.LOCKIN_DATE < to_date('23-SEP-03','dd-MON-rr')
					AND T.AP_NUM
						IN(SELECT XREF.AP_NUM
							FROM XREF
							WHERE XREF.BROKER = 'INTEGRITY MTG')
					AND T.AP_NUM LIKE ap_num_arg);
	else
		date_hold := null;
	end if;
	return date_hold;
end;
/
Update xref2 set investor_suspend = get_expiry(ap_num);

Let me know how things go.

Dave
Sandy, Utah, USA @ 06:37 GMT, 11:37 Mountain Time

 

[fatherofthree]

Dave(SantaMufasa)

Thank you very much. It seems to work. I am in the process of fully testing it out now.

Thanks

Chad