October 31, 2008 at 11:21 am
how would i decode this in tsql
select /*+ RULE */
decode(orig.orig_system,null,'ORACLE','UNKNOWN','ORACLE',orig.orig_system)||'|'||nvl(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||'|'||to_char(ca.account_established_date,'MM/DD/YYYY')||'|'||
org.known_as||'|'||ca.attribute3
October 31, 2008 at 12:50 pm
The || operator is string concat, so replace with + in SQL.
Function nvl() is replaced with IsNull() (or Coalesce() )
To_Char is replaced with convert() and 1 of the MSSQL date format codes
Decode() is replaced with a CASE .. WHEN .. ELSE construct:
eg:
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) + '|' +
etc
November 3, 2008 at 9:18 am
does this pl/sql equal this tsql, it is giving me errors when i run it.
PL/SQL
select /*+ RULE */
decode(orig.orig_system,null,'ORACLE','UNKNOWN','ORACLE',orig.orig_system)||'|'||nvl(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||'|'||to_char(ca.account_established_date,'MM/DD/YYYY')||'|'||
org.known_as||'|'||ca.attribute3
TSQL
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(ca.account_established_date,'MM/DD/YYYY')
+ '|' + org.known_as + '|' + ca.attribute3
the error is Msg 243, Level 16, State 1, Line 2
Type ca.account_established_date is not a defined system type.
November 3, 2008 at 10:05 am
paul.starr (11/3/2008)
does this pl/sql equal this tsql, it is giving me errors when i run it.PL/SQL
select /*+ RULE */
decode(orig.orig_system,null,'ORACLE','UNKNOWN','ORACLE',orig.orig_system)||'|'||nvl(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||'|'||to_char(ca.account_established_date,'MM/DD/YYYY')||'|'||
org.known_as||'|'||ca.attribute3
TSQL
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(ca.account_established_date,'MM/DD/YYYY')
+ '|' + org.known_as + '|' + ca.attribute3
the error is Msg 243, Level 16, State 1, Line 2
Type ca.account_established_date is not a defined system type.
Your convert should look like this: convert(varchar(10), ca.account_established_date, 101)
November 3, 2008 at 10:10 am
this is the error i got now:
Msg 8114, Level 16, State 5, Line 2
Error converting data type nvarchar to float.
November 3, 2008 at 10:19 am
Okay, That's what happens when you make a guess in the dark. What is the data type oc ca.account_established_date? Actually, what are the data types for all the columns involved in this query, as it may not be just ca.account_established_date that is causing a problem.
November 3, 2008 at 10:31 am
Here is the entire query:
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_CUST_ACCOUNTS CA, Oracle11i..AR.HZ_PARTIES HP, Oracle11i..AR.HZ_ORGANIZATION_PROFILES ORG,
Oracle11i..AR.HZ_ORIG_SYS_REFERENCES ORIG, Oracle11i..AR.HZ_CUST_ACCT_SITES_ALL SI
where hp.party_id = ca.party_id
and str(ca.attribute10,9,3) = '086'
and ca.cust_account_id = si.cust_account_id
and si.bill_to_flag in ('P','Y')
and org.party_id = hp.party_id
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.owner_table_id = ca.cust_account_id
and (orig.orig_system is null or orig.orig_system not in ('ALUMINUM'))
order by ca.account_number, ca.attribute10;
November 3, 2008 at 10:36 am
Well, having the entire query is good, but we still need to know the data types of the columns since the problem you are having is a conversion problem.
November 3, 2008 at 10:50 am
with the query i gave you, i am converting from pl/sql into tsql
here is an output line from the pl/sql
ORACLE | 93596 | 68498 | 3089512-086 | MARTIN POWER SOLUTIONS | A | D1 | NC1 | ||
does this help with the data type question??
November 3, 2008 at 10:55 am
Not really. I need to know what are the data types of the columns referenced from each of the tables used in the query.
Please take a few minutes to read the article referenced below, it should help you help us help you (say that three times fiast :D).
November 3, 2008 at 2:15 pm
here are the data types:
ORIG_SYSTEM VARCHAR2(30)
ORIG_SYSTEM_REFERENCE VARCHAR2(255)
ACCOUNT_NUMBER VARCHAR2(30)
ATTRIBUTE10 VARCHAR2(150)
PARTY_NAME VARCHAR2(360)
STATUS VARCHAR2(1)
CATEGORY_CODE VARCHAR2(30)
ACCOUNT_ESTABLISHED_DATE DATE
CUSTOMER_CLASS_CODE VARCHAR2(30)
November 3, 2008 at 2:21 pm
here are the datatypes:
ORIG_SYSTEM VARCHAR2(30)
ORIG_SYSTEM_REFERENCE VARCHAR2(255)
ACCOUNT_NUMBER VARCHAR2(30)
ATTRIBUTE10 VARCHAR2(150)
PARTY_NAME VARCHAR2(360)
STATUS VARCHAR2(1)
CATEGORY_CODE VARCHAR2(30)
ACCOUNT_ESTABLISHED_DATE DATE
CUSTOMER_CLASS_CODE VARCHAR2(30)
November 3, 2008 at 3:41 pm
Missing the data types for org.known_as and ca.attribute3.
November 3, 2008 at 3:45 pm
ATTRIBUTE3 VARCHAR2(150)
KNOWN_AS VARCHAR2(240)
November 3, 2008 at 3:53 pm
Something is missing somewhere. I don't see anything that would cause this conversion error: Error converting data type nvarchar to float. The only non-character data type is ca.account_established_date, and that is in properly created convert statement for SQL Server 2005.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply