Home » SQL & PL/SQL » SQL & PL/SQL » migrate View from SQL to Oracle (11.g)
migrate View from SQL to Oracle [message #642075] Wed, 02 September 2015 04:07 Go to next message
oracle1987
Messages: 3
Registered: September 2015
Junior Member
Hello!
I try to migrate a view from SQL-Server to Oracle in SQL Server the where-Statement is:

SQL:
...
and (patindex('%!'+rtrim(LOANR)+'!%',replace(replace(isnull('[xxx1],[9004]',0),'[','!'),']','!')) > 0)


in Oracle I try:

and REGEXP_INSTR('[xxx1],[9004]', '[' || LOANR || ']', 1, 1, 0, 'i') > 0

Can anyone help me?

Under oracle i get too mutch results with all wagetypes (loanr).

The String '[xxx1],[9004]' is the list for the restriction of the wagetypes.


LOANR is the column in the Table. In this column there is just one wagetype for example 9004 without the character "[" and "]".

The restriction list is delivered from another table in the format "[val1],[val2],[val3]".
For testing I use it directly in the view as string ('[xxx1],[9004]').

Regards Wink

[Updated on: Wed, 02 September 2015 04:17]

Report message to a moderator

Re: migrate View from SQL to Oracle [message #642076 is a reply to message #642075] Wed, 02 September 2015 04:42 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't speak SQL Server so I can't just translate A to B. However, if you could describe (with words) conditions that have to be met in order to create that view's WHERE clause, that might help.

Report message to a moderator

Re: migrate View from SQL to Oracle [message #642078 is a reply to message #642076] Wed, 02 September 2015 04:52 Go to previous messageGo to next message
oracle1987
Messages: 3
Registered: September 2015
Junior Member
hello thanks for your reply.

I try to describe you the requirement for this where clause.

The user can set the restriction of the wagetypes (-> the parameter list).
This restrictions are in the table A

A.RESTRICTWAGETYPES (VARCHAR2) / just this one line exists!:
[4001],[4002],[4003]

In the table B there are the variable data.
B.LOANR (VARCHAR2) | A.DATE (NUMBER) | B.USER (VARCHAR2)
4001 | 20150903 | sschmidt
4003 | 20150904 | sschmidt
4004 | 20150905 | sschmidt
9009 | 20150905 | sschmidt
...

Now the where clause must be deliver only the variable data from the table B where the field B.LOANR exists in the restrictionlist A.RESTRICTWAGETYPES

Do you understand my explanation?

Regards

[Updated on: Wed, 02 September 2015 04:52]

Report message to a moderator

Re: migrate View from SQL to Oracle [message #642081 is a reply to message #642075] Wed, 02 September 2015 05:10 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hello,
do you have any reason for using that cryptic string instead of a simple list of string values
LOANR  in ('xxx1', '9004')

? Anyway, the characters '[]' have special meaning in regular expressions - "match any character in list" (http://en.wikipedia.org/wiki/Regular_expression#POSIX_basic_and_extended).
You were close, you just need to escape the '[' and ']' characters in the pattern (if you insist on using string instead of collections). Just beware that index on LOANR cannot be used for this expression.

There are many other ways in converting comma-separated strings to collections, which are described e.g. in this article: http://tkyte.blogspot.com/2006/06/varying-in-lists.html
However, I think it is better to use collection and stop using those funky strings, wherever they come from.

Report message to a moderator

Re: migrate View from SQL to Oracle [message #642083 is a reply to message #642078] Wed, 02 September 2015 05:27 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
oracle1987

Now the where clause must be deliver only the variable data from the table B where the field B.LOANR exists in the restrictionlist A.RESTRICTWAGETYPES

If that's so, would
from a, b
where A.RESTRICTWAGETYPES = B.LOANR

do any good?

If that view doesn't contain A table's data, then you might try
from b
where b.loanr in (select a.restrictwagetypes from a)

[Updated on: Wed, 02 September 2015 05:27]

Report message to a moderator

Re: migrate View from SQL to Oracle [message #642085 is a reply to message #642081] Wed, 02 September 2015 05:56 Go to previous messageGo to next message
oracle1987
Messages: 3
Registered: September 2015
Junior Member
Thanks!
I change the conditions.

Now it works!

and REGEXP_INSTR(replace(replace('[xxx1],[9004]','[','!'),']','!'), '!' || LOANR || '!', 1, 1, 0, 'i') > 0

[Updated on: Wed, 02 September 2015 05:57]

Report message to a moderator

Re: migrate View from SQL to Oracle [message #642086 is a reply to message #642078] Wed, 02 September 2015 06:30 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
oracle1987 wrote on Wed, 02 September 2015 04:52
hello thanks for your reply.

I try to describe you the requirement for this where clause.

The user can set the restriction of the wagetypes (-> the parameter list).
This restrictions are in the table A

A.RESTRICTWAGETYPES (VARCHAR2) / just this one line exists!:
[4001],[4002],[4003]


Are you saying you have a single row with multiple values in a single column?
If so, you have a fundamentally flawed design, regardless what rdbms you are using. All tables should be designed to Third Normal Form. This doesn't even meet First Normal Form. This is fundamental to the whole concept of relational databases.

https://en.wikipedia.org/wiki/Data_normalization
https://en.wikipedia.org/wiki/First_normal_form
https://en.wikipedia.org/wiki/Second_normal_form
https://en.wikipedia.org/wiki/Third_normal_form

Or to put it more succinctly, all non-PK elements in a table should "depend on the key,the whole key, and nothing but the key, so help you Codd".

Report message to a moderator

Re: migrate View from SQL to Oracle [message #642092 is a reply to message #642086] Wed, 02 September 2015 08:35 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and our Normalization for an example.

Report message to a moderator

Previous Topic: Data migration fromOracle to SFDC
Next Topic: Top 2 Salary from Each Dept without using Analytic Function
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 13:19:02 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.