|
Well, the idea is to encapsulate database handling to ease change of DBMS (Axapta is not originally created at Microsoft, so it actually supports Oracle too  ).
There are some options that the Damgaard/Naviosion/Microsoft guys are exposing though. But the only promising field that I can find, is one labeled "ODBC options" which I presume is meant for additional options to a connection string. I haven't been able to find any documentation on valid ODBC options to SQL Server 2008 though...
"God doesn't play dice" - Albert Einstein
"God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr
|
|
|
|
|
hi all,
can any one tell me what is an identity column and what is the use of this column in sql table...
Regards,
S.Inayat basha.
|
|
|
|
|
Try using Google: Identity column[^]. This was the first result.
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
nils illegitimus carborundum
|
|
|
|
|
|
identity automatically increment your column values from seed property specified in identify clause.
exam
create table tbl
( i int
identity(1000,1), name varchar(100))
output
1000 
1001
1002
Ganu Sharma
|
|
|
|
|
identity automatically increment your column values from seed property specified in identify clause.
exam
create table tbl
( i int
identity(1000,1), name varchar(100))
output as
1000 table1
1001 table2
1002 table3
Ganu Sharma
|
|
|
|
|
Hi all, I am trying to load data from a array to dataset or any other ways to display the data in a datagridview. if you can please help me in this regard.
thanks
|
|
|
|
|
You didn't say anything about what type of data or whether you need a DataSet for other reasons, but assuming you just want to display the data and nothing else, just run through a loop and add DataGridView rows and put your data in a cell. Simple, but the details of how you do it depend on what language you are using.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Hello Walt Fair,What I want to do is that I have an array of string, how do I put the array data in a grid view by column and row.
thanks
|
|
|
|
|
You need to loop through your array of strings and add a new row to your dataset and populate the fields of your new row with your data from the array
Example (VB.NET)
For idx As Integer = 0 To myArray.Length -1
Dim row as DataRow = myDataSet.Table(0).NewRow
row(0) = myArray(idx)
myDataSet.Rows.Add(row)
Next
Steve Jowett
-------------------------
Real programmers don't use PL/1. PL/1 is for insecure momma's boys who can't choose between COBOL and Fortran.
|
|
|
|
|
Hi guys,
I am doing a FULLTEXT search as shown below
Select t.*,k.[Rank]
From table1 t
Join Containstable(table1, column3, 'abcd|efgh|ijkl|mnop') As k On t.Id = k.[Key]
Order By k.[Rank] DESC
But I want the results to be ranked distinctly. Currently my results are listed something like the below
column1 column2 column3 Rank
1 asdc abcd|efgh|ijkl|mnop 99
2 asdad abcd|efgh|ijkl 99
3 cdsdfc abcd 80
4 efsef abcd|efgh 30
5 asasda abcd|efgh|ijkl 30
6 dsas abcd|efgh|ijkl|mnop 30
Also please note that row1 and row6 are having same values for column3 but the ranks are 99 and 30 respectively.
Thanks in Advance
Jophin
jophin
|
|
|
|
|
I'm not quite sure what you want.
If you want row 6 just below row 1 and so on,
just add another order filter:
Order By t.column3 [DESC/ASC], k.[Rank] DESC
|
|
|
|
|
Hi Scubapro,
Select t.*,k.[Rank]
From table1 t
Join Containstable(table1, column3, 'abcd|efgh|ijkl|mnop') As k On t.Id = k.[Key]
Order By k.[Rank] DESC
column1 column2 column3 Rank
1 asdc abcd|efgh|ijkl|mnop 99
2 asdad abcd|efgh|ijkl 99
3 cdsdfc abcd 80
4 efsef abcd|efgh 30
5 asasda abcd|efgh|ijkl 30
6 dsas abcd|efgh|ijkl|mnop 30
I am not able to do an order by for the column3 because its data type is ntext.
My actual requirement is that row1 and row2 should not have equal ranks because,Row1 is an exact match and Row2 is not.
It is fine for me if, Rank of Row2=Rank of Row5 and Rank of Row1=Rank of Row6 because they are having equal values for column3.
Kind Regards
jophin
|
|
|
|
|
Try:
Join Containstable(table1, column3, '("abcd|efgh|ijkl|mnop")') As k On t.Id = k.[Key]
|
|
|
|
|
Hi Scubapro,
Thanks for the reply.
But this is not making any difference in the rankings. But we found that the current ranking is somehow ok for us.
Thanks for you efforts
Regards
jophin
|
|
|
|
|
Being a SQL Server developer, I am not familiar with Oracle.
We have a third party product using Oracle 10g that uses a dropdown to show the unique values from a field in a table of 2.5 million rows, but with only 50 unique values. There is no index on the field and it takes a minute to run the query 'select distinct field from table order by 1' used to populate the dropdown.
Adding an index on that field should allow the optimiser to scan the index rather than the table, and as the indexed field is 1/50th of the table’s row length I would expect to see a similar decrease in the time taken to run the query. However, it made no difference to the performance.
I have been told that Oracle will ALWAYS do a table scan if there is no where clause in the query. If this is the case without access to the source to change the query, I seem to have no way to optimise this.
Is what I heve been told true?
If not how can I get the optimiser to use the index without being able to chnage the SQL in the query?
|
|
|
|
|
From my experience, I would say, yes, Oracle will do a table scan if there is no where clause.
However, adding and index to the table may not solve the problem either, the entire table may be 2.5 million rows yet there are only 50 unique values, this means that each unique values a has about 50,000 index entries. Oracle would have to read 50,000 index records to satisfy the query. The optimizer may choose to perform a table scan rather then read the index.
Let me contact a friend of mine who is a true Oracle expert and see if there is anything that can be done. (He worked for Oracle for 18 years, now is a consultant specializing in Performance Tuning)
I'm afraid you may have to live with a poorly designed application.
|
|
|
|
|
The only way you will know if there is a table scan or not is to look into the plan generated for the SQL. If a where clause is an absolute necessity, which I doubt, then you can always add something like where 1 = 1. But without access to modify the source for the SQL you are in trouble.
Also are the indexes in separate tablespaces and files from the data? If not that may be why you don't see any difference.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Explain Query in PLSQL Developer shows:
SELECT STATEMENT.GOAL=ALL_ROWS
SORT UNIQUE
TABLE ACCESS FULL
The TABLE ACCESS FULL has cardinality of 2.5 milion, the other two 50, so I guess that is a full table scan
|
|
|
|
|
In PLSQL Developer, try adding theses different where clauses and see if a TABLE ACCESS FULL still shows up.
where 1 = 1
or
where nvl(field,field) = field
The last one has the the field you are selecting in the where clause and may force the use of the index.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Explain Plan with - where field <> 'imposibleValue'
SELECT STATEMENT.GOAL=FIRST_ROWS
SORT UNIQUE NOSORT
INDEX FULL SCAN
and the cost has reduced from 22,089 to 344
However, it only helps my understanding, not the problem!
|
|
|
|
|
It's always good to be able to understand a problem. At least now you have really good evidence to provide to the 3rd party vendor with a request for a change. Whether it is accepted or not is unfortunately an entirely different situation. Good luck with it though.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
You are right - adding a 'null' where clause such as where field <> 'imposibleValue' reduces the execution time from 1 minute to 1 second, so that is obviously forcing the use of an index.
Shame I can't change the sql 
|
|
|
|
|
Wow! This thread just tought me that I had really good luck when I designed the generation of the sql queries in our application: I do often start the WHERE clause with a "WHERE (1=1) " so that I can add further conditions simply with " AND " (instead of checking if the WHERE clause is still empty or not) - I did so because of that laziness...
|
|
|
|
|
See, it pays off to be lazy sometimes.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
