Click here to Skip to main content

Database

Re: On error resume next

Eddy Vluggen3-Aug-12 4:02

3-Aug-12 4:02

vanikanc wrote:
Thank you for all your suggestions!

My pleasure, hope I wasn't too rude.

vanikanc wrote:
It is just some inherited code,

Ah, that's always a good one to include on the first post. If we see code, we assume you wrote it, and partially understand it.

vanikanc wrote:
There was an error entering the value, it was supposed to be 50 and the user keyed in 500000. So, the business wants us to put aside such errors and continue processing the data.

On Error Resume Next indeed. That's not how engineers work; if it fails, it fails for a reason. It get's corrected or excluded beforehand, not ignored. It's how managers work; if it fails, and nothing is burning, it's not a problem. Just ship the damn product already, we'll fix the bugs later.

Perhaps this would be a good time to add a validator to the entry-field of that user, and sanitize his/her input before it gets into the system. Once the value has been entered, it should be treated as "correct".

You heard about Knight Capital? Seems they had an "On Error Resume Next" idea to, and the algo kept buying stocks in packages of 100 at a time, 20 to 25 times a second - for over an half hour! (Total >440 million* losses - let's just be glad they weren't a hospital and relying on that software)

Ignoring errors is the worst offence in IT; the system could have skipped customer 59 for all we know. The best approach is preached by (forgive me) PHP, and it's called "do or die". Either the app does what it should do, or there's an unexpected exception - and since we cannot guarantee that the we're still working with valid data (unexpected situation, who knows what variables are loaded and not?) we have only one realistic option; let the app die. Terminate.

That's always better than continuing and writing records with an outdated identity-value after an exception, and not nowing that you're corrupting a database that was still correct when the app died.

_{*) I checked this time whether I should use million or billion.}

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

if you can't read my code, try converting it here[^]

SQL Query - write all in one query

Raman samineni1-Aug-12 6:30

1-Aug-12 6:30

Hello Friends

I have 3 select statments I need to write all in one query How, is it works in MS SQL in Management Studio

Select E.FirstName +' ' + E.LastName as Supervisor
INNER JOIN [cgs].dbo.tems h ON(h.Supervisor = E.EmploId)
where h.temId = 336

Select E.FirstName +' ' + E.LastName as Agent
From [cgs].dbo.Employ E with (NoLock)
where E.EmploId = 2305

Select count(*) As Tras
From CGCSLF
Where Dispo = 'Tras'

Select count(*) As Comp
From CGCSLF
Where Dispo = 'Comp'

SELECT AgeID Sum(Hours) As HOURS
FromCGCSLF
Group By AgeID
Raman

Thank you in advance

Re: SQL Query - write all in one query

PIEBALDconsult1-Aug-12 8:42

1-Aug-12 8:42

I'm unclear what you require.

If you are using the ADO.net classes in SqlClient then you can put all those in one Command -- just separate them with semi-colons (;). You can then use ExecuteReader to get a DataReader to read the results -- use the NextResult method to advance to the results from the next statement. I'm fairly sure that DataAdapters will handle it as well, but I haven't used one for several years.

If you need to do something else, then please clarify your question.

Re: SQL Query - write all in one query

Raman samineni1-Aug-12 16:02

1-Aug-12 16:02

hello sir,
I call this query in the program, before I do, I am testing query in the SQL management studio. In program Code is like that

String query = @"SELECT t.firstName + '' + t.LastName

Regards
Raman

Re: SQL Query - write all in one query

PIEBALDconsult1-Aug-12 18:23

1-Aug-12 18:23

I don't think you finished what you intended to write, but what you did write looks bad.

Re: SQL Query - write all in one query

Mycroft Holmes1-Aug-12 13:17

1-Aug-12 13:17

Raman samineni wrote:
I have 3 select statments

Funny I count 5, the ability to count is reasonably fundamental to stating your problem. You also need to be much clearer what your goal is.

Do some reading on UNION and UNION ALL, these will help. Your queries must have the same fields and formats so your first query should be something like

SQL

Select E.FirstName +' ' + E.LastName as FullName, 'Supervisor' as TypeName
INNER JOIN [cgs].dbo.tems h ON(h.Supervisor = E.EmploId)
where h.temId = 336

Never underestimate the power of human stupidity
RAH

Re: SQL Query - write all in one query

Raman samineni1-Aug-12 15:56

1-Aug-12 15:56

I put Union and Union all, it does not work.

Thanks
Regards
Raman

Re: SQL Query - write all in one query

Mycroft Holmes1-Aug-12 16:25

1-Aug-12 16:25

Did you do some reading on how to use UNION or did you just stick it in between the select queries. RTFM

Never underestimate the power of human stupidity
RAH

Re: SQL Query - write all in one query

Raman samineni2-Aug-12 8:22

2-Aug-12 8:22

Hello sir,

Just stick in between the select queries. I dont want use Union , Because this qry goes to in the program.

Regards

Raman

Re: SQL Query - write all in one query

Eddy Vluggen1-Aug-12 22:34

1-Aug-12 22:34

