Database

16-Dec-09 18:52

I have a columns say, Col and Col[1] in table say Test. Now I want to do a select from it. How to go about it? (Since [] has a special meaning for SQl server) I tried

select Col,Col[1] from Test

but for obvious reasons, it does not works.

[Edit]
This will work:

Select Col,[Col[1]]] from Test

[/Edit]

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

modified on Thursday, December 17, 2009 12:59 AM

Mycroft Holmes16-Dec-09 18:59

Mycroft Holmes

16-Dec-09 18:59

try

select Col,[Col[1]] from Test

Who would be dumb enough to use reserved words or characters in a column name. Stoopid really.

Never underestimate the power of human stupidity
RAH

dan!sh 16-Dec-09 19:01

16-Dec-09 19:01

Mycroft Holmes wrote:
Who would be dumb enough to use reserved words or characters in a column name.

Well, I just did that to find how SQL will behave.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Mycroft Holmes16-Dec-09 19:50

Mycroft Holmes

16-Dec-09 19:50

Did not even look at the moniker before answering otherwise I'd have been questioning your sanity.

Doing that is akin to sticking your finger in the power point, may not kill you but your fingers gonna sting for a while.

So did the extra set of brackets work? I flatly refuse to try it out

Never underestimate the power of human stupidity
RAH

dan!sh 16-Dec-09 21:16

How to group data which not the same ?

16-Dec-09 21:16

Mycroft Holmes wrote:
Doing that is akin to sticking your finger in the power point, may not kill you but your fingers gonna sting for a while.

Never tried this.

Mycroft Holmes wrote:
So did the extra set of brackets work?

Yes it did.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Golden Jing15-Dec-09 23:06

Golden Jing

15-Dec-09 23:06

Dear All,

First i would like to sorry if my expliant not clear.

My issue is that
I want group data in field company name which not equal each other.
It caused by inputting different users.
Example:
Company_Name
ComxxxA Co.
ComxxxA Co.
ComxxxA Co.,Ltd.
ComxxxA Co.,Ltd.
ComxxxA Co.,
ComxxxA Co.,
ComxxxA
ComxxxA
Comx
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co
ComxxxxxxxB Co.,
ComxxxxxxxB Co.,

..... (there r alot company name which difference)

So up to example above, what i want is the name is shortest
ComxxxA
Comx
ComxxxxxxxB Co

thanks for your help...

VB.Net

Niladri_Biswas16-Dec-09 2:00

Niladri_Biswas

16-Dec-09 2:00

Try this

declare @t table(Company_Name varchar(50))
insert into @t 
	select 'ComxxxA Co.' union all select 'ComxxxA Co.' union all
	select 'ComxxxA Co.,Ltd.' union all select 'ComxxxA Co.,Ltd.' union all
	select' ComxxxA Co.,' union all select 'ComxxxA Co.,' union all
	select 'ComxxxA ' union all select 'ComxxxA ' union all 
	select 'Comx' union all select 'ComxxxxxxxB Co.,Ldt' union all
	select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co.,Ldt' union all
	select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co' union all
	select 'ComxxxxxxxB Co.,' union all select 'ComxxxxxxxB Co.,'

Query:

select  Company_Name
from @t 
where replace(Company_Name, ' ','') not like '%[.,]%'
group by Company_Name

Output:

Company_Name
Comx
ComxxxA 
ComxxxxxxxB Co

Niladri Biswas

Golden Jing16-Dec-09 2:29

Golden Jing

16-Dec-09 2:29

Thanks,

Yes it like example.
But it is not enough yet.
Because in this field has more companies like that
up to differenc user enter difference.

So have any solution for protect it ?

VB.Net

[Message Deleted]

Niladri_Biswas16-Dec-09 3:23

Niladri_Biswas

16-Dec-09 3:23

[Message Deleted]

Golden Jing16-Dec-09 14:51

Golden Jing

16-Dec-09 14:51

it is not complete what i want.

but i would like to thank to you so much.

VB.Net

ADODB Recordset - using ActiveX Data Object in Access 2007

Luc Pattyn16-Dec-09 15:03

Luc Pattyn

16-Dec-09 15:03

Hi,

