PC Paul wrote:
> soup_or_power_at_yahoo.com wrote:
>
>>DA Morgan wrote:
>>
>>>soup_or_power_at_yahoo.com wrote:
>>>
>>>>Can someone please explain why ORA-01722 occurs in the following sql
>>>>near s.C42=115504? s.C42 is a number in the database. Many thanks!
>>>>
>
> <snip>
>
>>Daniel
>>I am posting the edited sql. Most of the sql is checking for the
>>domain names based on country codes. You can call it a bad schema but
>>it is inevitable given the prolific list of country codes. Here is a
>>brief sql that has the problem at s.C42=1155104. Thank you!
>>
>>INSERT INTO sendq_tbl ( profile_id, slist_id, block_id )
>>SELECT s.profile_id, 58492488, 0 FROM subscriber_tbl s, sub_report_tbl
>>srt WHERE s.plist_id = 16131237
>>AND srt.profile_id = s.profile_id AND srt.plist_id = 16131237 AND
>>((s.C11 LIKE '%PROSPECT%' OR s.C11 LIKE '%UNKNOWN%' OR s.C11 IS NULL
>>OR s.C11 = 'PARTNERSHIPS' OR s.C11 =
>>'MAJOR DONORS' OR s.C11 = 'SPONSORS' OR s.C11 = 'PLEDGERS' OR s.C11 =
>>'SINGLEGIFT' OR
>>s.C11 = 'ORGANIZATIONS') AND NOT (s.C36 = '20050408_KINTERA_GT5000'
>>AND s.C36 IS NOT NULL) AND NOT
>>(s.C36 IN
>>('20050303_TSUNAMI_DV_NEW_DONORS','20050105_KINTERA_NEWDONOR') AND
>>s.C36 IS NOT NULL) AND NOT
>>(s.PROFILE_KEY LIKE ...) )
>>AND s.C42 = 1155104) AND (srt.NUM_OPTOUT = 0 AND srt.NUM_FTD <= 3) AND
>>s.block_id = '0')
>
>
> Much nicer!!
>
> Can't help with the immediate problem, *but* is there really no way of
> having the country codes in a table and using them from there instead?
My thought too. Far too much hard coding going on.
What would be really helpful soup would be if you did one more post that
was a single post containing all of the following:
- Oracle edition and version
- DDL that creates the table sendq_tbl
- DDL that creates the table subscriber_tbl
- DDL that creates the table_report_tbl
- The shorter version of the SQL statement (above)
that creates the exception.
- The full and complete error message you are getting: Not just the number.
Then all I can promise is that I'll see if I can reproduce it here in
the lab.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Jul 18 2005 - 12:29:21 CDT