Raman samineni wrote:
I need to write all in one query

What would you gain from having it in a single statement? As PIEBALD stated, you can put them in a single SqlCommand and execute them.

The first Sql-statement will not work. Test it again, it lacks a FROM clause.

you could embed the count as a sub-select, like this;

SQL

SELECT E.FirstName +' ' + E.LastName AS Agent
      ,(SELECT COUNT(*) FROM CGCSLF WHERE Dispo = 'Comp') AS [Comp]
 FROM [cgs].dbo.Employ E with (NoLock)
WHERE E.EmploId = 2305

What the ELEPHANT is that NOLOCK doing there??
You need to pay attention to your formatting
EmploId is redundant; you're already in the "E" table, just name the damn thing "Id". That way all keys are named Id, saves some time typing and thinking, less chances on a typo, and a lot clearer when reading the code.

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Query - write all in one query

Raman samineni2-Aug-12 8:19

2-Aug-12 8:19

Hello sir

Thank you. My out put was

Supervisor- AgentName-Hours-Tran-Completed,

when I ran the qry i need to get this report.

Regards
Raman

Re: SQL Query - write all in one query

Eddy Vluggen2-Aug-12 8:59

2-Aug-12 8:59

Raman samineni wrote:
when I ran the qry i need to get this report.

You could put the other query in there too, as a subquery. The other queries might be combined, depending on "what" you are selecting from. I have not seen a corrected version of that code yet; I suggest you start writing and testing Smile | :)

Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Query - write all in one query

Raman samineni6-Aug-12 8:37

6-Aug-12 8:37

hello sir,

How all three statments be written as one query.

Regards
Raman

Re: SQL Query - write all in one query

Raman samineni6-Aug-12 8:39

6-Aug-12 8:39

how I will put in the sql Command all three query

Thanks
Raman

Re: SQL Query - write all in one query

Eddy Vluggen6-Aug-12 8:50

6-Aug-12 8:50

Raman samineni wrote:
how I will put in the sql Command all three query

Do you need them in a single "sql statement", or do you need them in a single "SqlCommand"? For either way you have been given a solution - did they not work, and if so, what went wrong?

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Query - write all in one query

Raman samineni6-Aug-12 11:11

6-Aug-12 11:11

send me both, No problem

Thanks
Raman

Re: SQL Query - write all in one query

Eddy Vluggen6-Aug-12 11:30

6-Aug-12 11:30

Raman samineni wrote:
send me both, No problem

AFAIK, we're volunteers here. Why should I provide two pieces of code??

The first query from your list is simply invalid, as it lacks a FROM statement. You can search on the internet what a "subquery" is and how to write one, and try to combine that code as suggested. Also reread PIEBALDs' suggestions, there's a good reason they get upvoted every time.

Good luck, you'll need it Dead | X|

Dead | X|

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Query - write all in one query

Raman samineni8-Aug-12 5:30

8-Aug-12 5:30

hello sir,

My OUT PUT Report was

SuperName Agent Name Hours Trans Compl

Thanks
Raman

Re: SQL Query - write all in one query

Eddy Vluggen8-Aug-12 7:00

8-Aug-12 7:00

I can't help you, sorry.

We gave quite some answers and options, and your response was "write them both for me". Unless you can answer whether you need them in a single SqlCommand or as a single Sql-statement, there will not be any progress.

Now, it doesn't add anything of value to have them in a single statement, but I'm willing to help. When I say "help", I mean that I can point out how some things are done, it does not mean that I am going to write YOUR code.

Re: SQL Query - write all in one query

Raman samineni8-Aug-12 11:21

8-Aug-12 11:21

yes, that is true, I need them in SQLCOMMAND or String query in the program

I appreciated

Raman

Re: SQL Query - write all in one query

Eddy Vluggen8-Aug-12 12:12

8-Aug-12 12:12

You're welcome Smile | :)

Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

if you can't read my code, try converting it here[^]

Arul R Ece31-Jul-12 23:57

31-Jul-12 23:57

Hi friends,

I want to know the future of SSRS in SQL .

Eddy Vluggen1-Aug-12 2:48

1-Aug-12 2:48

Arul R Ece wrote:
I want to know the future

Hang on a second, while I go fetch my crystal ball.

Arul R Ece wrote:
of SSRS in SQL

There's a 95% chance that it will still be supported in the next five years.

Anything particular that you're worried about? If not, I suggest bookmarking their blog[^].

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

if you can't read my code, try converting it here[^]

Replication Help

Mohamad Shahmardan31-Jul-12 23:09

Mohamad Shahmardan

31-Jul-12 23:09

i need source code of replication database in c#. who can help me?

Re: Replication Help

Eddy Vluggen1-Aug-12 0:51

1-Aug-12 0:51

pashmakjoon wrote:
i need source code of replication database in c#.

This is not a take-out restaurant.

pashmakjoon wrote:
who can help me?

MSDN[^].

Bastard Programmer from Hell Suspicious | :suss:

Suspicious | :suss:

if you can't read my code, try converting it here[^]

General News Suggestion Question Bug Answer Joke Praise Rant Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.