May 18, 2010 at 9:23 am
Hi Guys
I have a table named earned_trans with three columns [msisdn],[claim_date], [claim_type]
Please help me to write the query for this(bascially a stored procedure)
So here i have to get the claim_date and claim_type using msisdn
select claim_date, claim_type where msisdn='1233'
and with this
if claim_date == null
then set result = "absent"
elseif claim_date != null
then if(claim_type == "bank")
then set result = "bank"
elseif(claim_type == "claim")
then set result = "claim"
Please help me to write this query
May 18, 2010 at 9:46 am
If I understand your logic correctly then this might be what you're looking for:
select claim_date, claim_type,
case when claim_date is null then 'absent'
when claim_date is not null and claim_type = 'bank' then 'bank'
when claim_date is not null and claim_type = 'claim' then 'claim'
end as SetResult
from earned_trans
where msisdn='1233'
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
May 18, 2010 at 9:52 am
Thank you very much, i will give a try with this, before that i need to add a condition when it doesnt reeturn any result then i have to set "Not Exist", can you please add in that query?
Sorry i am not a hardcore SQL dev, so i could not do this change, may be a small change.
May 18, 2010 at 10:00 am
pattamuthu (5/18/2010)
Thank you very much, i will give a try with this, before that i need to add a condition when it doesnt reeturn any result then i have to set "Not Exist", can you please add in that query?Sorry i am not a hardcore SQL dev, so i could not do this change, may be a small change.
select claim_date, claim_type,
case when claim_date is null then 'absent'
when claim_date is not null and claim_type = 'bank' then 'bank'
when claim_date is not null and claim_type = 'claim' then 'claim'
else 'Not Exist'
end as SetResult
from earned_trans
where msisdn='1233'
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
May 18, 2010 at 10:03 am
Trey Staker (5/18/2010)
pattamuthu (5/18/2010)
Thank you very much, i will give a try with this, before that i need to add a condition when it doesnt reeturn any result then i have to set "Not Exist", can you please add in that query?...select claim_date, claim_type,
case when claim_date is null then 'absent'
when claim_date is not null and claim_type = 'bank' then 'bank'
when claim_date is not null and claim_type = 'claim' then 'claim'
else 'Not Exist'
end as SetResult
from earned_trans
where msisdn='1233'
I'm not sure if I get it right, but if OP adds a condition that doesn't return any result then the 'else' condition will never be executed.
May 18, 2010 at 10:04 am
Thats great, will give a try, and will keep posted. Thank you very much
May 18, 2010 at 10:18 am
I gave a try now.. rjv_rnjn is correct.
It doesnt work, when there is no transaction in the table, but other cases are fine.
Thanks
May 18, 2010 at 10:26 am
I see. Are you looking for the stored proc to return just absent, bank claim or not exists or are you looking for the claim_date and claim_type aswell with the return set?
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
May 18, 2010 at 10:32 am
I'm not 100% sure what you're looking for still especially not having sample data and ddl. Assuming I'm understanding you try this:
if exists (select 1
from earned_trans
where msisdn='1233')
)
BEGIN
END
select
case when claim_date is null then 'absent'
when claim_date is not null and claim_type = 'bank' then 'bank'
when claim_date is not null and claim_type = 'claim' then 'claim'
else 'unknown'
end as SetResult
from earned_trans
where msisdn='1233'
ELSE
select 'Not Exists' as SetResult
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
May 18, 2010 at 11:08 am
pattamuthu (5/18/2010)
It doesnt work, when there is no transaction in the table, but other cases are fine.
Any specific reason why you would want the SQL query to return that text? To me it seems like if the result set is being used by an application front end then it should be the app's responsibility to check for number of results and show corresponding message (its my preference to code things that way).
If not then Trey's idea of check on existence of a record and then branching off would be the way to go.
May 18, 2010 at 11:11 am
Actually i am writing a stored procedure, so i expect it to return a String from it..
Thats why
May 19, 2010 at 4:34 am
I have written like this in my procedure
ALTER PROCEDURE [dbo].[O2_SERVICES]
-- Add the parameters for the stored procedure here
-- Input Parameters
@msisdn VARCHAR(255),
-- Output Parameters
@lastQuartRewardState VARCHAR(255) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--To get last Quarter state
if exists (select claim_date, claim_type from earned_transaction where et.msisdn = @msisdn)
BEGIN
select
case when claim_date is null
then SET @lastQuartRewardState = 'Present'
when claim_date is not null and claim_type = 'Bank'
then SET @lastQuartRewardState = 'Banked'
when claim_date is not null and claim_type = 'Claim'
then SET @lastQuartRewardState = 'Claimed'
else 'unknown'
END
ELSE
SET @lastQuartRewardState = 'Absent'
-- Insert statements for procedure here
END
GO
But it says
Incorrect syntax near the keyword 'SET'. There seems to be a problem
Then how to set in to the variable?
Please help
May 19, 2010 at 7:19 am
Please some one help me in this regard fast..
May 19, 2010 at 9:20 am
Pat,
SELECT and SET do not belong together.
SET allows you to do simple operations with variables
SET @date = GETDATE()
SET @int = 1
SELECT allows you to set variables from data in a database:
SELECT @top = TOP 1([Field]) FROM [Table] ORDER BY [Field]
Notice I didn't use SET in there anywhere.
The part of your query:
select
case when claim_date is null
then SET @lastQuartRewardState = 'Present'
when claim_date is not null and claim_type = 'Bank'
then SET @lastQuartRewardState = 'Banked'
when claim_date is not null and claim_type = 'Claim'
then SET @lastQuartRewardState = 'Claimed'
else 'unknown'
END
Should be written:
SELECT @lastQuartRewardState = CASE
WHEN claim_date IS NULL
THEN 'Present'
WHEN claim_date IS NOT NULL AND claim_type = 'Bank'
THEN 'Banked'
WHEN claim_date IS NOT NULL AND claim_type = 'Claim'
THEN 'Claimed'
ELSE 'unknown'
END
FROM earned_transaction
WHERE msisdn = @msisdn
Notice that "@lastQuartRewardState = " is part of the SELECT clause, and that the CASE goes after the =. Also notice that in the THEN and ELSE clause, there is only data. Also, because you are referencing a field (claim_date), you need a FROM clause to specify the table.
Hope that makes sense.
May 19, 2010 at 9:28 am
Because your stored procedure returns a single value instead of a rowset, I recommend using a FUNCTION rather than a PROCEDURE. Functions allow you a little more flexibility (like calling it from inside a SELECT statement), and is a little cleaner to call:
CREATE FUNCTION [dbo].[ufnO2_SERVICES] (
@msisdn VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
IF EXISTS (SELECT claim_date, claim_type FROM earned_transaction WHERE msisdn = @msisdn)
BEGIN
SELECT @lastQuartRewardState = CASE
WHEN claim_date IS NULL
THEN 'Present'
WHEN claim_date IS NOT NULL AND claim_type = 'Bank'
THEN 'Banked'
WHEN claim_date IS NOT NULL AND claim_type = 'Claim'
THEN 'Claimed'
ELSE 'unknown'
END
FROM earned_transaction
WHERE msisdn = @msisdn
END
ELSE
SET @lastQuartRewardState = 'Absent'
RETURN @lastQuartRewardState
END
GO
Functions have to be called from inside a SELECT statement (and you have to explicitly specify the schema):
SELECT @lqrs = dbo.ufnO2_SERVICES(@msisdn)
Or, if the MSISDNs were stored in a table:
SELECT
msisdn, dbo.ufnO2_SERVICES(msisdn) AS LastQuartRewardState
FROM
[msisdn_table]
Therein lies the flexibility of the function, I can call it once for every row in the table, and return the value as a field in a SELECT statement.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply