October 4, 2012 at 4:32 pm
I have a table with 2 columns. plan_type and signles (Y and N -- only 2 values).
If plan_type = static AND All signles are Y then Print 'A'
If plan_type = static AND All signles are either Y OR N then Print 'B'
If plan_type = static AND All signles are N then Print 'C'
I know it sounds simple but I am not sure the code for this.
If Plan_type = static AND select * from table where-- how to check if all signles in this table are Y only?? Whats the condition?
Please help. Thanks
October 4, 2012 at 4:39 pm
This is a compound problem. static is the easy part, it's consistent.
First, you need to know if you've got Y, N, or mixed. That can be handled with a compound aggregation.
The cte handles the selectivity, the second query deals with the selections.
;WITH cte AS
(SELECT
PlanID,
SUM( CASE signles WHEN 'Y' THEN 1 ELSE 0 END) AS SumY,
SUM( CASE signles WHEN 'N' THEN 1 ELSE 0 END) AS SumN
FROM
sometable
WHERE
plan_type = 'static'
GROUP BY
PlanID
)
SELECT
/*planinfo*/
PlanID,
CASE WHEN SumN <> 0 AND SumY <> 0 THEN 'B'
WHEN SumY > 0 THEN 'Y'
WHEN SumN > 0 THEN 'N'
ELSE ''
END AS ValueOutput
FROM
cte
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 4, 2012 at 5:50 pm
That's sounds great. Thanks but I dont want CTE. Common table expression is there any other way? Thanks a ton
October 4, 2012 at 6:09 pm
dallas13 (10/4/2012)
That's sounds great. Thanks but I dont want CTE. Common table expression is there any other way? Thanks a ton
All a CTE presents is an encapsulated subquery. Just cut and paste the select statement into where the cte is in the FROM clause and you'll get what you need. CTEs (unless recursive) are just to make the code read easier.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2012 at 9:04 am
Okay Craig, Thanks a lot. Please see below condition and the pieces I've created now I need to make them combined.
ii)in the case of Static plans
(1) if the hierarchy is not defined - then N
(2) else if all siNgles are mobile enabled - then Y
(3) else if some but not all siNgles are mobile - then P
(4) else (i.e.: all siNgles) are NOT mobile enabled - then N
iii)in the case of any other plan_type (ex.: Q,P,E,etc.) - it's N
So for Plan_type ='Static' and
(1) Hierarchy is not defined then -N (here is the piece for that)
IF this is the condition below
SELECT b.event_id
FROM dba.t_event b
WHERE not exists (select * from dba.t_events_in_plan a Where b.event_id = a.event_id and b.plan_type = 'S')
THEN PRINT'N'
(2),(3) and (4)
SELECT
s.Plan_type,
CASE WHEN SumN > 0 AND SumY > 0 THEN 'P'
WHEN SumY > 0 AND SumN = 0 THEN 'Y'
WHEN SumN > 0 AND SumY = 0 THEN 'N'
ELSE ''
END AS ValueOutput
FROM
(SELECT
e.Plan_type,
SUM( CASE e.jds_mobile_enabled WHEN 'Y' THEN 1 ELSE 0 END) AS SumY,
SUM( CASE e.jds_mobile_enabled WHEN 'N' THEN 1 ELSE 0 END) AS SumN
FROM
dba.v_event e
WHERE
e.plan_type = 'S'
GROUP BY e.Plan_type ) s
iii) in the case of any other plan_type (ex.: Q,P,E,etc.) - it's N
IF
select plan_type from dba.v_event where plan_type IN ('Q','P','E') THEN PRINT 'N'
I want to combine all 3 pieces now
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply