November 4, 2008 at 9:51 am
same error:
where
CAST(RIGHT(ca.attribute10,3) as varchar(3)) = '086'
--and si.bill_to_flag in ('P','Y')
--and IsNull(org.status,'A') ='A'
--and between GetDate()org.effective_start_date and GetDate() org.effective_end_date
--and orig.owner_table_name ='HZ_CUST_ACCOUNTS'
--and (orig.orig_system is null or orig.orig_system not in ('ALUMINUM'))
--order by
--ca.account_number, ca.attribute10;
November 4, 2008 at 9:52 am
Good catch on str. I kept seeing it and ignoring it also.
November 4, 2008 at 10:00 am
I can't see how that code would give you that error. This works fine:
DECLARE @b-2 nvarchar(50)
SET @b-2 = 'afeakfe;a-086'
SELECT CASE WHEN CAST(RIGHT(@b,3) as varchar(3)) = '086' THEN 1 ELSE 0 END
SELECT CASE WHEN RIGHT(@b,3) = '086' THEN 1 ELSE 0 END
The cast should be unnecessary, I just threw it in there for good measure. Is there any chance you had your original code in the window and it executed both?
For that matter... eliminating the WHERE clause earlier shouldn't have given you the same error either. Are you aware that in MS SQL(Both SSMS and QA), if you only want to run part of what is in the window, you need to highlight just that section before clicking run?
November 4, 2008 at 10:08 am
when i run only this portion of the code i still get the same error:
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
SELECT 1
--CASE
--WHEN orig.orig_system is null or orig.orig_system = 'UNKNOWN'
-- THEN 'ORACLE'
--ELSE orig.orig_system
--END, -- + '|' +
--IsNull(orig.orig_system_reference,ca.orig_system_reference), -- + '|' +
--ca.account_number, -- + '|' +
--ca.attribute10, -- + '|' +
--hp.party_name, -- + '|' +
--ca.status, -- + '|' +
--hp.category_code, -- + '|' +
--ca.customer_class_code, -- + '|' +
--convert(varchar(10), ca.account_established_date, 101), -- + '|' +
--org.known_as, -- + '|' +
--ca.attribute3
from
Oracle11i..AR.HZ_ORGANIZATION_PROFILES ORG
INNER JOIN Oracle11i..AR.HZ_PARTIES HP ON CAST(org.party_id as nvarchar(50)) = CAST(hp.party_id as nvarchar(50))
INNER JOIN Oracle11i..AR.HZ_CUST_ACCOUNTS CA ON CAST(hp.party_id as nvarchar(50)) = CAST(ca.party_id as nvarchar(50))
INNER JOIN Oracle11i..AR.HZ_ORIG_SYS_REFERENCES ORIG ON CAST(orig.owner_table_id as nvarchar(50)) = CAST(ca.cust_account_id as nvarchar(50))
INNER JOIN Oracle11i..AR.HZ_CUST_ACCT_SITES_ALL SI ON CAST(ca.cust_account_id as nvarchar(50)) = CAST(si.cust_account_id as nvarchar(50))
November 4, 2008 at 10:16 am
The base idea here is to boil this query down to its base elements and figure out everything that's causing issues. At this point, you'd start commenting out joins one at a time until you found out which of those was a problem etc. etc. As you fix issues, you add things back in. Beyond that, I'm out of ideas. I see nothing in that query that (based on the information you're providing, and assuming that is the only thing being run) would cause the error you are getting.
That said, like Lynn, I have no experience in Oracle, and it seems that may be what's needed at this point.
November 4, 2008 at 10:22 am
I have to second Seth on the approach. Start by writing your query simply as a select query, starting with the main table first and all the data elements needed from it. Then add one table and the required elements from it at a time until you have an error.
From that, maybe we can help you further.
Also, maybe there is a lurker out there watching this thread who has some Oracle experience and will jump in finally and help out.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply