Multiple fields in where statement

In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record. Below was my first attempt, which of course doesn't work.

Delete from oop_test where acct_no, curr_seq, acct_type in (select acct_no, curr_seq, acct_type from oop_temp)What about this?

Delete from oop_test
where exists(select 'ok' from oop_temp i where i.acct_no=oop_test.acct_no and ...)|||YES! THAT IS IT! Thanks a TON! I am still a bit confused about the use of the variable "i" in the query. Explain it's use to me. Maybe it is my lack of SQL skills.|||This can also be done with a simple join:

Delete oop_test
from oop_test
inner join oop_temp
on oop_test.acct_no = oop_test.acct_no
and oop_test.curr_seq = oop_test.curr_seq
and oop_test.acct_type = oop_test.acct_type

blindman|||I will have to give this a shot. While the first method works like a champ, I had a difficult time figuring it out. This is real straight forward.

THANKS!|||The "i" in Snail's code is just an alias for the table name oop_temp. Many coders assign shorter alias name for tables in their queries to reduce the amount of typing required. Otherwise, Snail's code just checks each record in oop_test to see is a matching record "exists" in the oop_temp based on the three columns specified.

Both methods work. I think the join method might be more efficient, though for small-to-midsize tables probably not enough to be noticable. Go with whichever method you find easiest to read.

blindman|||Actually, the part I had a problem with was the select 'ok' part. Do you know what that means?|||All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman|||Originally posted by blindman
All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman

I agree - there is no difference in performance between select 'OK' and select * inside exists, but 'OK' looks much better for me ;)|||So SSchuler, its just a matter of style, and lookin' good! :cool:

blindman|||HEHEH! Good one. Ya learn something new everyday. I can't believe that tripped me up like it did. Now that you point it out, it is quite obvious. Don't let anyone say that us computer jockeys don't have style! ;-)|||* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)|||Originally posted by ms_sql_dba
* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)

I believe that's changed...Where SELECT * is actually optimized to perform better.|||Brett is correct. Select * is optimized, and the columns are not expanded.

blindman|||Originally posted by SSchuler
In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record.

To find "a record in which three specific fields match the same three fields in another table," you would use a JOIN clause in a SELECT query.

To delete those fields, you use this select query as a sub-select in a DELETE query, something like this:

DELETE FROM victim WHERE victim_id IN
(SELECT id FROM
table1 A JOIN table2 B USING
A.F1 = B.F1 AND A.F2 = B.F2 AND A.F3 = B.F3
)

(Sub-select italicized for emphasis. Caution: extemporaneous SQL coding... do not try this at home.) ;-)|||A little late, sundial. Read other member's posts first!

blindman

multiple fields in SELECT WHERE IN

Hi All

I'm in the process of trying to write a query (in access 2000) that copys data between tables, then updates related tables.

The problem:

One of the tables is linked on 2 fields, and I need to do a where not in query, so I can't use a join.

Is it possible to do something along the lines of:

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

Any help much appreciated

Mark MiddlemistYES YOU CAN

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

this works fine, but check that field1 and ref1 should have same datatype etc.,|||This won't be upgradable to sql server though.
You could

select * from table1
where not exists
(select * from table2 where table1.field1 = table2.ref1 and table1.field2 = table2.ref2)

or

select table1.*
from table1 left outer join table2
on table1.field1 = table2.ref1 and table1.field2 = table2.ref2
where table1.field1 is null

these will still bring back results if the fields allow null whereas the in clause may well not.
In all cases the results may not be correct for nullable fields.

Multiple Datasets

