decode pl/sql

  • 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

  • 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

  • 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.

  • 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)

  • this is the error i got now:

    Msg 8114, Level 16, State 5, Line 2

    Error converting data type nvarchar to float.

  • 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.

  • 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;

  • 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.

  • 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??

  • 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).

  • 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)

  • 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)

  • Missing the data types for org.known_as and ca.attribute3.

  • ATTRIBUTE3 VARCHAR2(150)

    KNOWN_AS VARCHAR2(240)

  • 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