Re: SQL Query - write all in one query - Database Discussion Boards

Re: SQL Query - write all in one query

Eddy Vluggen1-Aug-12 22:34

Eddy Vluggen

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:

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

Raman samineni

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

Eddy Vluggen

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 | :)

Bastard Programmer from Hell 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

Raman samineni

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

Raman samineni

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

Eddy Vluggen

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:

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

Raman samineni

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

Eddy Vluggen

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|

Bastard Programmer from Hell 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

Raman samineni

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

Eddy Vluggen

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

Raman samineni

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

Eddy Vluggen

8-Aug-12 12:12

You're welcome Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

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

SSRS

Arul R Ece31-Jul-12 23:57

Arul R Ece

31-Jul-12 23:57

Hi friends,

I want to know the future of SSRS in SQL .

Re: SSRS

Eddy Vluggen1-Aug-12 2:48

Eddy Vluggen

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:

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

Eddy Vluggen

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:

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

Re: Replication Help

Mohamad Shahmardan1-Aug-12 5:48

Mohamad Shahmardan

1-Aug-12 5:48

i can't find your code.
where is your code?

Re: Replication Help

Pete O'Hanlon1-Aug-12 6:03

Pete O'Hanlon

1-Aug-12 6:03

He left it under a rock outside. Have you gone out and looked for it?

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Re: Replication Help

Eddy Vluggen1-Aug-12 11:01

Eddy Vluggen

1-Aug-12 11:01

I didn't supply any; the relevant sections are in the manual, and it takes some study and configuration.

Bastard Programmer from Hell Suspicious | :suss:

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

Re: Replication Help

jschell1-Aug-12 6:21

jschell

1-Aug-12 6:21

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

Major databases have tools in the database itself which is used to replicate the database.

programmatically write changes back to Access 2007 using C#

Ming Luo31-Jul-12 5:42

Ming Luo

31-Jul-12 5:42

Dear all:

I am trying to use C# to communicate a small database build in Access 2007. I can read from the database and I can modify the values and save the changes back to database using the DataGridView. However, I am not sure how to modify the values programmatically.

Here is the code building the connection.

string g_ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TestDB.accdb";
       
DataSet g_ds= new DataSet();
OleDbConnection g_con = new OleDbConnection(g_ConnectionString);
OleDbDataAdapter  g_adapter = new OleDbDataAdapter("Select * from qrySuggestion", g_con);
g_adapter.Fill(g_ds, "qrySuggestion");

this.dataGridView1.DataSource = g_ds.Tables[0];
g_con.Close();

I have a button on the form and when the button is clicked, save the changes back to the database. The code is as follows:

public void SaveTable(string tblName, DataTable tbl)
 {
     g_adapter = new OleDbDataAdapter("select * from " + tblName, g_con);

     OleDbCommandBuilder bu = new OleDbCommandBuilder(g_adapter);

     g_adapter.UpdateCommand = bu.GetUpdateCommand();
     g_adapter.Update(tbl);

     tbl.AcceptChanges();

     g_con.Close();
 }

I can use the above code to read from the database and display the data in the DataGridView with no problem. And I can make modification in the DataGridView and save it back by calling the SaveTable method.

However, the problem is that I want to make modification programmatically and reflect the changes back to the database. What I did was to add two statement at the beginning of the SaveTable method as:

DataRow row = tbl.Rows[0];
row[0] = 30;

assuming the column 0 i.e. row[0] is a int field.

When I add those two statement at the beginning of the SaveTable method. An exception will occur at g_adapter.Update(tbl); the exception is something like:

Missing DataColumn"pk_ID" in SourceColumn "pk_ID" in DataTable "tblSuggestionBase"

Can anyone help me out with the problem please?

Thanks

Asura

split string in MySQL

Jassim Rahma31-Jul-12 3:23

Jassim Rahma

31-Jul-12 3:23

Hi,

I want to know how can I split spilt in MySQL stored procedure.

I have this string Jassim Loves Banana

I want to split it by space and add x to it so it will be

xJassim xLove xBanana

Re: split string in MySQL

Blue_Boy31-Jul-12 5:14

Blue_Boy

31-Jul-12 5:14

use replace as

SQL

SELECT REPLACE('Jassim Loves Banana', ' ', ' x');

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Re: split string in MySQL

Jassim Rahma31-Jul-12 11:14

Jassim Rahma

31-Jul-12 11:14

one more question please..

if I have the string as

jassim rahma

jassim ali rahma

or anything else

how can split that to be:

<pre>SELECT file_no, old_file_no, account_number, id_number, full_name, date_of_birth FROM patients WHERE MATCH(full_name) AGAINST('jassim' WITH QUERY EXPANSION) and MATCH(full_name) AGAINST('rahma' WITH QUERY EXPANSION) ORDER BY full_name;</pre>

Problem with MySQL Full-Text Search

Jassim Rahma29-Jul-12 10:49

Jassim Rahma

29-Jul-12 10:49

I am using the following statement:

SQL

SELECT * FROM patients WHERE MATCH(full_name) AGAINST('jassim ali' WITH QUERY EXPANSION) ORDER BY full_name;

what I want to display all records contains jassim and should also contains ali but getting totally different result! here are names I am getting in the result:

Abdul Rahma Salma
Ali Jassim
Jassim Rahma
Jassim Rahma
Mohd Ali Ahmed
Mohd Jassim Maki

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