November 3, 2008 at 4:06 pm
maybe its this line i have commented out:
how would i write this line:
--and between GetDate()org.effective_start_date and GetDate() org.effective_end_date
data types
EFFECTIVE_START_DATE NOT NULL DATE
EFFECTIVE_END_DATE DATE
November 3, 2008 at 4:07 pm
As you are converting from pl/sql to t-sql, is it safe to say you are also porting the database from oracle to sql server? You provided the data types based on on oracle, are the data types different in sql server if you are porting?
November 3, 2008 at 4:09 pm
paul.starr (11/3/2008)
maybe its this line i have commented out:how would i write this line:
--and between GetDate()org.effective_start_date and GetDate() org.effective_end_date
data types
EFFECTIVE_START_DATE NOT NULL DATE
EFFECTIVE_END_DATE DATE
What exactly does this line do in oracle? I have ZERO oracle experience, other than being able to spell it.
November 3, 2008 at 4:15 pm
this is the original line in oracle
and sysdate between org.effective_start_date and nvl(org.effective_end_date,sysdate)
i believe it helps with the query in selecting the dates that match the order number.
for example, if the order number ends in '086' then it selects those dates that have those order numbers.
i just received the script i sent you earlier and was told i had to convert the pl/sql script to tsql.
November 3, 2008 at 4:20 pm
what does this do: nvl(org.effective_end_date,sysdate)?
November 3, 2008 at 4:22 pm
Does this run? (I converted your query to use current JOIN syntax... hopefully I didn't mess it up. If I did, replace the FROM and WHERE with your originals and try it)
If it does run, go through and remove the commas and uncomment out the + '|' + at the end of each line one at a time until you get the error again. When you do, you know what line it is.
SELECT
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 org.party_id = hp.party_id
INNER JOIN Oracle11i..AR.HZ_CUST_ACCOUNTS CA ON hp.party_id = ca.party_id
INNER JOIN Oracle11i..AR.HZ_ORIG_SYS_REFERENCES ORIG ON orig.owner_table_id = ca.cust_account_id
INNER JOIN Oracle11i..AR.HZ_CUST_ACCT_SITES_ALL SI ON ca.cust_account_id = si.cust_account_id
where
str(ca.attribute10,9,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 3, 2008 at 4:30 pm
Making a guess here about nvl...
This:
sysdate between org.effective_start_date and nvl(org.effective_end_date,sysdate)
would be this:
getdate() between org.effective_start_date and isnull(org.effective_end_date, getdate())
November 4, 2008 at 8:43 am
error is still
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.
November 4, 2008 at 8:51 am
paul.starr (11/4/2008)
error is stillMsg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.
After running mine?
November 4, 2008 at 8:53 am
yes
November 4, 2008 at 8:57 am
Ok, continuing to try to isolate the error... try this one:
SELECT TOP 100
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 org.party_id = hp.party_id
INNER JOIN Oracle11i..AR.HZ_CUST_ACCOUNTS CA ON hp.party_id = ca.party_id
INNER JOIN Oracle11i..AR.HZ_ORIG_SYS_REFERENCES ORIG ON orig.owner_table_id = ca.cust_account_id
INNER JOIN Oracle11i..AR.HZ_CUST_ACCT_SITES_ALL SI ON ca.cust_account_id = si.cust_account_id
And if that one fails...
SELECT
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 varchar(50)) = CAST(hp.party_id as varchar(50))
INNER JOIN Oracle11i..AR.HZ_CUST_ACCOUNTS CA ON CAST(hp.party_id as varchar(50)) = CAST(ca.party_id as varchar(50))
INNER JOIN Oracle11i..AR.HZ_ORIG_SYS_REFERENCES ORIG ON CAST(orig.owner_table_id as varchar(50)) = CAST(ca.cust_account_id as varchar(50))
INNER JOIN Oracle11i..AR.HZ_CUST_ACCT_SITES_ALL SI ON CAST(ca.cust_account_id as varchar(50)) = CAST(si.cust_account_id as varchar(50))
where
str(ca.attribute10,9,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:27 am
still error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.
November 4, 2008 at 9:34 am
Ok, starting all the way at square 1. This *should* run.
If it does run, begin by uncommenting (removing the --'s) from the where criteria one line at a time. If it works with the entire where uncommented, start back over and do the same with the select. If at any point it starts failing again, it should give us a better idea where to look.
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))
where
--str(ca.attribute10,9,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:42 am
When i start at the first line the error is:
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric"
where
str(ca.attribute10,9,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:48 am
I knew something didn't look right with that STR function this whole time. I kept looking at it and ignoring it, then looking at it and ignoring it. You need to use SUBTRING, not STR. Try this:
CAST(RIGHT(ca.attribute10,3) as varchar(3)) = '086' instead.
This is a smack in the head moment. BOL for STR function:
STR
Returns character data converted from numeric data.
Syntax
STR ( float_expression [ , length [ , decimal ] ] )
Arguments
float_expression
Is an expression of approximate numeric (float) data type with a decimal point.
length
Is the total length, including decimal point, sign, digits, and spaces. The default is 10.
decimal
Is the number of places to the right of the decimal point.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply