Variable in WHERE CLAUSE 2

[JBats]

Hellow People,

I just want to ask if this statement is possible or there is a way to do same with this one because I tried this in Sybase SQL and it works.


SELECT TransCode, Description, Status = Case When Status_ID = '01' Then 'Active' else 'Inactive' end
From t_Trans WHERE Status = 'Active'

Please check on the variable field Status which I used it in the where clause condition. I tried this in MSSQL but it doesn't work.

JBats
Good is not better if not than best...

 

[travisbrown]

Select
TransCode,
Description,
CASE Status_ID WHEN '01' THEN 'Active' ELSE 'Inactive' END AS Status
FROM t_Trans
WHERE Status_ID = '01'

Though you wouldn't need the case statement because you are only showing the active records. The following SQL would return the desired result. You'd use the case if you were returning a recordset that wasn't filtered on the Status_ID field.

Select
TransCode,
Description,
'Active' AS Status
FROM t_Trans
WHERE Status_ID = '01'

 

[amrita418]

All variables in sql server have @ before them.

declare @Status varchar(20)

SELECT   TransCode, 
         Description, 
         @Status = Case When Status_ID = '01' Then 'Active' else 'Inactive' end
From     t_Trans 
WHERE    Status = 'Active'

Let us know if that solves your problem.
Regards,
AA

 

[amrita418]

I am sorry, I think I misread your question.

As travisbrown suggested, you do not need variable here.

Disregard my post.

 

[travisbrown]

Was that what you are looking for JBats? I was a bit confused by your question like amrita418.

 

[JBats]

Thanks for your rely, both of your example are correct but I'm looking for a way on how I can make the variable inside the query be included in the WHERE CLAUSE condition.

I will show you another example inorder for you to understand more on my question.

SELECT ItemCode, ItemDescription,
TotalSale = (SELECT SUM(SoldAmount) FROM T_Sales WHERE ItemCode = t_Product.ItemCode and CatCode = '1')
FROM t_Product
WHERE TotalSale > 0

I am not using a stored procedure so there's no way I can use and declare variable as what amrita418 says that all variables in sql server have @ before them.




JBats
Good is not better if not than best...

 

[travisbrown]

You don't need a variable.

SELECT b.ItemCode, b.ItemDescription, SUM(a.SoldAmount) AS SoldAmount
FROM T_Sales a RIGHT OUTER JOIN t_Product b ON a.ItemCode = b.ItemCode WHERE
 b.CatCode = 1
GROUP BY 
b.ItemCode, b.ItemDescription
HAVING SUM(a.SoldAmount) > 0

or 

Select * FROM
SELECT b.ItemCode, b.ItemDescription, SUM(a.SoldAmount) AS SoldAmount
FROM T_Sales a RIGHT OUTER JOIN t_Product b ON a.ItemCode = b.ItemCode WHERE
 b.CatCode = 1
GROUP BY 
b.ItemCode, b.ItemDescription)
WHERE SoldAmount > 0

If you really want to use a variable (variables aren't just for stored procedures; you can use them in Query Analyzer. It's just Enterprise Manager that balks.

DECLARE @sSQL decimal(10,2)
SET @sSQL = (SELECT SUM(SoldAmount) FROM T_Sales WHERE ItemCode = t_Product.ItemCode and CatCode = '1')

SELECT ItemCode, ItemDescription,
       @sSQL AS TotalSale FROM t_Product
WHERE @sSQL > 0

Or finally,

SELECT * FROM
(SELECT ItemCode, ItemDescription, (SELECT SUM(SoldAmount) FROM T_Sales WHERE ItemCode = t_Product.ItemCode and CatCode = '1') AS TotalSale
 FROM t_Product)
WHERE TotalSale > 0

 

[ESquared]

SELECT * FROM (
  SELECT
    TransCode,
    Description,
    Status = Case When Status_ID = '01' Then 'Active' else 'Inactive' end
  FROM t_Trans
  ) A
WHERE Status = 'Active'

but this is not as efficient as the first answer given to you, WHERE Status = '01'

But if you must have codes associated with values, why not pop them into a lookup table and do a join?

CREATE TABLE TranStatuses (
  Status_ID char(2) CONSTRAINT PK_TranStatuses PRIMARY KEY NOT NULL,
  StatusDesc varchar(20)
)

INSERT TranStatuses SELECT '01', 'Active' UNION SELECT '00', 'Inactive'

SELECT
  TransCode,
  Description,
  Status = StatusDesc
FROM
  t_Trans T
  INNER JOIN TranStatuses S ON T.Status_ID = S.StatusID
WHERE
  T.Status_ID = '01'
-- S.StatusDesc = 'Active' -- this is not preferred, as the other method will allow use of an index if there is one covering the status_id column in t_Trans.

 

[JBats]

This is the exact example I am looking for. Thanks a lot for sharing your knowledge it helps me a lot.


SELECT * FROM
(SELECT ItemCode, ItemDescription, (SELECT SUM(SoldAmount) FROM T_Sales WHERE ItemCode = t_Product.ItemCode and CatCode = '1') AS TotalSale
FROM t_Product)
WHERE TotalSale > 0



JBats
Good is not better if not than best...

 

[travisbrown]

Give ESquared a star too because he makes a good point about indexing.

 

[ESquared]

Efficiency matters. You can't just do things ways that are "easy" for the developer. To be a good SQL developer, you have to go for efficiency, too.