Re: ORA-01722

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? Received on Mon Jul 18 2005 - 11:54:52 CDT