A tricky code problem!

I have a report that looks like the following:

record record record null null record record
supress supress supress null record1 null supress
supress supress supress record2 null null supress

This is the best way to describe my data, its 3 rows of records and I want to show record1 and record2 on the top row. I have the other data beneath supressed. I am sure this maybe be possible using SQL code or using a loop or something but I have no idea where to start and have been trawling the web for answers or some code samples but have found none to date as it is quite a specific thing. Its tricky I know but could someone help me please?Hi,
I want to make a page break after section3 in crystal report(ver. 8) after displaying 5 records. I'm using .Dsr report. I am using the following code.

'*******************************
Set Report = New CrystalReport1
Report.Database.AddADOCommand db, cmd
For i = 1 To rs.Recordcount

Set txtObj = Report.Section2.AddTextObject(l, 150, vHight)
txtObj.Width = 300
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!ITEM_DESC, 400, vHight)
txtObj.Width = 2000
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!narmst_sItemName, 2800, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate, 4200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemQty, 5700, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 7100, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 8500, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

If IsNull(rs!VAT_AMT) Then
m = 0
Else
m = Val(rs!VAT_AMT)
End If

Set txtObj = Report.Section2.AddTextObject(m, 9600, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

Set txtObj = Report.Section2.AddTextObject(Val(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty + m), 11200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True

If i > 5 Then
Report.Section3.NewPageAfter = True
End If

rs.MoveNext
vHight = vHight + 250
l = l + 1
Next i
'**************************

But it is not working. Please Help me.

with regards,
Arindam

a substitute for UNION?

I'd like to change query:
SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)
to one SELECT like this
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2
or (say DM has columns A, B, C):
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, C
On 19 Feb, 20:46, bbl...@.op.pl wrote:
> I'd like to change query:
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE (condition1)
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE (condition2)
> to one SELECT like this
> SELECT DM.*, WasCondition1, WasCondition2 FROM DM
> WHERE (condition1) or (condition2)
> but how to fill in the WasConditionX column?
> The UNION version was bad because in case a row fulfilled 2
> conditions, it was repeated instead of joining them, such like this:
> SELECT DM.*, 'condition1', 'condition2' FROM DM
> WHERE condition1 AND condition2
> UNION
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE condition1 AND NOT condition2
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE NOT condition1 AND condition2
> or (say DM has columns A, B, C):
> SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
> (
> SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
> WHERE condition1
> UNION
> SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
> WHERE condition2
> ) AS DM
> GROUP BY A, B, C
Use a CASE expression. To adapt your second example:
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;
Always avoid using SELECT *.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||> Use a CASE expression. To adapt your second example:
> SELECT col1, col2, col3,
> CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
> CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
> WHERE (condition1) OR (condition2) ;
Well, then each condition will be checked twice -- performance
decreases (the conditions use subqueries)?

> Always avoid using SELECT *.
Why?
|||I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):
DECLARE @.pp INT
SET @.pp = 7
SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
FROM (
SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
)
)
GROUP BY [ID]
|||On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You are missing naming the derived table. Note the AS tbl at the end of the inner query:
> SELECT ...
> FROM
> (
> SELECT ... FROM...
> ) AS tbl
> WHERE ...
> GROUP BY...
Thanks!
Can I write a faster query?
|||Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.
SELECT SUM(Sent) AS Sent,
SUM(Received) AS Received,
[ID]
FROM (SELECT CASE WHEN DM.SenderID = @.pp
THEN 1
ELSE 0
END AS Sent,
CASE WHEN DM.ReceiverPersonID = @.pp
THEN 1
WHEN DM.ReceiverDepartmentID NOT IN
(SELECT PD.DepartmentID
FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp
AND PD.ObsoleteDate IS NULL)
THEN 0
WHEN DM.StationID IN
(SELECT PS.StationID
FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp
AND PS.ObsoleteDate IS NULL)
THEN 1
WHEN DM.InstructionNr IN
(SELECT I.InstructionNr
FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp
AND I.PUID IN
(SELECT PU.ID
FROM WSDMS..PUs PU))
THEN 1
ELSE 0
END AS Received,
DM.*
FROM WSDMS..DocumentMessages DM
WHERE DM.ObsoleteDate IS NULL) as X
GROUP BY [ID]
I left all the IN tests as IN tests, (though one was reversed to a NOT
IN). Another change that could be worth trying is to rewrite each one
as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.
Roy Harvey
Beacon Falls, CT
On 19 Feb 2007 13:21:45 -0800, bbla32@.op.pl wrote:

>I'll show you my query which doesn't want to compile for some reason
>(Incorrect syntax near the keyword GROUP):
>DECLARE @.pp INT
>SET @.pp = 7
>
>SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>FROM (
>SELECT 1 AS Sent, 0 AS Received,
>DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.SenderID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
>DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverPersonID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
>DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
>(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
>WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.StationID IN
>(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
>WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
>UNION
>SELECT 0 AS Sent, 1 AS Received,
>DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
>(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
>WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.InstructionNr IN
>(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
>WHERE I.PUID = @.pp AND I.PUID IN
>(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
>)
>)
>GROUP BY [ID]
|||On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Assuming I didn't make a mistake - the code is untested of course -
> the following might perform slightly better. Or not, a lot depends on
> the indexing and actual data.
Great example!

> as an EXISTS test. In any case the use of subqueries in the CASE
> should limit the number of times the subqueries are executed, which is
> where any performance gain will come from.
Well, wouldn't it execute slower since the subquery is within CASE?
I have indices only on ID columns for all tables.
|||On 19 Feb 2007 15:01:46 -0800, bbla32@.op.pl wrote:

>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
>Well, wouldn't it execute slower since the subquery is within CASE?
No reason why that should be an issue.
The best way to make the subqueries faster is to run them less. If
the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.
Roy Harvey
Beacon Falls, CT
|||> The best way to make the subqueries faster is to run them less. If
> the first WHEN is satisfied the subqueries are not run at all. If the
> first subquery matches, the second subquery is not run at all. In the
> original query every subquery was run against every row in the table -
> and every row was processed as many times as there were UNIONed
> SELECTs. So the idea behind moving them into the CASE is to run them
> once per incoming row, and then as few of them as is required.
That makes sense. I have a problem with your query though: in the
construct like below
SELECT CASE
WHEN DM.ReceiverDepartmentID NOT IN (1)
THEN 0
ELSE 1
END
when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?
|||> when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?
I changed to
WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
IN ...
and
WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...
I suppose it should be ok now.

a substitute for UNION?

I'd like to change query:
SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)
to one SELECT like this
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2
or (say DM has columns A, B, C):
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, COn 19 Feb, 20:46, bbl...@.op.pl wrote:
> I'd like to change query:
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE (condition1)
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE (condition2)
> to one SELECT like this
> SELECT DM.*, WasCondition1, WasCondition2 FROM DM
> WHERE (condition1) or (condition2)
> but how to fill in the WasConditionX column?
> The UNION version was bad because in case a row fulfilled 2
> conditions, it was repeated instead of joining them, such like this:
> SELECT DM.*, 'condition1', 'condition2' FROM DM
> WHERE condition1 AND condition2
> UNION
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE condition1 AND NOT condition2
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE NOT condition1 AND condition2
> or (say DM has columns A, B, C):
> SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
> (
> SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
> WHERE condition1
> UNION
> SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
> WHERE condition2
> ) AS DM
> GROUP BY A, B, C
Use a CASE expression. To adapt your second example:
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;
Always avoid using SELECT *.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> Use a CASE expression. To adapt your second example:
> SELECT col1, col2, col3,
> CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
> CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
> WHERE (condition1) OR (condition2) ;
Well, then each condition will be checked twice -- performance
decreases (the conditions use subqueries)?

> Always avoid using SELECT *.
Why?|||I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):
DECLARE @.pp INT
SET @.pp = 7
SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
FROM (
SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
)
)
GROUP BY [ID]|||You are missing naming the derived table. Note the AS tbl at the end of the
inner query:
SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bbla32@.op.pl> wrote in message news:1171920105.335960.271700@.m58g2000cwm.googlegroups.com..
.
> I'll show you my query which doesn't want to compile for some reason
> (Incorrect syntax near the keyword GROUP):
> DECLARE @.pp INT
> SET @.pp = 7
>
> SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
> FROM (
> SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.SenderID = @.pp
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverPersonID = @.pp
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
> AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
> AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @.pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
> )
> )
> GROUP BY [ID]
>|||On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You are missing naming the derived table. Note the AS tbl at the end of th
e inner query:
> SELECT ...
> FROM
> (
> SELECT ... FROM...
> ) AS tbl
> WHERE ...
> GROUP BY...
Thanks!
Can I write a faster query?|||bbla32@.op.pl wrote:
> On Feb 19, 10:31 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Thanks!
> Can I write a faster query?
A few tips:
- Only select the columns that you need in the derived table. IOW,
change "DM.*" to "DM.ID"
- Make sure you have proper indexes in place. For example on
DocumentMessages(SenderID,ObsoleteDate) and on
DocumentMessages(ReceiverPersonID,Obsole
teDate)
- Remove unnecessary IN clauses
Gert-Jan|||Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.
SELECT SUM(Sent) AS Sent,
SUM(Received) AS Received,
[ID]
FROM (SELECT CASE WHEN DM.SenderID = @.pp
THEN 1
ELSE 0
END AS Sent,
CASE WHEN DM.ReceiverPersonID = @.pp
THEN 1
WHEN DM.ReceiverDepartmentID NOT IN
(SELECT PD.DepartmentID
FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp
AND PD.ObsoleteDate IS NULL)
THEN 0
WHEN DM.StationID IN
(SELECT PS.StationID
FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp
AND PS.ObsoleteDate IS NULL)
THEN 1
WHEN DM.InstructionNr IN
(SELECT I.InstructionNr
FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp
AND I.PUID IN
(SELECT PU.ID
FROM WSDMS..PUs PU))
THEN 1
ELSE 0
END AS Received,
DM.*
FROM WSDMS..DocumentMessages DM
WHERE DM.ObsoleteDate IS NULL) as X
GROUP BY [ID]
I left all the IN tests as IN tests, (though one was reversed to a NOT
IN). Another change that could be worth trying is to rewrite each one
as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.
Roy Harvey
Beacon Falls, CT
On 19 Feb 2007 13:21:45 -0800, bbla32@.op.pl wrote:

>I'll show you my query which doesn't want to compile for some reason
>(Incorrect syntax near the keyword GROUP):
>DECLARE @.pp INT
>SET @.pp = 7
>
>SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>FROM (
>SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.SenderID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverPersonID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @.pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
> )
> )
>GROUP BY [ID]|||On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Assuming I didn't make a mistake - the code is untested of course -
> the following might perform slightly better. Or not, a lot depends on
> the indexing and actual data.
Great example!

> as an EXISTS test. In any case the use of subqueries in the CASE
> should limit the number of times the subqueries are executed, which is
> where any performance gain will come from.
Well, wouldn't it execute slower since the subquery is within CASE?
I have indices only on ID columns for all tables.|||On 19 Feb 2007 15:01:46 -0800, bbla32@.op.pl wrote:

>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
>Well, wouldn't it execute slower since the subquery is within CASE?
No reason why that should be an issue.
The best way to make the subqueries faster is to run them less. If
the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.
Roy Harvey
Beacon Falls, CT

a substitute for UNION?

I'd like to change query:

SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)

to one SELECT like this

SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)

but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:

SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2

or (say DM has columns A, B, C):

SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, COn 19 Feb, 20:46, bbl...@.op.pl wrote:

Quote:

Originally Posted by

I'd like to change query:
>
SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)
>
to one SELECT like this
>
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
>
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
>
SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2
>
or (say DM has columns A, B, C):
>
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, C

Use a CASE expression. To adapt your second example:

SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;

Always avoid using SELECT *.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Use a CASE expression. To adapt your second example:

Quote:

Originally Posted by

>
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;

Well, then each condition will be checked twice -- performance
decreases (the conditions use subqueries)?

Quote:

Originally Posted by

Always avoid using SELECT *.

Why?|||I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):

DECLARE @.pp INT
SET @.pp = 7

SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]

FROM (

SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @.pp

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @.pp

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
)

)

GROUP BY [ID]|||You are missing naming the derived table. Note the AS tbl at the end of the inner query:

SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bbla32@.op.plwrote in message news:1171920105.335960.271700@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):
>
DECLARE @.pp INT
SET @.pp = 7
>
>
SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>
FROM (
>
SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @.pp
>
UNION
>
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @.pp
>
UNION
>
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
>
UNION
>
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
)
>
)
>
GROUP BY [ID]
>

|||On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.comwr ote:

Quote:

Originally Posted by

You are missing naming the derived table. Note the AS tbl at the end of the inner query:
>
SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...

Thanks!

Can I write a faster query?|||bbla32@.op.pl wrote:

Quote:

Originally Posted by

>
On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.comwr ote:

Quote:

Originally Posted by

You are missing naming the derived table. Note the AS tbl at the end of the inner query:

SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...

>
Thanks!
>
Can I write a faster query?

A few tips:
- Only select the columns that you need in the derived table. IOW,
change "DM.*" to "DM.ID"

- Make sure you have proper indexes in place. For example on
DocumentMessages(SenderID,ObsoleteDate) and on
DocumentMessages(ReceiverPersonID,ObsoleteDate)

- Remove unnecessary IN clauses

Gert-Jan|||Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.

SELECT SUM(Sent) AS Sent,
SUM(Received) AS Received,
[ID]
FROM (SELECT CASE WHEN DM.SenderID = @.pp
THEN 1
ELSE 0
END AS Sent,
CASE WHEN DM.ReceiverPersonID = @.pp
THEN 1
WHEN DM.ReceiverDepartmentID NOT IN
(SELECT PD.DepartmentID
FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp
AND PD.ObsoleteDate IS NULL)
THEN 0
WHEN DM.StationID IN
(SELECT PS.StationID
FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp
AND PS.ObsoleteDate IS NULL)
THEN 1
WHEN DM.InstructionNr IN
(SELECT I.InstructionNr
FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp
AND I.PUID IN
(SELECT PU.ID
FROM WSDMS..PUs PU))
THEN 1
ELSE 0
END AS Received,
DM.*
FROM WSDMS..DocumentMessages DM
WHERE DM.ObsoleteDate IS NULL) as X
GROUP BY [ID]

I left all the IN tests as IN tests, (though one was reversed to a NOT
IN). Another change that could be worth trying is to rewrite each one
as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.

Roy Harvey
Beacon Falls, CT

On 19 Feb 2007 13:21:45 -0800, bbla32@.op.pl wrote:

Quote:

Originally Posted by

>I'll show you my query which doesn't want to compile for some reason
>(Incorrect syntax near the keyword GROUP):
>
>DECLARE @.pp INT
>SET @.pp = 7
>
>
>SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>
>FROM (
>
>SELECT 1 AS Sent, 0 AS Received,
>DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.SenderID = @.pp
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
>DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverPersonID = @.pp
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
>DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
>(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
>WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.StationID IN
>(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
>WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
>DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
>(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
>WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.InstructionNr IN
>(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
>WHERE I.PUID = @.pp AND I.PUID IN
>(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
>)
>
>)
>
>GROUP BY [ID]

|||On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.

Great example!

Quote:

Originally Posted by

as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.

Well, wouldn't it execute slower since the subquery is within CASE?
I have indices only on ID columns for all tables.|||On 19 Feb 2007 15:01:46 -0800, bbla32@.op.pl wrote:

Quote:

Originally Posted by

>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

>as an EXISTS test. In any case the use of subqueries in the CASE
>should limit the number of times the subqueries are executed, which is
>where any performance gain will come from.

>
>Well, wouldn't it execute slower since the subquery is within CASE?

No reason why that should be an issue.

The best way to make the subqueries faster is to run them less. If
the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.

Roy Harvey
Beacon Falls, CT|||The best way to make the subqueries faster is to run them less. If

Quote:

Originally Posted by

the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.

That makes sense. I have a problem with your query though: in the
construct like below

SELECT CASE
WHEN DM.ReceiverDepartmentID NOT IN (1)
THEN 0
ELSE 1
END

when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?|||when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?

I changed to

WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
IN ...

and

WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...

I suppose it should be ok now.|||On 19 Feb 2007 15:53:06 -0800, bbla32@.op.pl wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?

>
>
>I changed to
>
WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
>IN ...
>
>and
>
WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...
>
>I suppose it should be ok now.

That is the problem with not haveing the real table definitions and
test data to work with. Your correction is fine.

Roy Harvey
Beacon Falls, CT

a substitute for UNION?

I'd like to change query:
SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)
to one SELECT like this
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2
or (say DM has columns A, B, C):
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, COn 19 Feb, 20:46, bbl...@.op.pl wrote:
> I'd like to change query:
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE (condition1)
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE (condition2)
> to one SELECT like this
> SELECT DM.*, WasCondition1, WasCondition2 FROM DM
> WHERE (condition1) or (condition2)
> but how to fill in the WasConditionX column?
> The UNION version was bad because in case a row fulfilled 2
> conditions, it was repeated instead of joining them, such like this:
> SELECT DM.*, 'condition1', 'condition2' FROM DM
> WHERE condition1 AND condition2
> UNION
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE condition1 AND NOT condition2
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE NOT condition1 AND condition2
> or (say DM has columns A, B, C):
> SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
> (
> SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
> WHERE condition1
> UNION
> SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
> WHERE condition2
> ) AS DM
> GROUP BY A, B, C
Use a CASE expression. To adapt your second example:
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;
Always avoid using SELECT *.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> Use a CASE expression. To adapt your second example:
> SELECT col1, col2, col3,
> CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
> CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
> WHERE (condition1) OR (condition2) ;
Well, then each condition will be checked twice -- performance
decreases (the conditions use subqueries)?
> Always avoid using SELECT *.
Why?|||I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):
DECLARE @.pp INT
SET @.pp = 7
SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
FROM (
SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPersonID = @.pp
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
UNION
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepartmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionNr IN
(SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
)
)
GROUP BY [ID]|||You are missing naming the derived table. Note the AS tbl at the end of the inner query:
SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bbla32@.op.pl> wrote in message news:1171920105.335960.271700@.m58g2000cwm.googlegroups.com...
> I'll show you my query which doesn't want to compile for some reason
> (Incorrect syntax near the keyword GROUP):
> DECLARE @.pp INT
> SET @.pp = 7
>
> SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
> FROM (
> SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.SenderID = @.pp
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverPersonID = @.pp
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
> AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
> UNION
> SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
> AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
> AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @.pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
> )
> )
> GROUP BY [ID]
>|||On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You are missing naming the derived table. Note the AS tbl at the end of the inner query:
> SELECT ...
> FROM
> (
> SELECT ... FROM...
> ) AS tbl
> WHERE ...
> GROUP BY...
Thanks!
Can I write a faster query?|||bbla32@.op.pl wrote:
> On Feb 19, 10:31 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > You are missing naming the derived table. Note the AS tbl at the end of the inner query:
> >
> > SELECT ...
> > FROM
> > (
> > SELECT ... FROM...
> > ) AS tbl
> > WHERE ...
> > GROUP BY...
> Thanks!
> Can I write a faster query?
A few tips:
- Only select the columns that you need in the derived table. IOW,
change "DM.*" to "DM.ID"
- Make sure you have proper indexes in place. For example on
DocumentMessages(SenderID,ObsoleteDate) and on
DocumentMessages(ReceiverPersonID,ObsoleteDate)
- Remove unnecessary IN clauses
Gert-Jan|||Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.
SELECT SUM(Sent) AS Sent,
SUM(Received) AS Received,
[ID]
FROM (SELECT CASE WHEN DM.SenderID = @.pp
THEN 1
ELSE 0
END AS Sent,
CASE WHEN DM.ReceiverPersonID = @.pp
THEN 1
WHEN DM.ReceiverDepartmentID NOT IN
(SELECT PD.DepartmentID
FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @.pp
AND PD.ObsoleteDate IS NULL)
THEN 0
WHEN DM.StationID IN
(SELECT PS.StationID
FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @.pp
AND PS.ObsoleteDate IS NULL)
THEN 1
WHEN DM.InstructionNr IN
(SELECT I.InstructionNr
FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @.pp
AND I.PUID IN
(SELECT PU.ID
FROM WSDMS..PUs PU))
THEN 1
ELSE 0
END AS Received,
DM.*
FROM WSDMS..DocumentMessages DM
WHERE DM.ObsoleteDate IS NULL) as X
GROUP BY [ID]
I left all the IN tests as IN tests, (though one was reversed to a NOT
IN). Another change that could be worth trying is to rewrite each one
as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.
Roy Harvey
Beacon Falls, CT
On 19 Feb 2007 13:21:45 -0800, bbla32@.op.pl wrote:
>I'll show you my query which doesn't want to compile for some reason
>(Incorrect syntax near the keyword GROUP):
>DECLARE @.pp INT
>SET @.pp = 7
>
>SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>FROM (
>SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.SenderID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverPersonID = @.pp
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @.pp AND PS.ObsoleteDate IS NULL)
>UNION
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @.pp AND PD.ObsoleteDate IS NULL)
>AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @.pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @.pp)
> )
>)
>GROUP BY [ID]|||On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Assuming I didn't make a mistake - the code is untested of course -
> the following might perform slightly better. Or not, a lot depends on
> the indexing and actual data.
Great example!
> as an EXISTS test. In any case the use of subqueries in the CASE
> should limit the number of times the subqueries are executed, which is
> where any performance gain will come from.
Well, wouldn't it execute slower since the subquery is within CASE?
I have indices only on ID columns for all tables.|||On 19 Feb 2007 15:01:46 -0800, bbla32@.op.pl wrote:
>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@.snet.net> wrote:
>> as an EXISTS test. In any case the use of subqueries in the CASE
>> should limit the number of times the subqueries are executed, which is
>> where any performance gain will come from.
>Well, wouldn't it execute slower since the subquery is within CASE?
No reason why that should be an issue.
The best way to make the subqueries faster is to run them less. If
the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.
Roy Harvey
Beacon Falls, CT|||> The best way to make the subqueries faster is to run them less. If
> the first WHEN is satisfied the subqueries are not run at all. If the
> first subquery matches, the second subquery is not run at all. In the
> original query every subquery was run against every row in the table -
> and every row was processed as many times as there were UNIONed
> SELECTs. So the idea behind moving them into the CASE is to run them
> once per incoming row, and then as few of them as is required.
That makes sense. I have a problem with your query though: in the
construct like below
SELECT CASE
WHEN DM.ReceiverDepartmentID NOT IN (1)
THEN 0
ELSE 1
END
when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?|||> when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?
I changed to
WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
IN ...
and
WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...
I suppose it should be ok now.|||On 19 Feb 2007 15:53:06 -0800, bbla32@.op.pl wrote:
>> when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?
>
>I changed to
> WHEN DM.ReceiverDepartmentID IS NULL OR DM.ReceiverDepartmentID NOT
>IN ...
>and
> WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...
>I suppose it should be ok now.
That is the problem with not haveing the real table definitions and
test data to work with. Your correction is fine.
Roy Harvey
Beacon Falls, CT

A problem in Distributed Transaction in a procedure

-- I made local procedure:

create procedure proc_AccountFrom
@.client varchar(50) = null
,@.account varchar(50) = null
,@.amount money = null
as

set nocount on

update client03.Northwind.dbo.ClientAccount
set AccountAmount = AccountAmount - @.amount
where ClientName = @.client and AccountName = @.account

go

-- I also made a procedure on the server:

create procedure proc_AccountTo
@.client varchar(50) = null
,@.account varchar(50) = null
,@.amount money = null
as

set nocount on

update server01.Northwind.dbo.ClientAccount
set AccountAmount = AccountAmount + @.amount
where ClientName = @.client and AccountName = @.account

set nocount off

-- I made the following local procedure:

create procedure proc_AccountTransfer
@.client varchar(50) = null
,@.account varchar(50) = null
,@.amount money = null
as
set nocount on
set ansi_warnings on
set xact_abort on

begin distributed transaction

exec proc_AccountFrom 'Bishoy','Saving',1000

exec server01.Northwind.dbo.proc_AccountTo 'Bishoy','Saving',1000

commit transaction

go

-- The table code:

use northwind
go
CREATE TABLE [dbo].[ClientAccount]
(ClientID int IDENTITY (1,1) NOT NULL
,ClientName varchar (50) NOT NULL
,AccountName varchar (50) NOT NULL
,AccountAmount money NOT NULL
,constraint PK_ClientID primary key clustered (ClientID)
,constraint CK_Amount check (AccountAmount >= 0)
)
go
insert into dbo.ClientAccount values ('Bishoy','Checking',100000)
insert into dbo.ClientAccount values ('Bishoy','Saving',2000)
go

?

\r\n

-- But I received the following response:

\r\n

Server: Msg 7391, Level 16, State 1, Procedure proc_AccountTransfer, Line 12
The operation could not be performed because the OLE DB provider \'SQLOLEDB\' was unable to begin a distributed transaction. \r\n
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider \'SQLOLEDB\' ITransactionJoin::JoinTransacti on returned 0x8004d00a]. \r\n
?

\r\n

-- Although:

\r\n

The DTS was active on both servers ?-- ?and they was linked?

\r\n

--? and the tables present

\r\n

--
Thank you.
Bishoy

\r\n\r\n",0]);D(["ce"]);D(["ms","1f76"]);//-->

-- But I received the following response:

Server: Msg 7391, Level 16, State 1, Procedure proc_AccountTransfer, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransacti on returned 0x8004d00a].

-- Although:

The DTS was active on both servers -- and they was linked

-- and the tables present

By 'DTS' do you mean 'DTC'? The MSDTC to be precise?
First off, make sure that the DTC process is running on both systems. Second, make sure that Network DTC Access is checked on both systems.
Hope that helps.
|||Can have multiple causes, but first check your firewall, you'll have to insert a rule in it (or turn it of :s)
|||Yes, DTC was running on both servers.
But, how to make sure that Network DTC Access is checked on both systems?

A problem getting value out of Stored Procedure

Server management studio does not give error from following query, but output parameter (kokonaissumma) is always NULL. I tested it other ways, by making it return value then it worked. But that required changes to the query, so I really don't know. The problem query is the last.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END
return (SELECT count(*) FROM dbo.t_osto)
select @.kokonaissumma =sum(Yhteensa)FROM dbo.t_osto

sum(Yhteensa)FROM dbo.t_osto

your missing a space:

sum(Yhteensa) FROM dbo.t_osto

|||

Move your SELECTs before the RETURN.

create PROCEDURE [dbo].[kori2]( @.Tuotekoodivarchar(20),@.kokonaissummamoney output)ASBEGIN SET NOCOUNT ON;IFNOT EXISTS(SELECT *FROM dbo.t_ostoWHERE Tuotekoodi=@.Tuotekoodi)BEGIN INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,AlvFROM dbo.t_TuoteWHERE Tuotekoodi= @.TuotekoodiENDELSE BEGIN UPDATE dbo.t_ostoSET Maara=Maara+1WHERE Tuotekoodi=@.TuotekoodiENDselect @.kokonaissumma =sum(Yhteensa)FROM dbo.t_ostoEND

|||

It works. Thank you both. It looks like this now.

...

...

UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)
END

Regards

Leif

|||

You dont need the return statement. The count is being returned through the OUTPUT parameter.

|||

I see. I'll fix that too. Below is part of the query now, it has now all features what I planned.

--

--

select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto --total money
select @.tuotemaara =sum(Maara) FROM dbo.t_osto -- how many items

return (SELECT count(*) FROM dbo.t_osto) --how many lines (and no return)

END

I should learn some sql. It is my weakest point in ASP.NET. I looked at "Books on line", but even first page used so unfamiliar terms, I was not able to go much further than that. Well, search is there and today I found some tutorials in there.

Thanks

Leif

|||

Hi again

I tried my stored procedure without return. Like this. This is my latest a refresh only version.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
SELECT count(*) FROM dbo.t_osto

With this the return value was 0. That was not the correct value. I wonder what else is wrong in my code.

This gave correct value as before. Below.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)

Regards

Leif

a Null check for a single record

Hi,
I have a table that may contains Null filled columns.
I have a SP that do Update and insert.
How can i do a null check to avoid trying to update null fields?
let say Type_1 column is null, I need a condition that will do a quick check (single record is sufficient) to see that it has null value and to skip the update command:

CREATE PROCEDURE Lan_Insert_Data_Type
as
DECLARE @.Single_Rec int
Begin

-- ??? HERE comes the condition --

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Thanks

Yossi--Try This

CASE When Type_1 IS NULL
Then
--Do nothing
SET @.Single_Rec = @.Single_Rec

ELSE

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

END|||Originally posted by eschapir
--Try This

CASE When Type_1 IS NULL
Then
--Do nothing
SET @.Single_Rec = @.Single_Rec

ELSE

UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

END
Thaks for the reply
but its not working,
maybe i missed some thing|||/* something like this ? */

if not exists(
select *
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type=1 and LanTable.Type_1 is null
)
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1|||Originally posted by ispaleny
/* something like this ? */

if not exists(
select *
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num
where p.Data_Type=1 and LanTable.Type_1 is null
)
UPDATE Data_type
SET Product_Num = LanTable.ProductNum,
Data =LanTable.Type_1
FROM Data_Type p
JOIN LanTable
ON Lantable.ProductNum = p.Product_num where p.Data_Type =1

Works great...
Thanks mate

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
TIA,
ChrisR
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR
|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
--
TIA,
ChrisR> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
TIA,
ChrisR> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go

a few questions

Hi,
I am new to RSS and I have a few questions that I hope you can help me with.
1. I have a sp with parameter that gets a default value of null
@.misgeretid smallint=null and the where statement is as follows:
WHERE (misgeretcode = @.misgeretid or misgeretcode is null)
In rss I want to add a parameter but allow the user not to select a value
and if he doesn't select a value it will return null to the sp.
2.Also, I noticed that in the employee sales sample the employee dropdown
list gets a value <select a value>. I want to replace that label with all and
set the value to 0. Is it possible?
3. Are there any books on how to user rss with visual studion .net
preferablly vb.net?
ThanksAnswers inline.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"collie" <collie@.discussions.microsoft.com> wrote in message
news:ABC14A10-D68A-41AC-871A-B45B1E57E3B1@.microsoft.com...
> Hi,
> I am new to RSS and I have a few questions that I hope you can help me
> with.
> 1. I have a sp with parameter that gets a default value of null
> @.misgeretid smallint=null and the where statement is as follows:
> WHERE (misgeretcode = @.misgeretid or misgeretcode is null)
> In rss I want to add a parameter but allow the user not to select a value
> and if he doesn't select a value it will return null to the sp.
You need to enable the parameter to "allow nulls". This is done in the
parameter dialog box.
> 2.Also, I noticed that in the employee sales sample the employee dropdown
> list gets a value <select a value>. I want to replace that label with all
> and
> set the value to 0. Is it possible?
Yes, you need to make 0 the default value and add an "all" to the parameter
set.
> 3. Are there any books on how to user rss with visual studion .net
> preferablly vb.net?
Yes, there are several RS books available. See
http://www.microsoft.com/sql/reporting/techinfo/books.asp.
> Thanks
>