SQL Query - write all in one query
-
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
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
SqlCommandand execute them.-
The first Sql-statement will not work. Test it again, it lacks a
FROMclause. -
you could embed the count as a sub-select, like this;
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 :suss: if you can't read my code, try converting it here[^]
-
-
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
SqlCommandand execute them.-
The first Sql-statement will not work. Test it again, it lacks a
FROMclause. -
you could embed the count as a sub-select, like this;
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 :suss: if you can't read my code, try converting it here[^]
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
-
-
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
Hello sir, Just stick in between the select queries. I dont want use Union , Because this qry goes to in the program. Regards Raman
-
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
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 :)
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
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
SqlCommandand execute them.-
The first Sql-statement will not work. Test it again, it lacks a
FROMclause. -
you could embed the count as a sub-select, like this;
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 :suss: if you can't read my code, try converting it here[^]
hello sir, How all three statments be written as one query. Regards Raman
-
-
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
SqlCommandand execute them.-
The first Sql-statement will not work. Test it again, it lacks a
FROMclause. -
you could embed the count as a sub-select, like this;
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 :suss: if you can't read my code, try converting it here[^]
how I will put in the sql Command all three query Thanks Raman
-
-
how I will put in the sql Command all three query Thanks Raman
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 :suss: if you can't read my code, try converting it here[^]
-
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 :suss: if you can't read my code, try converting it here[^]
send me both, No problem Thanks Raman
-
send me both, No problem Thanks Raman
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
FROMstatement. 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 X|Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
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
SqlCommandand execute them.-
The first Sql-statement will not work. Test it again, it lacks a
FROMclause. -
you could embed the count as a sub-select, like this;
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 :suss: if you can't read my code, try converting it here[^]
hello sir, My OUT PUT Report was SuperName Agent Name Hours Trans Compl Thanks Raman
-
-
hello sir, My OUT PUT Report was SuperName Agent Name Hours Trans Compl Thanks Raman
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
SqlCommandor 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. -
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
SqlCommandor 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.yes, that is true, I need them in SQLCOMMAND or String query in the program I appreciated Raman
-
yes, that is true, I need them in SQLCOMMAND or String query in the program I appreciated Raman