I am trying to write an invoice report for. It will include details of both
companies involved and then list worked hours and expenses on a per project
basis. I need the worked hours to display in a table under each project and
the expenses in a seperate table under each project.
I have tried what seemed to be a simple way... Created on dataset for the
address info of both companies and then a second dataset which contains the
the expeneses and worked hours per project and then created a list for
containing the project name and 2 tables for holding the hours worked and
expenses repectively. I then used filters on each table to display only the
row types it was concerned, expense or hours. Doing this made it so the
parent list failed to render the project name for projects beyond the first.
A second method I though would be to have each table have it's own dataset
and then somehow filter it according to the parent data regions current
project, but can't figure out how to create the join between the 2 datasets.
Any help would be appreciate including maybe a totally different approach to
this problem.
-stanRead up on subreports. A subreport is just a normal report with parameters.
First get the report working with parameters. Then embed it, right click on
the report and set the parameter to the field you want it mapped to in the
main report. Very easy once you get the hang of it.
Bruce L-C
"Stan Huff" <no!_spam!_stanhuff@.yhaoo.com> wrote in message
news:OmVGkGwgEHA.216@.tk2msftngp13.phx.gbl...
> I am trying to write an invoice report for. It will include details of
both
> companies involved and then list worked hours and expenses on a per
project
> basis. I need the worked hours to display in a table under each project
and
> the expenses in a seperate table under each project.
> I have tried what seemed to be a simple way... Created on dataset for the
> address info of both companies and then a second dataset which contains
the
> the expeneses and worked hours per project and then created a list for
> containing the project name and 2 tables for holding the hours worked and
> expenses repectively. I then used filters on each table to display only
the
> row types it was concerned, expense or hours. Doing this made it so the
> parent list failed to render the project name for projects beyond the
first.
> A second method I though would be to have each table have it's own dataset
> and then somehow filter it according to the parent data regions current
> project, but can't figure out how to create the join between the 2
datasets.
> Any help would be appreciate including maybe a totally different approach
to
> this problem.
> -stan
>|||One other note. Filters bring over all the data and then filters it. If you
have a lot of data then this will be very very slow. It will particularly
kill you if you are developing off of a subset of the data and then you go
live with the real stuff which is exponentially bigger.
Bruce L-C
"Stan Huff" <no!_spam!_stanhuff@.yhaoo.com> wrote in message
news:OmVGkGwgEHA.216@.tk2msftngp13.phx.gbl...
> I am trying to write an invoice report for. It will include details of
both
> companies involved and then list worked hours and expenses on a per
project
> basis. I need the worked hours to display in a table under each project
and
> the expenses in a seperate table under each project.
> I have tried what seemed to be a simple way... Created on dataset for the
> address info of both companies and then a second dataset which contains
the
> the expeneses and worked hours per project and then created a list for
> containing the project name and 2 tables for holding the hours worked and
> expenses repectively. I then used filters on each table to display only
the
> row types it was concerned, expense or hours. Doing this made it so the
> parent list failed to render the project name for projects beyond the
first.
> A second method I though would be to have each table have it's own dataset
> and then somehow filter it according to the parent data regions current
> project, but can't figure out how to create the join between the 2
datasets.
> Any help would be appreciate including maybe a totally different approach
to
> this problem.
> -stan
>

Multiple data sources

If I write an SQL query I can select data from various database from within
the same query. Any idea how I can do that when creating a SRS report.?
ThanksThe best way is by creating a stored procedure on the SQL Server, which
picks up the data from the different databases. Then you use this stored
procedure in your report.
Kaisa M. Lindahl Lervik
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:O8Rh%23nmAHHA.3560@.TK2MSFTNGP04.phx.gbl...
> If I write an SQL query I can select data from various database from
> within the same query. Any idea how I can do that when creating a SRS
> report.?
> Thanks
>|||Great.
thanks very much.
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:es0rtfoAHHA.3604@.TK2MSFTNGP04.phx.gbl...
> The best way is by creating a stored procedure on the SQL Server, which
> picks up the data from the different databases. Then you use this stored
> procedure in your report.
>
> Kaisa M. Lindahl Lervik
> "Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
> news:O8Rh%23nmAHHA.3560@.TK2MSFTNGP04.phx.gbl...
>> If I write an SQL query I can select data from various database from
>> within the same query. Any idea how I can do that when creating a SRS
>> report.?
>> Thanks
>

multiple data flows

Easy: read a SQL table with 500 fields, transform, write to flat file using SSIS.

But, I have hundreds of transformations to define using Lookups and Aggregates, Derived Column transformations. I wan to group the data flow transformations in usable (reasonable size) groups (packages, containers, subroutines, whatever you want to call it).

I cannot figure out a simple easy way of doing this most "simple" obvious thing.

Am I the only one on the planet who needs to do this?

Thx.

Newbie.

Hi Paul,

No you are not the only one that has seen a need to modularize the data flows. The current product is the version 1.0 and unfortunately it does not give you an intuitive way to do this.

Currently, you may be able to achieve some level of modularization by breaking your data flows and connecting them through staging objects (raw files, flat files, temporary tables, etc).

We are actively looking into improving this experience, though.

It will be there in one of the future releases.

Thanks,

Bob

|||I think your question is how to break up what would ordinarily be a very large data flow (hundreds of transformations?!), into multiple smaller data flows.

If so, you would use Raw Files to save the data pipeline to the disk so one data flow can end and another can begin. The Raw File destination will write to a file on the disk at the end of one data flow and the Raw File source will read from it to begin the next data flow. The raw file contains the pipeline metadata, so you don't have to worry about defining your columns (500?!) each time.
|||Okay, this makes sense and I was looking to do exactly what the two responses above suggest. Just thought this should be a LOT easier than this... and it sounds like it will be some day...

In case anyone is listening, what I would really like is to highlight a set of (connected) data flows and right-click and select Save As Sub Data Flow... and then give it a name... That's all. I understand that the mechanics behind the scene makes this not so easy -but, whatever, it should be possible to do this. MS: please make it so.

Thanks !
|||Thanks!|||

Paul Des wrote:

Okay, this makes sense and I was looking to do exactly what the two responses above suggest. Just thought this should be a LOT easier than this... and it sounds like it will be some day...

In case anyone is listening, what I would really like is to highlight a set of (connected) data flows and right-click and select Save As Sub Data Flow... and then give it a name... That's all. I understand that the mechanics behind the scene makes this not so easy -but, whatever, it should be possible to do this. MS: please make it so.

Thanks !

Paul,

I don't think I'm giving too much away by saying this will appear in the product one day. I'm hoping beyond hope that it will be in katmai.

Note to MSFT, whatever it takes to get this into katmai - do it!!!!

-Jamie

|||

Paul - thanks for the feedback. We have been working on this as a key enhancement to the product and the experience is somewhat similar to what you describe above. As Jamie knows, not everything we're working on are for the next release, but it will not be very long before you have this available.

Multiple columns and row values

Hi. I am trying to write a single stored procedure which would trace the
changes made by a user on a table. I would like this implemented on multiple
tables having the most efficient code possible. Is it possible to browse to
a
table and extract all its columns (column_name from information_schema) and
get the row value for these columns having only a record id. I was able to
get the column_name but unable to make a sql statement retrieving the values
of the column_name.
Any help is appreciated.Can you post more detail? What do you mean "columns having only a record
id"? If you post actual DDL, sample data, and sample output, that would be
best...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
news:871EF4F4-B4A4-493D-BBC1-6787783C1BFF@.microsoft.com...
> Hi. I am trying to write a single stored procedure which would trace the
> changes made by a user on a table. I would like this implemented on
multiple
> tables having the most efficient code possible. Is it possible to browse
to a
> table and extract all its columns (column_name from information_schema)
and
> get the row value for these columns having only a record id. I was able to
> get the column_name but unable to make a sql statement retrieving the
values
> of the column_name.
> Any help is appreciated.
>|||OK. Sorry about that.. I have TABLE1 with COL1, COL2, COL3, COL4. COL1 is in
t
and the primary key. also I have TABLE2 with COL1, COL2, COL3, etc.. with
COL1 as primary key and an int. I want to write a stored procedure to extrac
t
a record from TABLE1 or TABLE2 (table_name being passed as parameter) with a
record id (COL1) and loops over the columns and its row values. So I can use
this stored procedure in these two tables or in any other table as long as
the primary key is int. better if the int (primary key) is eliminated as a
constraint as well..
so a query from information_schema would give me the column names (given the
table as a parameter) but how do i extract the row value of those columns if
i know the record primary key value (COL1).
Thanks.
"Adam Machanic" wrote:

> Can you post more detail? What do you mean "columns having only a record
> id"? If you post actual DDL, sample data, and sample output, that would b
e
> best...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
> news:871EF4F4-B4A4-493D-BBC1-6787783C1BFF@.microsoft.com...
> multiple
> to a
> and
> values
>
>|||"[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
news:D66A9835-AAAF-4811-8F6F-1473F095166A@.microsoft.com...
> OK. Sorry about that.. I have TABLE1 with COL1, COL2, COL3, COL4. COL1 is
int
> and the primary key. also I have TABLE2 with COL1, COL2, COL3, etc.. with
> COL1 as primary key and an int. I want to write a stored procedure to
extract
> a record from TABLE1 or TABLE2 (table_name being passed as parameter) with
a
> record id (COL1) and loops over the columns and its row values. So I can
use
> this stored procedure in these two tables or in any other table as long as
> the primary key is int. better if the int (primary key) is eliminated as a
> constraint as well..
Why do you want to do this? You're completely eliminating most of the
benefits of using stored procedures, and DBMSs in general -- keeping the
application out of the data management business! My advice to you is to
very carefully consider your motives for doing this -- I can guarantee that
you will not end up simplifying anything by tightly coupling your
application to your database (which is what this stored procedure will
accomplish). You can take that with however many grains of salt as you
choose, but you may want to search the archives of this group for lots of
threads about these kinds of techniques and the problems they invariably
cause.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||i know.. well. i have written a large application and i everything is almost
in place. I just need this user tracking history. who inserts what and who
updates which record.. and so on.. i can use triggers but that would mean
going to each one of the tables which is 100+. I want a single stored
procedure, called from a class that I can reuse on every page. So i need the
columns and the current values so i can record them in a table where it can
be retrieved in a report. but this has to go on a per column value.. and the
pages are a lot less that the tables..
"Adam Machanic" wrote:

> "[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
> news:D66A9835-AAAF-4811-8F6F-1473F095166A@.microsoft.com...
> int
> extract
> a
> use
>
> Why do you want to do this? You're completely eliminating most of the
> benefits of using stored procedures, and DBMSs in general -- keeping the
> application out of the data management business! My advice to you is to
> very carefully consider your motives for doing this -- I can guarantee tha
t
> you will not end up simplifying anything by tightly coupling your
> application to your database (which is what this stored procedure will
> accomplish). You can take that with however many grains of salt as you
> choose, but you may want to search the archives of this group for lots of
> threads about these kinds of techniques and the problems they invariably
> cause.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

Multiple BinaryWrite() calls

I have two byte[] arrays, objResult1 and objResult2, which contain data from
two separate calls to Render().
I would like to somehow write the results for both reports to the Reponse
object.
This is the code I have:
HttpResponse objResponse = System.Web.HttpContext.Current.Response;
objResponse.ClearContent();
objResponse.ClearHeaders();
string fileName = Path.GetFileName(this.ReportPath) +
".pdf";
objResponse.ContentType = this.strMimeType;
objResponse.AddHeader ("content-disposition",
"attachment; filename=\"" + fileName + "\"");
objResponse.BinaryWrite(objResult1);
objResponse.BinaryWrite(objResult2);
objResponse.Flush();
objResponse.Close();
Unfortunately when I do this, the contents of objResult1 are not visible
i.e. the 2nd call to BinaryWrite overwrites the first one.
How can i simply append the second byte array?
Any help will be much appreciated...
Thanks.You can combine two arrays by using static Array.Copy call. Just create a
new array sized as a sum of two and copy your array one by
one to the new one. Be aware, if the mime types of the webresponses are
different you'll get a garbled output.
Also, BinaryWrite should not override the previous write.
The reason of missing the first write is writing file header information
twice, thus only one will be visible, if you lucky enough, otherwise you can
get some garbage
I'd use frames or iframes, if needed to display output from two different
pages on a single one.
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:53A315A1-FFD7-48BF-99D0-84D63BD062C3@.microsoft.com...
>I have two byte[] arrays, objResult1 and objResult2, which contain data
>from
> two separate calls to Render().
> I would like to somehow write the results for both reports to the Reponse
> object.
> This is the code I have:
> HttpResponse objResponse => System.Web.HttpContext.Current.Response;
> objResponse.ClearContent();
> objResponse.ClearHeaders();
> string fileName = Path.GetFileName(this.ReportPath) +
> ".pdf";
> objResponse.ContentType = this.strMimeType;
> objResponse.AddHeader ("content-disposition",
> "attachment; filename=\"" + fileName + "\"");
> objResponse.BinaryWrite(objResult1);
> objResponse.BinaryWrite(objResult2);
> objResponse.Flush();
> objResponse.Close();
> Unfortunately when I do this, the contents of objResult1 are not visible
> i.e. the 2nd call to BinaryWrite overwrites the first one.
> How can i simply append the second byte array?
> Any help will be much appreciated...
> Thanks.