Database

Re: Storing data which in the form of a list to database.

21-Aug-09 17:18

Wow, this question is so wrong in so many ways.

Databases store structured data (generally)
A list (complete) is unstructured data
Text wrap is not a concept a database has.

The way to go
Create a table with 3 fields
ID int identity(1,1)
Sequence int
ListText varchar(a number > the possible text length)

Into this you store the bullet number in Sequence and the text in ListText
When you retrieve the data you order it by Sequence and it is up to you to display the text in the control you want.

Never underestimate the power of human stupidity
RAH

udaykatakam23-Aug-09 21:32

udaykatakam

23-Aug-09 21:32

Yeah you are right thanks for your reply may be I did not frame my question right. Lets say my data is as follows.

Col1	Col2	Col3
1	Line1 Line2 Line3	xxx
2	ABC	XYZ

This is 2 rows of data and since database stores structured data first row would be stored as:

1 Line1Line2Line3 xxx

So my question is what method should I adapt so that when I pull the data from the database I would know that the data in Col2 and Row1 is in the form of:

Line1
Line2
Line3

I hope now I made my self clear.

-UdayKiran

Re: Storing data which in the form of a list to database.

Mycroft Holmes23-Aug-09 21:48

Re: Storing data which in the form of a list to database.

23-Aug-09 21:48

I have the nasty suspicion you are working with a lousy data structure, I am assuming that Line1,Line2,Line3 should be stored as seperate pieces of data, in a related table. I do not know enough about your structure or what you are trying to acheive to give any useful feedback.

udaykatakam24-Aug-09 11:29

udaykatakam

24-Aug-09 11:29

Alright Thanks for your time. Is there anybody who could help me with my problem.

-UdayKiran

About the SQL Execution Plan

mctramp16820-Aug-09 22:09

mctramp168

20-Aug-09 22:09

1、How to know which SQL statement runs faster than the other SQL statement by contrasting the being displayed execution plan?
2、Could you please tell me the meaning of I/O Cost、CPU Cost、Subtree Cost and Cost? and what is the measuring unit of these
costs? and if there is a mathematical expression among the cost,say Cost=I/O Cost+Cpu Cost+subtree cost?
3、Is it possible to compute the accurate time of one SQL statements execution costed by execution plan?

Re: About the SQL Execution Plan

Ashfield20-Aug-09 23:19

Re: About the SQL Execution Plan

20-Aug-09 23:19

The analysis of the execution plan is far too much to reply in a forum, the MSDN documentation gives all the information you need.

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

Mycroft Holmes21-Aug-09 0:38

Re: About the SQL Execution Plan

21-Aug-09 0:38

I think you may have the wrong handle on what to use the execution plan for, it is not for timing parts of a query, more for identifying high cost points of your query.

Do study the MSDN stuff if you need more detailed information but I look for the highest % nodes and make sure they are for index scans. This is a VERY crude use of the tool but a good first step.

Never underestimate the power of human stupidity
RAH

David Mujica21-Aug-09 2:54

David Mujica

21-Aug-09 2:54

I agree. I typically use the execution plan to make sure I can reduce the number of I/O's; no full table scans ... It has been my experience that if you reduce (or limit) the I/O's your application will respond quickly.

Regarding, timing of SQL: Why don't you build your own timer logic and try various SQL statements out? Keep it simple, something like this:

Begin timer1
Execute SQL1
End timer1

Begin timer2
Execute SQL2
End timer2

Now check which timer is the smallest. You will have to collect these statistics multiple times to ensure that you are not getting skewed results because the data happens to be in cache.

SQL:Find one primary key of each duplicate records

xodeblack20-Aug-09 19:42

xodeblack

20-Aug-09 19:42

I have table FEE,There are 9 rows in this Fee table

PK ........ FEE ........... TRANS. ID
11 ........ 5000$ ........ 2222
12 ........ 9000$ ........ 2222
13 ........ 1000$ ........ 2222
14 ........ 2000$ ........ 7777
15 ........ 1000$ ........ 7777
16 ........ 1000$ ........ 7777
17 ........ 2500$ ........ 9999
18 ........ 4100$ ........ 9999
19 ........ 4500$ ........ 9999

I want to fetch the only one primary key of each
duplicate 'trans. id'
For example: The result(PK) should be
11,14,17
OR
13,14,17
OR
14,14,17
......
......
Duplicate Trans. id are 2222,7777,9999.

Re: SQL:Find one primary key of each duplicate records

Ashfield20-Aug-09 21:01

Re: SQL:Find one primary key of each duplicate records

20-Aug-09 21:01

try this

select min(PK) from FEE 
where Trans.ID in (select distinct Trans.ID from FEE)
group by Trans.ID

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

xodeblack20-Aug-09 21:25

xodeblack

20-Aug-09 21:25

Hi Ashfield
First of all thankz for your reply

