Nested Transactions - what for?

Hi
Can someone explain what nested transactions can be used for? I know about
the outer an inner transaction but you still either commit or rollback all
transactions.
In Oracle you can start autonomous transaction within another transaction
and any commit or rollback statement affects its own transaction.
So - I am not looking at explanation how to create nested transactions but
what could be a practical use of them?I think that this is largely a leftover from the original Sybase design.
As you know, there are no nested transactions in SQL Server, semantically speaking.
Why did they put in the syntax then? I guess you had to be in that Sybase design meeting to be able
to answer that question. My guess is that it allow for code modules to call each other. One stored
proc programmer want to transaction-protect the code. The proc he is writing will call another proc
which is also transaction-protected. Stuff like that...
Note that savepoint is another matter. This gives the ability to perform partial rollbacks. This is
IMO much more useful then doing partial commits. I find the thought of a partial commit a bit scary,
but perhaps this is because the feature isn't available in SQL Server.
One scenario I would consider it useful, though, is when you want to log actions even if you
rollback the transaction. In SQL Server 2000, we can often achieve the same functionality logging to
a table variable (which survives a ROLLBACK).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Witold" <witoldi@.shaw.ca> wrote in message news:bWo8d.211357$%S.80172@.pd7tw2no...
> Hi
> Can someone explain what nested transactions can be used for? I know about
> the outer an inner transaction but you still either commit or rollback all
> transactions.
> In Oracle you can start autonomous transaction within another transaction
> and any commit or rollback statement affects its own transaction.
> So - I am not looking at explanation how to create nested transactions but
> what could be a practical use of them?
>

Nested Transactions - what for?

Hi
Can someone explain what nested transactions can be used for? I know about
the outer an inner transaction but you still either commit or rollback all
transactions.
In Oracle you can start autonomous transaction within another transaction
and any commit or rollback statement affects its own transaction.
So - I am not looking at explanation how to create nested transactions but
what could be a practical use of them?
I think that this is largely a leftover from the original Sybase design.
As you know, there are no nested transactions in SQL Server, semantically speaking.
Why did they put in the syntax then? I guess you had to be in that Sybase design meeting to be able
to answer that question. My guess is that it allow for code modules to call each other. One stored
proc programmer want to transaction-protect the code. The proc he is writing will call another proc
which is also transaction-protected. Stuff like that...
Note that savepoint is another matter. This gives the ability to perform partial rollbacks. This is
IMO much more useful then doing partial commits. I find the thought of a partial commit a bit scary,
but perhaps this is because the feature isn't available in SQL Server.
One scenario I would consider it useful, though, is when you want to log actions even if you
rollback the transaction. In SQL Server 2000, we can often achieve the same functionality logging to
a table variable (which survives a ROLLBACK).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Witold" <witoldi@.shaw.ca> wrote in message news:bWo8d.211357$%S.80172@.pd7tw2no...
> Hi
> Can someone explain what nested transactions can be used for? I know about
> the outer an inner transaction but you still either commit or rollback all
> transactions.
> In Oracle you can start autonomous transaction within another transaction
> and any commit or rollback statement affects its own transaction.
> So - I am not looking at explanation how to create nested transactions but
> what could be a practical use of them?
>
sql

Nested transactions

I am writing a program using VC++ 6.0 and SQL 2000 and I am trying to use nested transactions. I have 1 outer transaction and the 2 inner transactions are in sepetrate function calls inside the outer transaction. I have something like this:

BEGIN TRANSACTION;

if (!functioncall1()) // commit if function suceeds, otherwise rollback
{
Rollback Transaction;
return;
}

if (!functioncall2()) // commit if function suceeds, otherwise rollback
{
Rollback Transaction;
return;
}

COMMIT TRANSACTION ;

Both functions contain a complete transaction inside the function call. If either function fails however, I want to do a rollback of the entire transaction. This is not happening though. If functioncall1 suceeds and the transaction in that function commits, then if I do a rollback during functioncall2, the transaction in functioncall1 is not rolled back. This seems to be directly opposite of the SQL help for transaction. Am I missing something obvious here?This gets rather complicated to explain, but I'll give it a shot.

SQL transactions don't nest in the strict (relational algebra) sense of the word. When you "nest" SQL transactions, they form something more like a procedure call stack where the COMMIT behaves like a return and the ROLLBACK behaves something like throwing an execption. The first rollback to come along basically wipes you back to step 1, before the first BEGIN TRANSACTION occured.

There were reasons for this behavior, once upon a time. There is still a reasonable logical arguments for maintaining the behavior, even though it goes so badly against the mental model used by third generation programming tools (like VB, VC, C#, etc).

The simplest solution I see to your problem is to avoid nesting, and use a try/throw/catch model to allow the application side logic to match the database side.

-PatP|||I have tried using the nested transactions and I can get them to work in the SQL query analyzer, but when I try to do the same thing in the application, the rollback will undo the work done in the inner most transaction, but not to the outer transaction.|||Ah... If that is the case, please post the Transact-SQL for what you want to do. It will be much easier to help you translate the Transact-SQL to C than to guess how it is different from what you've posted. What you posted won't behave the way you want it to, because SQL transactions don't work that way.

-PatP|||When I call function 1, the function completes and the transaction is completed. Function 2 doesn't finish and a rollback occurs inside the function, but the changes that were done in function 1 never get undone.

The SQL that I can get to work in the query analyzer is:

BEGIN Transaction Test1
Begin Transaction Test2
Insert into TABLE2
Commit Transaction Test2

BEGIN Transaction Test3
Insert into TABLE3
Commit Transaction
Rollback Transaction

Here is the C++ code that I can't get to work:

void main ( )
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
if (!Function1( ) ) //If this function fails, then rollback
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

ExecuteSQL(_T("BEGIN TRANSACTION "));
if (!Function2( ) ) //If this function fails, then rollback
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

// commit transaction and return success
ExecuteSQL(_T("COMMIT TRANSACTION "));
return 1;

////////////////////////////////////////////////////////////////////////////////////
int Function1( )
{

sqlStatement.Format(_T("INSERT INTO table1( VARIABLE LIST) FROM TABLE1");

try
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
nRowCount = ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

if (nRowCount != 1)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

sqlStatement.Format(_T("DELETE FROM TABLE2 ");

try
{
nRowCount = ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
e->Delete();
return 0;
}


// commit transaction and return success
ExecuteSQL(_T("COMMIT TRANSACTION "));
return 1;
}

////////////////////////////////////////////////////////////////////////////////////
int Function2( )
{
// Clear any previous errors
ResetError();

// Start a transaction
try
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
}
catch (CException *e)
{
return 0;
}
CString sqlStatement;
sqlStatement.Format( _T("DELETE FROM TABLE2 ");

try
{
ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
e->Delete();
ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return 0;
}
}
sqlStatement.Format( _T( "INSERT INTO TABLE3( )"),
TRY
{
ExecuteSQL(sqlStatement);
}
CATCH( CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return 0;
}
END_CATCH

// commit the changes
TRY
{
ExecuteSQL( _T("COMMIT TRANSACTION"));
}
CATCH( CException, e)
{
return 0;
}
END_CATCH

return 1;
}

Nested Transaction: how to commit the outer SP even if the inner ones fails.

Since some days I'm facing problems with the nested transactions and I read the they are not fully supported in sql server 2005 so I'd need an help from a more experienced SS programming.

My outer SP must live inside a transaction. It calles an another SP but IT MUST NOT ROLLBACK in case the INNER SP fails.

My nested SP, let's call it INNERSP is quite complex because it involves INSERT-UPDATE-DELETE, mathematic calculation and calls other SP as well.

Of course I want to make the INNERSP error-proof and I'd like to wrap it inside a try..catch structure.

If my INNERSP is:

BEGIN TRY

Insert ... --this insert cause an error that is trapped in the BEGIN CATCH

BEGIN TRANSACTION
COMMIT

END TRY

BEGIN CATCH
ROLLBACK TRANSACTION

END CATCH

The Rollback statement roll back also the transaction of the caller, but for me it is not acceptable.

I was investigating on the SAVE TRANSACTION statement but if I use it the @.@.TRANCOUNT is not decremented and my OUTERSP ends with a @.@.TRANCOUNT that is not zero, but from what I know "Nothing is actually committed until @.@.TRANCOUNT = 0"

Any helps is more that welcome!!
Thank you

Marina B.

Try putting your begin transaction statement before the work that you want inside of the transaction.|||

Interesting problem. It appears that even if an error in the called procedure is caught, the calling procedure's transaction still knows about it somehow:

if object_id('x') is not null drop table x

go

create table x(x1 int)

go

if object_id('px') is not null drop proc px

go

create proc px as

begin try

insert x values ('x')

end try

begin catch

print 'caught px'

end catch

go

if object_id('py') is not null drop proc py

go

create proc py as

begin tran

begin try

exec px

print @.@.error

insert x values (2)

commit

end try

begin catch

print 'caught py'

print error_message()

rollback

end catch

go

exec py

select * from x

go

/*

caught px

0

caught py

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

*/

|||

Not surprisingly, this behavior is my design. More importantly, this only applies to "fatal" errors. Per BOL:

"A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block."

This script is changed from the one above: the error that occurs in px is not a "fatal" error (the insert of "x" into an integer column is considered a syntax error and is fatal), and after it is caught, the py procedure does complete the transaction, resulting in a new row being inserted into table x:

set nocount on

go

if object_id('x') is not null drop table x

go

create table x(x1 int primary key)

go

if object_id('px') is not null drop proc px

go

create proc px as

begin try

insert x values (1)

end try

begin catch

print 'caught px'

print error_message()

print xact_state()

end catch

go

if object_id('py') is not null drop proc py

go

create proc py as

begin tran

begin try

insert x values (1)

exec px

print @.@.error

commit

end try

begin catch

print 'caught py'

print error_message()

rollback

end catch

go

exec py

select * from x

go

/*

caught px

Violation of PRIMARY KEY constraint 'PK__x__177C9889'. Cannot insert duplicate key in object 'dbo.x'.

1

0

*/

|||

SQL Server does not support autonomous transactions. Only way is to write an extended SP/SQLCLR SP to make a loopback connection to the database and call the inner SP without enlisting in same transaction. This technique of course has lot of disadvantages and side-effects. For one, you could create a distributed deadlock that is undetectable by SQL Server. You might end up blocking your own transaction consuming valuable resources on the server and so on.

Another technique is to use a table variable to store the results from the inner SP. Since table variables do not participate in user transactions, any subsequent COMMIT or ROLLBACK will not affect the data in the table variable. You can then retrieve the rows from the table variable after the transaction or in the CATCH block successfully. This will work from SQL Server 2000 onwards except there is no TRY...CATCH there.

|||

Hello everybody,

thankx for the answers .

By the way I found another solution that solve my problem but because SQL server programming is not my main skill I would like to know what to you think about it.

select @.@.trancount, 'Entering'

begin transaction

begin try

-- Detect if the procedure was called from an active transaction and save that for later use.In the procedure, @.TranCounter = 0 means there was no active transaction and the procedure started one.

-- @.TranCounter > 0 means an active transaction was started before the procedure was called.

DECLARE @.TranCounter INT;

DECLARE @.i INT;

SET @.TranCounter = @.@.TRANCOUNT;

select @.TranCounter,'Entering s.p. 1st level from broker'

IF @.TranCounter > 0

BEGIN

-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION ProcedureSave;

select @.@.trancount, 'Save transaction SP 1st level'

END

ELSE

BEGIN

-- Procedure must start its own transaction.

BEGIN TRANSACTION;

select @.@.trancount, 'New transaction created on the s.p. 1st level from broker'

END

BEGIN TRY

set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE FIRST LEVEL S.P.

--this is an another nested SP

DECLARE @.TranCounterSP2 INT;

SET @.TranCounterSP2 = @.@.TRANCOUNT;

select @.TranCounterSP2,'Entering s.p. 2nd level from s.p. 1st level'

IF @.TranCounterSP2 > 0

BEGIN

-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION ProcedureSave2;

select @.@.trancount, 'Save transaction SP 2nd level'

END

ELSE

BEGIN

-- Procedure must start its own transaction.

BEGIN TRANSACTION;

select @.@.trancount, 'New transaction created on the s.p. 2nd level from sp 1st level'

END

BEGIN TRY

set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE SECOND LEVEL S.P.

IF @.TranCounterSP2 = 0

BEGIN

COMMIT TRANSACTION

END

select @.@.trancount, 'S.p. 2nd level was committed'

END TRY

BEGIN CATCH

select @.@.trancount, 'SP 2nd level transaction on going to be rolled back rolled back'

IF @.TranCounterSP2 = 0

-- Transaction started in procedure. Roll back complete transaction - included the outer one.

ROLLBACK TRANSACTION;

ELSE

IF XACT_STATE() <> -1

ROLLBACK TRANSACTION ProcedureSave2;select @.@.trancount, 'SP 2nd level transaction was rolled back'

END CATCH

--this is the end of an another nested SP

-- Get here if no errors; must commit any transaction started in the procedure, but not commit a transaction started before the transaction was called.

IF @.TranCounter = 0

-- @.TranCounter = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

select @.@.trancount, 'SP 1st level transaction on going to be rolled back rolled back'

-- An error occurred; must determine which type of rollback will roll back only the work done in the procedure.

IF @.TranCounter = 0

-- Transaction started in procedure. Roll back complete transaction (INCLUDED the outer TRANSACTION).

ROLLBACK TRANSACTION;

ELSE

-- Transaction started before procedure called, do not roll back modifications made before the procedure was called.

IF XACT_STATE() <> -1

-- If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.

ROLLBACK TRANSACTION ProcedureSave;

select @.@.trancount, 'SP 1st level transaction was rolled back'

END CATCH

commit transaction -- for the outer transaction

select @.@.trancount, 'Outer transaction was committed'

end try

begin catch

if @.@.trancount>0

begin

rollback transaction

select @.@.trancount, 'Outer transaction was rolled back'

end

end catch

select @.@.trancount, 'Outer'

If I try this script everything works as I want, somebody can also confirm me that the transaction are also properly define and I'm not going to execute code in the wrong one?

Thank you

Marina B.

Nested Table

I have a table nested inside another table. Within the nesting, I want my
data to show me the detail, and not do a grouping. Unfortunately, the inner
table wants to keep doing an aggregate of the data, but I want to show the
detail. So I have a part number that has multiple customers being impacted
by supply limitations, I want to pull the part number with all of its data as
a single record, and then have a nested group that shows all the customer
name, like this:
AAAA
Acme Inc.
Acme Botanical
Acme Logistics
BBBB
Acme Inc.
Acme Environmental
When I add fields to the nested table, I keep having aggregate functions
appear, and if I don't use the aggregates, and specify scope, the data
element is acting like it is pulling from the parent table.
Thanks for any info.
ShaneI have a similar problem. I have a line of header information, then
multiple lines of detail, all coming from the same data source. RS
documentation says you can nest data regions, but I haven't found any
examples. I tried nesting groups and defined the header group as the parent
of the detail group. But, like Shane, I found that the RS insisted on
treating the detail lines as aggregates.
Is there example somewhere of a nested group that produces output in the
form that Shane shows below?
Thanks
Al
Shane Castle" <ShaneCastle@.discussions.microsoft.com> wrote in message
news:DB333B89-E614-48FC-943B-73163C9B735B@.microsoft.com...
> I have a table nested inside another table. Within the nesting, I want my
> data to show me the detail, and not do a grouping. Unfortunately, the
inner
> table wants to keep doing an aggregate of the data, but I want to show the
> detail. So I have a part number that has multiple customers being
impacted
> by supply limitations, I want to pull the part number with all of its data
as
> a single record, and then have a nested group that shows all the customer
> name, like this:
> AAAA
> Acme Inc.
> Acme Botanical
> Acme Logistics
> BBBB
> Acme Inc.
> Acme Environmental
> When I add fields to the nested table, I keep having aggregate functions
> appear, and if I don't use the aggregates, and specify scope, the data
> element is acting like it is pulling from the parent table.
> Thanks for any info.
> Shane|||My work around for this is to have your first report get all the
parts. You would add just one table. The first detail row you place
the part fields. In your second detail row place a subreport.
Make another report for companies. place only a table into this report
and have company fields in a single row. You might have to mess with
page border width stuff.
Use the part id or whatever and pass that into the subreport.
What will happen is that for each part. You'll print your part fields,
then it'll go to the second detail line and run the subreport which
gets all your companies for that part id.
Maybe not the best way to do this, but it's very simple, easy, and
clean.
Shane Castle <ShaneCastle@.discussions.microsoft.com> wrote in message news:<DB333B89-E614-48FC-943B-73163C9B735B@.microsoft.com>...
> I have a table nested inside another table. Within the nesting, I want my
> data to show me the detail, and not do a grouping. Unfortunately, the inner
> table wants to keep doing an aggregate of the data, but I want to show the
> detail. So I have a part number that has multiple customers being impacted
> by supply limitations, I want to pull the part number with all of its data as
> a single record, and then have a nested group that shows all the customer
> name, like this:
> AAAA
> Acme Inc.
> Acme Botanical
> Acme Logistics
> BBBB
> Acme Inc.
> Acme Environmental
> When I add fields to the nested table, I keep having aggregate functions
> appear, and if I don't use the aggregates, and specify scope, the data
> element is acting like it is pulling from the parent table.
> Thanks for any info.
> Shane

Nested query

Hey guys,
I have this query i can't seem to get to work just write. Can anyone
help me out here?
SELECT Table1.*
FROM Table1 INNER JOIN (SELECT * FROM Table2)
ON (Table1.ProvID = Table2.ProvID) AND
(Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
-Scott
When you use a query as a dynamic table like this, you have to provide an
alias for it:
SELECT Table1.*
FROM Table1
INNER JOIN (SELECT * FROM Table2) As Table2 ON (Table1.ProvID =
Table2.ProvID) AND (Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
However, I suggest just doing a straight inner join on the tables:
SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table1.ProvID = Table2.ProvID AND Table1.VerifDate =
Table2.VerifDate
WHERE Table1.Type Like 'A%'
"Scott Elgram" wrote:

> Hey guys,
> I have this query i can't seem to get to work just write. Can anyone
> help me out here?
> SELECT Table1.*
> FROM Table1 INNER JOIN (SELECT * FROM Table2)
> ON (Table1.ProvID = Table2.ProvID) AND
> (Table1.VerifDate = Table2.VerifDate)
> WHERE Table1.Type Like 'A%'
> --
> -Scott
>
>

Nested query

Hey guys,
I have this query i can't seem to get to work just write. Can anyone
help me out here?
SELECT Table1.*
FROM Table1 INNER JOIN (SELECT * FROM Table2)
ON (Table1.ProvID = Table2.ProvID) AND
(Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
-ScottWhen you use a query as a dynamic table like this, you have to provide an
alias for it:
SELECT Table1.*
FROM Table1
INNER JOIN (SELECT * FROM Table2) As Table2 ON (Table1.ProvID =
Table2.ProvID) AND (Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
However, I suggest just doing a straight inner join on the tables:
SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table1.ProvID = Table2.ProvID AND Table1.VerifDate =
Table2.VerifDate
WHERE Table1.Type Like 'A%'
"Scott Elgram" wrote:

> Hey guys,
> I have this query i can't seem to get to work just write. Can anyone
> help me out here?
> SELECT Table1.*
> FROM Table1 INNER JOIN (SELECT * FROM Table2)
> ON (Table1.ProvID = Table2.ProvID) AND
> (Table1.VerifDate = Table2.VerifDate)
> WHERE Table1.Type Like 'A%'
> --
> -Scott
>
>

Nested query

Hey guys,
I have this query i can't seem to get to work just write. Can anyone
help me out here?
SELECT Table1.*
FROM Table1 INNER JOIN (SELECT * FROM Table2)
ON (Table1.ProvID = Table2.ProvID) AND
(Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
--
-ScottWhen you use a query as a dynamic table like this, you have to provide an
alias for it:
SELECT Table1.*
FROM Table1
INNER JOIN (SELECT * FROM Table2) As Table2 ON (Table1.ProvID =Table2.ProvID) AND (Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
However, I suggest just doing a straight inner join on the tables:
SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table1.ProvID = Table2.ProvID AND Table1.VerifDate =Table2.VerifDate
WHERE Table1.Type Like 'A%'
"Scott Elgram" wrote:
> Hey guys,
> I have this query i can't seem to get to work just write. Can anyone
> help me out here?
> SELECT Table1.*
> FROM Table1 INNER JOIN (SELECT * FROM Table2)
> ON (Table1.ProvID = Table2.ProvID) AND
> (Table1.VerifDate = Table2.VerifDate)
> WHERE Table1.Type Like 'A%'
> --
> -Scott
>
>

Nested Inner Joins

Hi folks,

I have three tables:

ActivityCalls, UserList, and People

UserList.Username and ActivityCalls.Username are 'INNER JOINED' to determine which user is attached to an activity.

What I want to do is go one step further and look up the People.PersonName from people.

Here's what I have so far but it doesn't seem to work... Am I on the wrong track here?

INNER JOIN (People INNER JOIN UserList ON UserList.User_Username=ActivityCalls.Username)
ON People.Person_ID=UserList.Person_ID

Any help would be greatly appreciated.

Thanks

Nick

Quote:

Originally Posted by nkew

Hi folks,

I have three tables:

ActivityCalls, UserList, and People

UserList.Username and ActivityCalls.Username are 'INNER JOINED' to determine which user is attached to an activity.

What I want to do is go one step further and look up the People.PersonName from people.

Here's what I have so far but it doesn't seem to work... Am I on the wrong track here?

INNER JOIN (People INNER JOIN UserList ON UserList.User_Username=ActivityCalls.Username)
ON People.Person_ID=UserList.Person_ID

Any help would be greatly appreciated.

Thanks

Nick

Hi Nick
This might work:

FROM UserList U
INNER JOIN ActivityCalls A
ON U.User_Username = A.Username
INNER JOIN People P
ON U.User_PersonID = P.Person_ID

negate RESULT SET, all inactive client

I' d like to list all inactive clients.
Inactive client is a client who hasn't had invoice for 2 months.

I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clients
addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !
=''). I select only invoices with date after the interesting date.

So now I can list all active clients but i can't negate this result
set to get all inactive clients:

SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
FROM vwDok4FSFZGrid
INNER JOIN adr__Ewid
ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiek tu
WHERE
adr__Ewid.adr_Nazwa !=''
AND
(dok_DataWyst >= convert(datetime,'10/03/2007'))
GROUP BY dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst

Is there any way to negate this set?

1. Sorry for my English
2. I would appreciate any help
:)Mike (darthvadertojabuahahahaha@.gmail.com) writes:

Quote:

Originally Posted by

I' d like to list all inactive clients.
Inactive client is a client who hasn't had invoice for 2 months.
>
I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clients
addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !
>=''). I select only invoices with date after the interesting date.
>
So now I can list all active clients but i can't negate this result
set to get all inactive clients:
>
SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
FROM vwDok4FSFZGrid
INNER JOIN adr__Ewid
ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiek tu
WHERE
adr__Ewid.adr_Nazwa !=''
AND
(dok_DataWyst >= convert(datetime,'10/03/2007'))
GROUP BY dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
>
Is there any way to negate this set?

A complete guess:

SELECT adr_Nazwa, adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP
FROM adr__Ewid a
WHERE a.adr_Nazwa <''
AND NOT EXISTS (SELECT *
FROM vwDok4FSFZGrid v
WHERE v.dok_PlatnikId = a.adr_IdObiektu
AND v.dok_DataWyst >= '20070310')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

negate RESULT SET, all inactive client

I' d like to list all inactive clients.
Inactive client is a client who hasn't had invoice for 2 months.
I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clients
addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !
=''). I select only invoices with date after the interesting date.
So now I can list all active clients but i can't negate this result
set to get all inactive clients:
SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
FROM vwDok4FSFZGrid
INNER JOIN adr__Ewid
ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiektu
WHERE
adr__Ewid.adr_Nazwa !=''
AND
(dok_DataWyst >= convert(datetime,'10/03/2007'))
GROUP BY dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
Is there any way to negate this set?
1. Sorry for my English
2. I would appreciate any help
:)I would write it like this ...
SELECT *
FROM CustomerAddressTable AS InactiveCustomers
WHERE NOT EXIST ( SELECT * FROM InvoiceView AS I
INNER JOIN CustomerAddressTable AS C
ON I.ID = C.ID
WHERE InactiveCustomers.ID = C.ID
AND I.Date >= Convert(datetime,'08/03/2007')
AND I.Date <= Convert(datetime,'10/03/2007')
)
Sorry I didn't use your fields, I didn't understand all of them; I hope this
gives you some ideas.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Mike" wrote:
> I' d like to list all inactive clients.
> Inactive client is a client who hasn't had invoice for 2 months.
> I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clients
> addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !
> =''). I select only invoices with date after the interesting date.
> So now I can list all active clients but i can't negate this result
> set to get all inactive clients:
> SELECT dok_PlatnikId, adr_Nazwa,
> adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
> FROM vwDok4FSFZGrid
> INNER JOIN adr__Ewid
> ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiektu
> WHERE
> adr__Ewid.adr_Nazwa !=''
> AND
> (dok_DataWyst >= convert(datetime,'10/03/2007'))
> GROUP BY dok_PlatnikId, adr_Nazwa,
> adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
> Is there any way to negate this set?
> 1. Sorry for my English
> 2. I would appreciate any help
> :)
>|||This might help you:
SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
FROM vwDok4FSFZGrid dok1
INNER JOIN adr__Ewid adr
ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiektu
WHERE adr__Ewid.adr_Nazwa !=''
AND NOT EXISTS(
SELECT * FROM vwDok4FSFZGrid dok2
WHERE dok2.dok_PlatnikId = dok1.dok_PlatnikId
AND dok2.dok_DataWyst >= convert(datetime,'10/03/2007')
)
GROUP BY dok_PlatnikId,
adr_Nazwa,adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP,
dok_DataWyst
=====================Regards,
Steve
www.foxville.ch
On Oct 4, 12:00 am, Mike <darthvadertojabuahahah...@.gmail.com> wrote:
> I' d like to list all inactive clients.
> Inactive client is a client who hasn't had invoice for 2 months.
> I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clients
> addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !
> =''). I select only invoices with date after the interesting date.
> So now I can list all active clients but i can't negate this result
> set to get all inactive clients:
> SELECT dok_PlatnikId, adr_Nazwa,
> adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
> FROM vwDok4FSFZGrid
> INNER JOIN adr__Ewid
> ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiektu
> WHERE
> adr__Ewid.adr_Nazwa !=''
> AND
> (dok_DataWyst >= convert(datetime,'10/03/2007'))
> GROUP BY dok_PlatnikId, adr_Nazwa,
> adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
> Is there any way to negate this set?
> 1. Sorry for my English
> 2. I would appreciate any help
> :)