|
How about
select c.customer_id
from customer_table c
where exists ( select rowid from orders_table t
where t.customer_id = c.customer_id
)
Should give you a list of customer ids that have orders. 
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Yeah thanks. I figured it out.
I just wanted the fastest way of seeing if something exists()  and then go do the job.
[add]
This is what I wanted:
IF Exists(Select ID from Table1 where ColName='Value')
Insert Into...
I'm laughing my ass out now after the 30 min feverish thinking(about speed) and searching.
It was right in from of me. Doh
[/add]
Thanks anyway.
All the best,
Dan
modified on Wednesday, June 8, 2011 2:44 PM
|
|
|
|
|
MDL=>Moshu wrote: seeing if something exists() and then go do the job
If you're going to do it, just do it, don't check first.
|
|
|
|
|
Generally it's a very good idea. But the thing is this.
I have a production app(scanners and all that) and normally a interface for setting up the lines,projects...
I did not allow them to delete a PN. Now they want that feature.
But in order to not delete any production data I need to check if any production data exists for that PN
and if not, then and only then delete all stuff related to that PN. Else refuse it.
Yeah I know SQL would return an error complaining about FK and stuff but I don't want the user to see that.
So first check and if not exists then delete.
Else show a message to the user that production datas exists for that PN and delete has been aborted.
All the best,
Dan
modified on Thursday, June 9, 2011 5:05 AM
|
|
|
|
|
I have declare a variable in date format in Oracle(toad). for that i insert the value System.DateTime.Now in C#. when i exceute the Procedure it shows the error. ORA-01843: not a valid month.
|
|
|
|
|
this is what I can tell you about such issues:
- if you are using an appropriate field type (date, time, datetime, etc), then the most likely bad value you could have is a NULL value.
- if you are using strings (varchar, whatever) to store date/time information, you are bound to get into trouble with regional settings, languages, etc. Just don't use strings!
If that doesn't help, either Google the error message, you'll get a lot of hits. Or show us more detailed information on your table, your code, and the actual data that fails.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3
|
|
|
|
|
dd mm yyyy vs mm dd yyyy, anyone?
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
that is what I was hinting at with "regional settings".
it isn't wise to use strings for storing dates, and when (one thinks) one has to, one should use an invariant culture thingy, and not rely on regional settings that could be changed by the user and create havoc overnight.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3
|
|
|
|
|
|
You're welcome.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.4
|
|
|
|
|
Has someone experience applying clustered index more than on one field?
I think it is wrong action. May be it could work with small database. My observation showed such estimation: same table same number of records. Execution query to select all fields from same table without having clustered index on single column is 8sec (650243). In other case I have apply clustered index on primary and foreign keys. Execution last about 12 sec.; basically it decreased performance. So now is a question: if I can apply non clustered index on couple fields, what is the best practice to apply clustered index. Whether it should be only single field to sort data in a table?
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
|
|
|
|
|
By definition, there can only ever be a single clustered index on a table - it is the clustered index that sets the physical order of records. Usually, the clustered index would be on a single field, and generally that will be a key field that auto-numbers. The performance hit for clustered indices is more related to inserts and updates on your data, as if you insert/update a record that is towards the start of the index, and make it fall towards the end, nearly the whole table is physically reordered on the disk to accomodate this.
|
|
|
|
|
Thank you. You confirmed my thoughts.
We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic
|
|
|
|
|
I have created one Ssis pkg in my local DB.I am connecting to the the remote server by using C# code in this Ssis pkg.But some cases(machines) it's giving error(user-component failure).So i need to conn remote DB and i have to execute Stored procedure in that DB.
Can anyone give me suggestions to conn remote DB from local DB(machine) or Anyone known about this error?
|
|
|
|
|
ravi11038 wrote: I have created one Ssis pkg in my local DB
You are using SSIS.
ravi11038 wrote: I am connecting to the the remote server by using C# code in this Ssis pkg
And SSIS is using C# code. And the C# code is handling the connection.
ravi11038 wrote: But some cases(machines) it's giving error(user-component failure).
So? The solution is obviously to figure out what the connection
ravi11038 wrote: So i need to conn remote DB and i have to execute Stored procedure in that
DB.
A stored procedure on a remote machine will not fix a connection problem to the remote machine.
Now perhaps you want to create a stored procedure on the local machine and it will connect to the remote machine. But that is just moving the connection problem from one place to another and is unlikely to solve the problem if you do not figure out what is actually causing the connection failure.
|
|
|
|
|
Dear All,
We have a table with millions of records,i created a Windows application to retrieve data from mentioned table,it taking too much time to get result,I loaded data in the form load event,its stucking for few minutes to display as data is too much.
anyone can help me out to find any way to retrieve data w/o stuck the application?
thanks
|
|
|
|
|
Do you really need to load million of rows from table on load of form?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
|
Member 4650287 wrote: I realized
You realized what?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Besides adding criteria (WHERE) to the data request look into indexes.
|
|
|
|
|
|
|
You can't be creating a report with millions of lines.
So you are actually creating something else. Instead of getting those millions of lines and then processing them you should write a stored procedure that gets only the data you need.
|
|
|
|
|
Hi,
When i execute the query
select convert(varchar(2),datepart(day,'2011-11-06'))
the result i get is 6, i need to get the answer as 06 if only one digit, if 2 then no need of 0 in the first place.
How to get that?
Regards,
YPKI
|
|
|
|
|
You can do something like below.
DECLARE @Input as varchar(10) = '2011-11-06'
select case when datepart(day,@Input) <= 9 then
cast('0' + cast(datepart(day,@Input) as varchar(1)) as varchar(2))
else cast(datepart(day,@Input) as varchar(2)) end AS NewDate
Regards
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
