Prev: How do I handle a Transport Error
Next: dynamic SQL and temp tables
From: Quackhandle on 18 Jul 2008 11:25
Hi,

I have the following three SELECT statements:

----------------------------------------------------------------------------------------------------------------------------------------
SELECT
distinct
c.ChargeId,
a.AssetId,
e.EnumLiteralName as AssetStatus,
'1Cancelled' AS SiebelStage,
'N/A' AS SiebelReleaseType,
'N/A' AS RiskStatusFlag,
'N/A' AS MaintenanceFlag
FROM dbo.tblChargeIds c
INNER JOIN dbo.Assets a ON c.AssetId = a.AssetID
INNER JOIN dbo.Enumeration_Literals e ON a.AssetStatusID =
e.EnumLiteralID
LEFT JOIN dbo.WorkFlowCase wfc ON a.AssetID = wfc.AssetID
WHERE e.EnumLiteralName = 'DELETED'
;
SELECT
distinct
c.ChargeId,
a.AssetId,
e.EnumLiteralName as AssetStatus,
'2Rejected' AS SiebelStage,
'N/A' AS SiebelReleaseType,
'N/A' AS RiskStatusFlag,
'N/A' AS MaintenanceFlag
FROM dbo.tblChargeIds c
INNER JOIN dbo.Assets a ON c.AssetId = a.AssetID
INNER JOIN dbo.Enumeration_Literals e ON a.AssetStatusID =
e.EnumLiteralID
LEFT JOIN dbo.WorkFlowCase wfc ON a.AssetID = wfc.AssetID
WHERE e.EnumLiteralName = 'RETURNED'
;
SELECT
distinct
c.ChargeId,
a.AssetId,
e.EnumLiteralName as AssetStatus,
'3Released' AS SiebelStage,
'N/A' AS SiebelReleaseType,
'N/A' AS RiskStatusFlag,
'N/A' AS MaintenanceFlag
FROM dbo.tblChargeIds c
INNER JOIN dbo.Assets a ON c.AssetId = a.AssetID
INNER JOIN dbo.Enumeration_Literals e ON a.AssetStatusID =
e.EnumLiteralID
LEFT JOIN dbo.WorkFlowCase wfc ON a.AssetID = wfc.AssetID
WHERE e.EnumLiteralName = 'DISCHARGED' and wfc.TerminationDate IS
NOT NULL

----------------------------------------------------------------------------------------------------------------------------------------

I know I can roll those up into one SELECT using the CASE statementsm
however for the WHERE clause I have tried the following:

WHERE e.EnumLiteralName =

CASE e.EnumLiteralName

WHEN 'DELETED' THEN 'DELETED'
WHEN 'RETURNED' THEN 'RETURNED'
WHEN 'DISCHARGED' THEN 'DISCHARGED'

END

however this doesn't seem to give the same results as the three
separate statments. Any help would be much appreciated for a relative
noobie to T-SQL.

Thanks

qh

From: Plamen Ratchev on 18 Jul 2008 11:38
You do not need CASE to combine the result sets. Something like this will
do:

WHERE e.EnumLiteralName IN ('DELETED', 'RETURNED', 'DISCHARGED')

However, it is not clear what you are trying to achieve by combining the
statements. Plus one of the queries has an extra filter:

wfc.TerminationDate IS NOT NULL

To incorporate that you can use this:

WHERE e.EnumLiteralName IN ('DELETED', 'RETURNED')
OR (e.EnumLiteralName = 'DISCHARGED' AND wfc.TerminationDate IS NOT
NULL)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Dom on 18 Jul 2008 11:36
I think you put the case statement in the wrong place. The three
select statements are the same, except for SiebelStage, and that's
where the case should be.

Like this:

SELECT
distinct
c.ChargeId,
a.AssetId,
e.EnumLiteralName as AssetStatus,
case e.EnumLiteralname
when 'DELETED' then '1Cancelled'
when 'RETURNED' then '2Rejected'
when 'DISCHARGED' then '3Released'
end asSiebelStage,
...

And then remove the where statement. Of course, you through in a
monkey wrench with the last one which has a check on null, but you'll
have to work that out.

Dom





