decode pl/sql

  • 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

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

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

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

  • what does this do: nvl(org.effective_end_date,sysdate)?

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

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

  • error is still

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type nvarchar to float.

  • paul.starr (11/4/2008)


    error is still

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type nvarchar to float.

    After running mine?

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • yes

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

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • still error:

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type nvarchar to float.

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

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

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

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply