May 5, 2010 at 7:51 am
so you are not going to provide the CREATE TABLE definition of v_accounts_info_table?
you keep posting variations of the same oracle function over and over instead of providing the information we requested. stop that. just provide what we ask if you want help.
GilaMonster (5/4/2010)
If you want someone to help you rewrite it, you're going to have to give us a lot more. Starting with table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.http://www.sqlservercentral.com/articles/Best+Practices/61537/
you never answer the questions or provide the information we requested. without the tables existing on the SQL server, you can't make functions or procedures that reference those tables.
I don't think you'll get the answers you are looking for without hiring someone to transfer all your objects to SQL in the first place, let alone converting your oracle packages to procs and functions in SQL.
Lowell
May 5, 2010 at 7:54 am
b.noof (5/5/2010)
Sure i want your helpwhat i have to do ??
i give the function & the table
I've asked twice already for the table definition (which I still don't see), some sample data for that table and the output that the function is supposed to produce. I haven't worked with Oracle for 10 years, I'm not familiar enough with it's syntax to work out, from the Oracle code, what this is supposed to do.
Please read the link that I initially provided and give us the information we need to help you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2010 at 7:57 am
v_accounts_info_table is just a varchar
i use this for develloping my final project
and the function is develloped for card data table
May 5, 2010 at 8:06 am
There's a difference between @v_accounts_info_table, which is defined as a VARCHAR(4000) and v_accounts_info_table, which is a table you are selecting from, updating and inserting into
Table definitions please, of all tables that this function uses.
Sample data please. What's the starting data that this function uses? Both in the tables and the parameters that it'll be called with
Expected results please. What should the data in the tables look like once the function has run and the value that the function returns.
A description of what this is supposed to do would also be useful.
Also, as Lowell has noted, this will have to become a Procedure, not a Function, as SQL does not allow insert or update within a function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2010 at 8:13 am
this the definition of card data which usedd the function :
CREATE TABLE Card_data (
Card_number VARCHAR(24) NOT NULL AUTO_INCREMENT,
Bank-code CHAR(5) NOT NULL,
Branch-code CHAR(5) NOT NULL,
City_code CHAR(5) NOT NULL,
Country_code CHAR(3) NOT NULL,
First_name VARCHAR(30) NULL,
Last_name VARCHAR(30) NULL,
Expiry_date DATETIME NULL,
Product CHAR(3) NULL,
Status_card CHAR(1) NULL,
Status_date DATETIME(7) NULL,
Network CHAR(2) NULL,
Branch_code CHAR(5) NULL,
Bank_code CHAR(5) NULL,
Accounts_list VARCHAR(1080) NULL,
Delivery_action CHAR(1) NULL,
Delivery_date DATETIME(7) NULL,
Activation_action CHAR(1) NULL,
Activation_date DATETIME(7) NULL,
Renewal_date DATETIME(7) NULL,
Renewal_delivery_date DATETIME(7) NULL,
Address_1 VARCHAR(120) NULL,
Type_address_1 CHAR(1) NULL,
City_code_1 VARCHAR(5) NULL,
Country_code_1 CHAR(3) NULL,
Using_renewed_card CHAR(1) NULL,
PRIMARY KEY(Card_number));
the function have to create account list but 1 account is a varcharoh 54
May 5, 2010 at 8:18 am
Where is that used in the function? The only table I can see you reference in the function anywhere is v_accounts_info_table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2010 at 9:01 am
it's just a variable and not a table
May 5, 2010 at 9:07 am
b.noof (5/5/2010)
it's just a variable and not a table
no it's not. variables do not have row indexs or columns.
your multiple quotes demonstrate it over and over again:
v_accounts_info_table(index).account_sequence for example
v_accounts_info_table(v_account_id).account_sequence := v_account_id;
v_offset := v_offset + 2.;
v_accounts_info_table(v_account_id).account_number := SUBSTR(p_card_accounts,v_offset,24.);
v_offset := v_offset + 24.;
v_accounts_info_table(v_account_id).account_priority := SUBSTR(p_card_accounts,v_offset,2.);
p_application_traces_type.line_number
Lowell
May 5, 2010 at 9:08 am
How can it not be a table? You're referencing it in INSERT, UPDATE and SELECT statements
From the function that you posted:
INSERT INTO v_accounts_info_table( tempIdx , account_sequence ) VALUES (@v_account_id, @v_account_id)
SELECT @v_offset = @v_offset + 2
IF EXISTS (SELECT * FROM v_accounts_info_table WHERE tempIdx=@v_account_id)
UPDATE v_accounts_info_table SET account_number = @p_card_accounts WHERE tempIdx = @v_account_id
ELSE
INSERT INTO v_accounts_info_table( tempIdx , account_number ) VALUES (@v_account_id, SUBSTRING(@p_card_accounts, @v_offset, 24))
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2010 at 9:51 am
In Oracle it's a structure
but after i migrate from oracle to sqlserver it come a like u see it
i do the migrate with swiss its a soft
so if u have any thing to correct u welcome 😉
ty
May 7, 2010 at 1:08 pm
let me guess a variable? a table variable?
May 7, 2010 at 1:26 pm
Halcyon (5/7/2010)
let me guess a variable? a table variable?
No. It's a real table. A table variable would be prefixed with an @ sign. This table isn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 1:29 pm
b.noof (5/5/2010)
In Oracle it's a structurebut after i migrate from oracle to sqlserver it come a like u see it
i do the migrate with swiss its a soft
Ok, so it was a structure. Whatever conversion tool you used appears to have converted it to a table. Please look through the database schema and find what it's definition is.
NB. I am not talking about the variable @v_accounts_info_table which is defined in this function as a VARCHAR(4000).
We're also still waiting for sample data for the tables and the expected results of this function (and, if possible, an explanation of what it does)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 2:45 pm
b.noof (5/5/2010)
In Oracle it's a structurebut after i migrate from oracle to sqlserver it come a like u see it
i do the migrate with swiss its a soft
so if u have any thing to correct u welcome 😉
ty
J'ai vu qu vous avez repondu en francais deja. Est-ce ca fait plus facile pour vous en francais? Si oui, peut-etre je peut faire de traduction pour vous?
Nous en avons besoin de source pour vos tables. Si vous lisez le code que vous avez pourvu, vous verrez que les "Inserts" sont fait dans des tables et pas dans des variables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 10, 2010 at 6:26 am
It would be MUCH BETTER if you consult the BOL for the errors / exceptions in the code.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply