Simplify query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Simplify query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-26 : 03:18:18

I have a query in which I am sending a Boolean variable

On basis of that variable I just adding a Value in In clause
csmContainerStatus in ( 'O', 'P', 'X')

and for second case

csmContainerStatus in ( 'O', 'P', 'X', 'R')

I wanted to avoid multiple query.How can i do it?

if (@IsReadyToPayContainerIncluded = 'True' )
begin ;
WITH CTE
AS (SELECT csm.fkSegmentID,
csm.csmContainerID,
csmContainerLevel,
csmContainerStatus,
Upper(csmEntryPointActualOrDeliveryLocaleKey) as csmEntryPointActualOrDeliveryLocaleKey,
csmContainerGrossWeight,
csmNumberofCopies,
cqt.cqtDestinationEntry,
csm.csmContainerDestinationZip,
csm.csmIncludedInOtherDocumentation,
row_number() over (partition by csm.fkSegmentID, csm.csmContainerID ORDER BY cqt.cqtDestinationEntry DESC) as Rn
FROM SegmentRecord seg
INNER JOIN ContainerSummaryRecord csm
ON seg.fkJobID = csm.fkJobID
AND seg.segSegmentID = csm.fkSegmentID
inner Join ContainerQuantityRecord cqt
on cqt.fkJobID = csm.fkJobID
and cqt.fkContainerID = csm.csmContainerID
where ( csmContainerStatus in ( 'O', 'P', 'R', 'X' ) or csmContainerStatus is null ))
SELECT @JobID as JobID,
fkSegmentID as SegmentID,
csmContainerID as ContainerID,
csmContainerLevel as ContainerLevel,
csmContainerStatus as ContainerStatus,
csmEntryPointActualOrDeliveryLocaleKey as LocaleKeyWithLOCSuffix,
csmContainerGrossWeight as ContainerGrossWeight,
csmNumberofCopies as NumberofCopies,
cqtDestinationEntry,
csmContainerDestinationZip,
csmIncludedInOtherDocumentation
FROM CTE
WHERE Rn = 1
End
else
begin ;
WITH CTE
AS (SELECT csm.fkSegmentID,
csm.csmContainerID,
csmContainerLevel,
csmContainerStatus,
Upper(csmEntryPointActualOrDeliveryLocaleKey) as csmEntryPointActualOrDeliveryLocaleKey,
csmContainerGrossWeight,
csmNumberofCopies,
cqt.cqtDestinationEntry,
csm.csmContainerDestinationZip,
csm.csmIncludedInOtherDocumentation,
row_number() over (partition by csm.fkSegmentID, csm.csmContainerID ORDER BY cqt.cqtDestinationEntry DESC) as Rn
FROM SegmentRecord seg
INNER JOIN ContainerSummaryRecord csm
ON seg.fkJobID = csm.fkJobID
AND seg.segSegmentID = csm.fkSegmentID
inner Join ContainerQuantityRecord cqt
on cqt.fkJobID = csm.fkJobID
and cqt.fkContainerID = csm.csmContainerID
where ( csmContainerStatus in ( 'O', 'P', 'X' ) or csmContainerStatus is null ))
SELECT @JobID as JobID,
fkSegmentID as SegmentID,
csmContainerID as ContainerID,
csmContainerLevel as ContainerLevel,
csmContainerStatus as ContainerStatus,
csmEntryPointActualOrDeliveryLocaleKey as LocaleKeyWithLOCSuffix,
csmContainerGrossWeight as ContainerGrossWeight,
csmNumberofCopies as NumberofCopies,
cqtDestinationEntry,
csmContainerDestinationZip,
csmIncludedInOtherDocumentation
FROM CTE
WHERE Rn = 1
End

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-26 : 05:58:33

Use this expression instead:

csmContainerStatus in ( 'O', 'P', 'X', CASE WHEN @IsReadyToPayContainerIncluded = 'True' THEN 'R' END)

You won't need the IF...ELSE construct and can write it as a single query.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-03-26 : 08:27:15

Thanks

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources