Database

Re: Data duplication on multiple fields

5-Jul-16 11:14

In which way do you need to compare the the rows?
Are you looking for duplicates? Partial or fully.
Are there any text fields that needs fuzzy matching?

Wrong is evil and must be defeated. - Jeff Ello

Mycroft Holmes5-Jul-16 17:21

Re: Data duplication on multiple fields

5-Jul-16 17:21

add and identity column to the table
concatenate the 7 fields into a key field (can use a view)
group by the key field having a count > 1

You now have a list of the duplicated key values.

select identity field using row_number() and partition over the key field
delete anything with a row_number > 1

Never underestimate the power of human stupidity
RAH

jonyj18-Jul-16 2:49

jonyj

18-Jul-16 2:49

What RDBMS are you using?

You could do something like

SQL

SELECT DISTINCT (your 7 columns) FROM table

to get the unique values if that's what you're looking for.

SQL Transactions - Torn apart in old code

HobbyProggy5-Jul-16 1:12

HobbyProggy

5-Jul-16 1:12

Hey guys,

while i was doing some performance tests on my software i came across a fascinating result.
A lot of my DB query execution code uses transactions since if there is an error i have to undo the canges on the database. So now i found that the transaction creation and commit takes roughly 2 the cost of the actual executeQuery(). I also have a isDBAvailable check before every DB query gets fired to cope with connection problems. Now i saw i used to do the available check also with a transaction, which is in fact quite an overload for just checking a SELECT 0 executing as scalar.

So now im not sure if i should stick to the transaction there, any pros or cons? Should i just execute the scalar without using a transaction, what would you guys do? I mean it actually costs ~20ms so far using a transaction and currently im not sure if i need it or not. Little bit stuck right here cause i don'T know what i was thinking back then Smile | :)

Discuss?

[ed. Moved to Database discussion forum]
Edit: Thanks for moving, i see that the term technical discussion was a bit overshot in this!

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

modified 5-Jul-16 7:57am.

Marc Clifton5-Jul-16 1:15

Marc Clifton

5-Jul-16 1:15

HobbyProggy wrote:
A lot of my DB query execution code uses transactions

Maybe I have a big gaping hole in my understanding of DB's, but why do you need to create a transaction if you're "just" doing a query? Are you writing to some temp tables to perform the query?

Marc

Imperative to Functional Programming Succinctly

Contributors Wanted for Higher Order Programming Project!

Learning to code with python is like learning to swim with those little arm floaties. It gives you undeserved confidence and will eventually drown you. - DangerBunny

HobbyProggy5-Jul-16 1:18

HobbyProggy

5-Jul-16 1:18

Not really, but let's say i would insert 20 datasets that are dependant of something else and dataset 19 fails i need to reroll the whole data. That's actually why i use a transaction, so it get's done en block and can be reverted in case of failure.

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

Dave Kreskowiak5-Jul-16 1:51

Dave Kreskowiak

5-Jul-16 1:51

Again, why would you put any SELECT in a transaction? (Hint, hint)

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

HobbyProggy5-Jul-16 1:56

HobbyProggy

5-Jul-16 1:56

THANKS! I now reread it and got it. Maybe that was the knot in my head! Dead | X|

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

Time calculation

AVPRF4-Jul-16 20:37

AVPRF

4-Jul-16 20:37

Hi,

In my table having, from time and to time, I have to calculate the difference between the two times to calculate hour's of time worked. So can anyone provide me a solution

Mycroft Holmes4-Jul-16 21:01

4-Jul-16 21:01

Do some research into DATEDIFF[^]

Never underestimate the power of human stupidity
RAH

AVPRF5-Jul-16 19:47

AVPRF

5-Jul-16 19:47

Yes, I checked using datediff and my query is

SQL

select datediff(hh,'05:30:00','10:00:00') from tablename

but it gives me 5 as answer which is not correct

Mycroft Holmes6-Jul-16 12:40

6-Jul-16 12:40

It is perfectly correct, you looked for the difference in hours. Use your brain, try minutes or even seconds, if you wait for answers to be spoon fed to you it is going to be a long slow day.

Never underestimate the power of human stupidity
RAH

How to Search for names in MySQL

AVPRF6-Jul-16 19:02

AVPRF

6-Jul-16 19:02

Thanks.. I got the answer

Jassim Rahma2-Jul-16 13:06

Jassim Rahma

2-Jul-16 13:06

Hi,

I have mySQL with first_name, middle_name and last_name fields.

How can I pass a string from my php like param_name so when the user pass single name or multiple name it will split and search for the name in every fields..

For example if the user search for param_name = ""John Mathew Clark" it will do:

first_name or middle_name or last_name LIKE "%John%"
first_name or middle_name or last_name LIKE "%Mathew%"
first_name or middle_name or last_name LIKE "%Clark%"

Thanks,
Jassim

Technology News @ www.JassimRahma.com

modified 14-Jul-17 15:56pm.

Re: How to Search for names in MySQL

Mycroft Holmes2-Jul-16 14:10

Need guidance: duplicate oracle db to another

2-Jul-16 14:10

I don't know about MySQL but in SQL Server I have done the following in the past.

Build a function that takes a string and splits it on a delimiter (space) and return a table.
In your query left outer join the function, passing in your name field, on your name field with Name = or LIKE '%' + itemFromFunction + '%'

Make the result set DISTINCT.

Not sure if = or like is needed in the LOJ.

This is an old SQL Server split function I dug up.

SQL

<pre>
ALTER   FUNCTION [dbo].[fn_Split]
 (@List  varchar(8000), @Delimiter char(1))

RETURNS @Results table
 (Item varchar(8000),ID int Identity(1,1))

AS

begin
 declare @IndexStart int
 declare @IndexEnd int
 declare @Length  int
 declare @Word  varchar(8000)

 set @IndexStart = 1
 set @IndexEnd = 0

 set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','

--Get rid of any tabs or returns
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')

while @IndexStart <= @Length
begin
    set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)

    If @Delimiter = char(32) 
        set @IndexEnd = charindex(Space(1), @List, @IndexStart)

    if @IndexEnd = 0
        set @IndexEnd = @Length + 1

    set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
    set @IndexStart = @IndexEnd + 1

    INSERT INTO @Results(Item)
    SELECT @Word
end

return
end

Never underestimate the power of human stupidity
RAH

Foothill30-Jun-16 10:48

Foothill

30-Jun-16 10:48

I am having serious difficulties in my attempt to duplicate a production db to a test instance on the same server.

Using these examples has not helped:
Duplicate a database
Duplicate
Duplicating a database
Duplicating a database using RMAN
and there's more. All attempts with all these materials still end in failure.

Additionally, the lack of in-depth examples for Oracle administration available on the internet (indexed by Google/Bing) is concerning.

Okay, here's the closest that I have been able to get

1. Create test database with the Database Configuration Assistant
2. Create minimal SPFile {DB_Name=XXXX}
3. Shutdown test database
4. Start test database with NOMOUNT and minimal SPFile
5. With RMAN, connect to prod db (target)
- connect to test db (auxiliary)
- ran this command
RMAN> duplicate target database to XXXX
2> from active database
3> password file
4> DB_FILE_NAME_CONVERT '/oracle/oradata/prod/','/oracle/oradata/test/';
6. It ran for a minute then died with a lot of these errors
RMAN-05001: auxiliary file name D:\ORACLE\ORADATA\DCDI\CATALOGTBS.DBF conflicts with a file used by the target database

I have found some info with a 'you need to add this: CONFIGURE AUXNAME ....' but I have no clue where to add these lines and no example was provided.

By this point, the SPFile and Control file are in an invalid state. No clue how to get them back to try again. I've dropped and recreated the test database several times.

I have no clue what I am doing and I cannot find any example on how to do this properly. I've been working on this for about a day and a half and getting nowhere fast.

Can somebody help walk me through this or point me to a good example. I have no experience with Oracle admin and I'm reaching the end of my wits here. Dead | X|

if (Object.DividedByZero == true) { Universe.Implode(); }
Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016

Chris Quinn30-Jun-16 21:26

Chris Quinn

30-Jun-16 21:26

Can you not just do a backup and restore?

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Jörgen Andersson30-Jun-16 22:02

30-Jun-16 22:02

This is Oracle.
However much I prefer Oracle to SQL Server this is an area where Oracle sucks.

Wrong is evil and must be defeated. - Jeff Ello

Foothill1-Jul-16 3:16

Foothill

1-Jul-16 3:16

Would that copy everything? I need to copy not just tables and data, but procedures, users/passwords, links to other databases, etc...

if (Object.DividedByZero == true) { Universe.Implode(); }
Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016

Jörgen Andersson30-Jun-16 22:21

30-Jun-16 22:21

I would use Export/Import[^] instead.
The Enterprise Manager has a graphical UI that's quite helpful.
If you export only tablespaces from the database you'll need to setup the users on the new database BEFORE importing.

Wrong is evil and must be defeated. - Jeff Ello

Foothill1-Jul-16 3:14

Foothill

1-Jul-16 3:14

While the export/import track is an option, it is necessary to get a complete copy of the database (users, tables, procedures, everything). Using RMAN's replicate feature appears to be the logical choice to accomplish this in one easy to reproduce procedure that can be run whenever it's needed. The trouble is getting it to run correctly the first time. Once I have the correct procedure to replicate this production database to the test database, I can write up documentation. I'm having difficulty in getting it right.

if (Object.DividedByZero == true) { Universe.Implode(); }
Meus ratio ex fortis machina. Simplicitatis de formae ac munus. -Foothill, 2016

Jörgen Andersson1-Jul-16 6:11