It returns only one PK....I want to get
one PK of each duplicate record ....
According to last example I want one PK of 2222(Trans. ID),one PK of 7777(Trans. ID) and one PK of 9999(Trans. ID)
so one possible result is
11,14,17
Other possible result is
12,14,17
One more possible result is
13,14,17
.........
Now I think you understand what I want
waiting your reply

Re: SQL:Find one primary key of each duplicate records

Ashfield21-Aug-09 1:21

Re: SQL:Find one primary key of each duplicate records

21-Aug-09 1:21

Thats odd. I used this:

create table #b1(pk int identity, refid int)

insert into #b1 (refid)
select 1
union all
select 2
union all
select 3
union  all
select 1
union all
select 2
union all
select 3

select min(PK) from #b1 where refid in (select distinct refid from #b1)group by refid

and it gave me 1, 2 and 3. I'm using sql server 2005, what are you using?

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

xodeblack21-Aug-09 1:28

xodeblack

21-Aug-09 1:28

I am using SQLServer 2005

Re: SQL:Find one primary key of each duplicate records

Ashfield21-Aug-09 4:20

Re: SQL:Find one primary key of each duplicate records

21-Aug-09 4:20

So does my test script work for you?

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

Coding C#20-Aug-09 22:25

Coding C#

20-Aug-09 22:25

select min(PK),Trans.ID from FEE 
group by Trans.ID

try this

Coding C#

ExciteTemplate

Re: SQL:Find one primary key of each duplicate records

David Skelly20-Aug-09 22:32

David Skelly

20-Aug-09 22:32

Why do you need the WHERE clause? Can't you just do:

select TransId, min(PK) from FEE group by TransId

Re: SQL:Find one primary key of each duplicate records

Ashfield21-Aug-09 1:19

21-Aug-09 1:19

You can if you also want the transid (which I suspect is the case), but the OP just wanted the PK Smile | :)

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

LIKE Predicate

TheIdleProgrammer20-Aug-09 3:50

TheIdleProgrammer

20-Aug-09 3:50

Hi guys,

I'm struggling with something conceptually that maybe somebody would be able to help me with. I'm writing an application to match vehicles in one database to vehicle in another database. As you can imagine there are difference all over the place so there will be a lot of manual matching to be done but I'm doing my best to cut down on the amount of manual work where possible. The LIKE predicate has definitely been my friend here, but how can I make it work the other way around, i.e. instead of C_Model LIKE '%Fiesta%' I could do with 'Fiesta' LIKE %C_Model%. If you know what I mean?

For instance I can match this:

DB1          DB2
'Fiesta'  -> 'Fiesta 1'
          -> 'Fiesta 2'
          -> 'Fiesta 3'

...but I don't know how to go this way:

DB1           DB2
'306 -97' -> '306'
'306 97-' -> '306'

Cheers,

Chris Chambers.

Mycroft Holmes20-Aug-09 4:17

20-Aug-09 4:17

You may now get ready to kick yourself....

You have no problem getting DB2 like 'Fietsa%'
what is wrong with getting Db1 like '306%'

Never underestimate the power of human stupidity
RAH

TheIdleProgrammer20-Aug-09 4:28

TheIdleProgrammer

20-Aug-09 4:28

Yeah, maybe I should have explained myself better.

Take this statement from the second databases DataSet class:

return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%'");

Is there an expression I can use to try to further enhance this expression to help me retrieve the records where C_Model is like the model passed in? If '306 -97' is passed in and the models table contains '306' rows how can I get to those rows? Like I say, it's fine if I pass in 'Fiesta' and the models table contains 'Fiesta 1', 'Fiesta 2', 'Fiesta 3' etc.

The more I think about this the more I'm beginning to think it's not possible. I just don't see how I can select rows where a certain field matches a substring of a literal string.

Mycroft Holmes20-Aug-09 13:15

20-Aug-09 13:15

return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%' or C_Model like '306%'");

This answer is too simple I must be missing something in your question.

I use stored procs for 99.9% of my database work and therefore can design the SQL to do exactly what I want in QA/EM. I suggest you do this to get the result you want and then move it to a string if that is where you want to have you SQL. Also look into parameterised queries, you current model is subject to sql injection.

Never underestimate the power of human stupidity
RAH

TheIdleProgrammer20-Aug-09 22:10

TheIdleProgrammer

20-Aug-09 22:10

Hi Mycroft,

Mycroft Holmes wrote:
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%' or C_Model like '306%'");

This won't work because the '306' is what's actually in C_Model; at the point this statement is executed I don't actually know what that is. I just used that '306' string as a typical example.

To be honest, the more I've thought about this the more I've decided it's a stupid thing to do in the context of my application anyway so I've abandoned the idea for now. I'm sorry if I've wasted your time. Many thanks for your help anyway.

Thanks again,

Chris Chambers.

Mycroft Holmes21-Aug-09 0:33

21-Aug-09 0:33

Not a problem

Never underestimate the power of human stupidity
RAH