On Jul 18, 11:25 am, Quackhandle <quackhandle1...(a)yahoo.co.uk> wrote:
> Hi,
>
> I have the following three SELECT statements:
>
> ---------------------------------------------------------------------------­-------------------------------------------------------------
> SELECT
> distinct
> c.ChargeId,
> a.AssetId,
> e.EnumLiteralName as AssetStatus,
> '1Cancelled' AS SiebelStage,
> 'N/A' AS SiebelReleaseType,
> 'N/A' AS RiskStatusFlag,
> 'N/A' AS MaintenanceFlag
> FROM         dbo.tblChargeIds c
> INNER JOIN dbo.Assets a ON c.AssetId = a.AssetID
> INNER JOIN dbo.Enumeration_Literals e ON a.AssetStatusID =
> e.EnumLiteralID
> LEFT JOIN dbo.WorkFlowCase wfc ON a.AssetID = wfc.AssetID
> WHERE     e.EnumLiteralName = 'DELETED'
> ;
> SELECT
> distinct
> c.ChargeId,
> a.AssetId,
> e.EnumLiteralName as AssetStatus,
> '2Rejected' AS SiebelStage,
> 'N/A' AS SiebelReleaseType,
> 'N/A' AS RiskStatusFlag,
> 'N/A' AS MaintenanceFlag
> FROM         dbo.tblChargeIds c
> INNER JOIN dbo.Assets a ON c.AssetId = a.AssetID
> INNER JOIN dbo.Enumeration_Literals e ON a.AssetStatusID =
> e.EnumLiteralID
> LEFT JOIN dbo.WorkFlowCase wfc ON a.AssetID = wfc.AssetID
> WHERE     e.EnumLiteralName = 'RETURNED'
> ;
> SELECT
> distinct
> c.ChargeId,
> a.AssetId,
> e.EnumLiteralName as AssetStatus,
> '3Released' AS SiebelStage,
> 'N/A' AS SiebelReleaseType,
> 'N/A' AS RiskStatusFlag,
> 'N/A' AS MaintenanceFlag
> FROM         dbo.tblChargeIds c
> INNER JOIN dbo.Assets a ON c.AssetId = a.AssetID
> INNER JOIN dbo.Enumeration_Literals e ON a.AssetStatusID =
> e.EnumLiteralID
> LEFT JOIN dbo.WorkFlowCase wfc ON a.AssetID = wfc.AssetID
> WHERE     e.EnumLiteralName = 'DISCHARGED' and wfc.TerminationDate IS
> NOT NULL
>
> ---------------------------------------------------------------------------­-------------------------------------------------------------
>
> I know I can roll those up into one SELECT using the CASE statementsm
> however for the WHERE clause I have tried the following:
>
> WHERE  e.EnumLiteralName =
>
>         CASE e.EnumLiteralName
>
>         WHEN 'DELETED' THEN 'DELETED'
>         WHEN 'RETURNED' THEN 'RETURNED'
>         WHEN 'DISCHARGED' THEN 'DISCHARGED'
>
> END
>
> however this doesn't seem to give the same results as the three
> separate statments.  Any help would be much appreciated for a relative
> noobie to T-SQL.
>
> Thanks
>
> qh

From: Dom on 18 Jul 2008 11:45
Plamen Ratchev pointed something out that I had missed -- that you
might have more than just 'DELETED', 'RETURNED', and 'DISCHARGED'
records. Of course, then you need the where statement that he uses.
But as I read your original post, the problem you're having is with
the "SiebelStage" datapoint in the select statement.

Dom



On Jul 18, 11:38 am, "Plamen Ratchev" <Pla...(a)SQLStudio.com> wrote:
> You do not need CASE to combine the result sets. Something like this will
> do:
>
> WHERE e.EnumLiteralName IN ('DELETED', 'RETURNED', 'DISCHARGED')
>
> However, it is not clear what you are trying to achieve by combining the
> statements. Plus one of the queries has an extra filter:
>
> wfc.TerminationDate IS NOT NULL
>
> To incorporate that you can use this:
>
> WHERE e.EnumLiteralName IN ('DELETED', 'RETURNED')
>      OR (e.EnumLiteralName = 'DISCHARGED' AND wfc.TerminationDate IS NOT
> NULL)
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

From: Quackhandle on 21 Jul 2008 04:28
On Jul 18, 4:45 pm, Dom <dolivas...(a)gmail.com> wrote:
> Plamen Ratchev pointed something out that I had missed -- that you
> might have more than just 'DELETED', 'RETURNED', and 'DISCHARGED'
> records.  Of course, then you need the where statement that he uses.
> But as I read your original post, the problem you're having is with
> the "SiebelStage" datapoint in the select statement.
>
> Dom
>
> On Jul 18, 11:38 am, "Plamen Ratchev" <Pla...(a)SQLStudio.com> wrote:
>
>
>
> > You do not need CASE to combine the result sets. Something like this will
> > do:
>
> > WHERE e.EnumLiteralName IN ('DELETED', 'RETURNED', 'DISCHARGED')
>
> > However, it is not clear what you are trying to achieve by combining the
> > statements. Plus one of the queries has an extra filter:
>
> > wfc.TerminationDate IS NOT NULL
>
> > To incorporate that you can use this:
>
> > WHERE e.EnumLiteralName IN ('DELETED', 'RETURNED')
> >      OR (e.EnumLiteralName = 'DISCHARGED' AND wfc.TerminationDate IS NOT
> > NULL)
>
> > HTH,
>
> > Plamen Ratchevhttp://www.SQLStudio.com- Hide quoted text -
>
> - Show quoted text -


Guys, thanks very much for your replies, they have given me a base on
which to work from.


Much appreciated

qh.
 | 
Pages: 1
Prev: How do I handle a Transport Error
Next: dynamic SQL and temp tables