you can use SELECT DISTINCT[^] and ORDERBY to get an ordered list of all different company names present.
you can't find the "shortest" names as in your example, it would only give "Comx", not what you said it would.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

john john mackey15-Dec-09 12:38

john john mackey

15-Dec-09 12:38

Hi.
I wrote some VBA underlying code for an Access 2000 application and used to create/open recordsets in the following manner:

Dim rstLookupFixedAreas As New ADODB.Recordset
rstLookupFixedAreas.Open "tbl_FixedAreas", CurrentProject.Connection, adOpenStatic, adLockOptimistic

rstLookupFixedAreas.MoveFirst
Do Until (rstLookupFixedAreas.EOF)
    strValueList = strValueList & CStr(rstLookupFixedAreas.Fields(0)) & ";" & rstLookupFixedAreas.Fields(1) & ";"
    rstLookupFixedAreas.MoveNext
Loop

comboFixedArea.RowSource = strValueList
'comboFixedArea.DefaultValue = 1

rstLookupFixedAreas.Close

I did this by including the reference to Microsoft ActiveX Data Object 2.1 Library.

Now that I am using Access 2007, is there a more current method to do the same type of "recordset" operations? (or should i continue with including ADO 2.1 Library)

I want to use either a record index or Find method to get my record pointer.

Thank you for the help.
John John

MS-SQL - Bulk Insert query

22491715-Dec-09 11:50

224917

15-Dec-09 11:50

I have a .csv file with following column:

Col1,Col2

And the data table has one extra datetime column:

Col1,Col2,TimeStamp

While importing data using BULK INSERT from the CSV, is there a way to specify the value for the TimeStamp column too? (Timestamp would be same for all the newly imported rows)

Suhredayan

Re: MS-SQL - Bulk Insert query

Ashfield15-Dec-09 21:01

Ashfield

15-Dec-09 21:01

How about defining a default value for the column?

ALTER TABLE dbo.tablename ADD CONSTRAINT
	DF_TimeStamp DEFAULT '20091218' FOR TimeStamp

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

Re: MS-SQL - Bulk Insert query

22491716-Dec-09 6:14

224917

16-Dec-09 6:14

Ashfield wrote:
How about defining a default value for the column?

That may not work since the timestamp is same only per bulk insert. I do have a solution for this, wrap bulk insert inside a stored proc, and update the the timestamp from SP. But this would introduce few additional steps, was wondering if this can be avoided in case if BULK INSERT already has this feature.

Thank you,
Suhredayan

Question about using conditional SQL statement

James Shao15-Dec-09 4:22

James Shao

15-Dec-09 4:22

I have a time series data in which a numerical indicator fluctuates over time. If I want to find out for which given day, this indicator has crossed above 30 in the past 3 days. Is it possible to write a SQL query that retrieve this information?

The logic to check for the crossing movement is:

IF indicator(day x) > 30 AND indicator(3 days ago from day x) <30 THEN
Return "YES WE HAVE AN UPWARD CROSSING MOVEMENT THROUGH 30 FOR PAST 3 DAYS!"
END IF

Can this be accomplished using a SQL CASE statement with a HAVING clause or something else?

I'd greatly appreciate any help! If possible please provide a sample code Poke tongue | ;-P

Re: Question about using conditional SQL statement [modified]

Ashfield15-Dec-09 4:34

Ashfield

15-Dec-09 4:34

An exists should do it - as far as I can tell from your description

select column_list....
from table1 t1
where indicator > 30
and exists (select 1 from table1 t2 where t2.pk = t1.pk and t2.indicator < 30 and t2.dateadded = dateadd(day,-3,t1.dateadded))

where PK is your primary key

[Edit] I had missed the date bit from the exists originally [/Edit]

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

modified on Tuesday, December 15, 2009 11:17 AM

Re: Question about using conditional SQL statement [modified]

James Shao15-Dec-09 15:02

James Shao

15-Dec-09 15:02

Hi Ashfield, Your code looks exactly like what I need! I'll give it a try after getting home. Smile | :)

Many thanks again.

modified on Tuesday, December 15, 2009 10:19 PM

Re: Question about using conditional SQL statement

Ashfield15-Dec-09 20:57

Ashfield

15-Dec-09 20:57

no problem

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

CROSS POST

dan!sh 15-Dec-09 5:08