Database

how can write this oracle statement in sql server

18-Mar-09 4:21

You'd do a "create table" from code.

No thanks, you're welcome Smile | :)

I are troll Smile | :)

lakshmichawala18-Mar-09 1:58

lakshmichawala

18-Mar-09 1:58

InStr(AUD_COMMENTS,tTempEx1,1,1)-(InStr(AUD_COMMENTS,tTempEx +,1,1)+2)

Re: how can write this oracle statement in sql server

Eddy Vluggen18-Mar-09 2:02

18-Mar-09 2:02

You're looking for the parts about CharIndex and SubString, and the documentation is here[^] Smile | :)

I are troll Smile | :)

crossmatch data

AndyInUK18-Mar-09 1:04

18-Mar-09 1:04

Hi All,

There is a table with 200,000 rows and 7-8 columns with registeration number as a unique key.

Now i have xls sheet where i have got 16,000 registeration numbers. I need to crossmatch these numbers with the one in sql 2008 database to see how many matches with the database ?

Any help would be greatly appreciated.
Thanks

moon_stick18-Mar-09 1:11

moon_stick

18-Mar-09 1:11

You need to import the Excel spreadsheet into a new table in the database and then join the existing table onto the new table using the registration number as the join condition.

It definitely isn't definatley

AndyInUK18-Mar-09 1:12

18-Mar-09 1:12

cheers. I will try to do that now.

AndyInUK18-Mar-09 1:35

18-Mar-09 1:35

I have created new table with registeration number id and now trying to import the xls sheet into that table but data type is not matching. In xls the data type is 'double' and in sql there is no option to select data as double. Tried bigint etc but no success.

The below is the source information, So how to match it with new table ?
[Source Information]
Source Location : C:\abc.xls
Source Provider : Microsoft.ACE.OLEDB.12.0
Table: `'Users'`
Column: regxyz
Column Type: Double
SSIS Type: double-precision float [DT_R8]
Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles\JetToSSIS.xml

Any idea ??

Thanks

moon_stick18-Mar-09 1:42

moon_stick

18-Mar-09 1:42

I'm confused - are you saying that the data type of the registration number in the Excel spreadsheet is a double? Or are we talking about another column in the spreadsheet? What is the data type of the registration number in your database table?

It definitely isn't definatley

AndyInUK18-Mar-09 1:50

18-Mar-09 1:50

Basically the excel spreadsheet contains list of only one column which is registeration number - 5 to 8 digits numbers and while importing it through sql import wizard, it says source is double type and does not match with the column i created in new table in sql.

The reg number in the old databasse table is nvarchar and the new one i created just now is bigint but i will now try it with nvarchar ?

So once i get import the reg no to the sql table then i want to match that with the existing reg no in database to see how many reg no already exist.

moon_stick18-Mar-09 2:03

moon_stick

18-Mar-09 2:03

The SQL import wizard usually looks at the first 10 or 20 rows of data to figure out the type - I'd be surprised if it was incorrectly recognising an integer as a double.

In any case, the columns you're comparing should ideally have the same data type (probably int / big int).

If you're not even sure how to write the query, you'll probably want to invest in a book or two or do some research online (i.e. http://www.w3schools.com/sql/default.asp[^]). Joining two tables together is one of the most fundamental database tasks you can do.

To get you started, something like:

<br />
select<br />
t1.RegNum<br />
from<br />
DBTable t1<br />
inner join ExcelTable t2<br />
on t1.RegNum = t2.RegNum<br />

will work. If you can't get the two columns to be the same database, something like this might work better (but I can't guarantee the results)

<br />
select<br />
t1.RegNum<br />
from<br />
DBTable t1<br />
inner join ExcelTable t2<br />
on cast(t1.RegNum as varchar(max)) = cast(t2.RegNum as varchar(max))<br />

It definitely isn't definatley

AndyInUK18-Mar-09 2:14

18-Mar-09 2:14

yeh i know how to join the tables.
Thanksa l lot for your help mate.Was just confused as its been a while working on sql.

AndyInUK18-Mar-09 1:58

Convert Query Command from Oracle to MS-Access

18-Mar-09 1:58

yeh now i was able to import it. Now i just have to compare the two tables

M Riaz Bashir17-Mar-09 21:25

M Riaz Bashir

17-Mar-09 21:25

Hi,

kindly let me know the following query command into MS-Access.

SELECT A.*, B.MNAME
FROM DETAIL A, MASTER B
WHERE A.MCODE=B.MCODE(+)

Thank you in Advance
(Riaz)

Re: Convert Query Command from Oracle to MS-Access

Ashfield18-Mar-09 4:41

Ashfield

18-Mar-09 4:41

Well, if you know Oracle you should know that the (+) indicates an OUTER JOIN. Does that give you a clue?

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Defragmentation Problem

Aman786Singh17-Mar-09 21:06

Aman786Singh

17-Mar-09 21:06

I have a table containing 1 crore tuples(rows).
At the creation time , i have applied indexes on it.

Should i defrag it peiodically ??

Only the Select Operation is performed on it..

Kindly suggest...

Eddy Vluggen17-Mar-09 22:11

17-Mar-09 22:11

Aman786Singh wrote:
Only the Select Operation is performed on it

It won't get fragmented indeed, since you
- only read from it
- are using a database, not a file-system

Hope this helps Smile | :)

I are troll Smile | :)

Luc Pattyn18-Mar-09 5:55

Luc Pattyn

18-Mar-09 5:55

Eddy Vluggen wrote:
are using a database, not a file-system

you expect a read-only file-system to get fragmented over time?

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Eddy Vluggen18-Mar-09 6:08

18-Mar-09 6:08

I was thinking that there's no "defrag.exe" for databases! And I bet that somewhere, someone will have written such a thing Smile | :)

I are troll Smile | :)

Aman786Singh18-Mar-09 6:40

Aman786Singh

18-Mar-09 6:40

WHEN I RAN INDEXDEFFRAG..

THEN WHAT THIS RESULT MEANS???

PAGE SCANNED PAGE MOVED
245 0

Eddy Vluggen18-Mar-09 9:46