With Cube key word - 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 2005 Forums

Transact-SQL (2005)

With Cube key word

Author

Topic

venkatkrishna
Starting Member

12 Posts

Posted - 2007-05-28 : 11:42:04

Hello,

I have the query written in the Oracle, i would like to convert this query into the sql server 2000. in this used the key word cube. can any one guide me to converting the same.
Oracle stamt:
SELECT cluster_cd, cluster_desc,
CASE
WHEN grouping (area_cd) = 1
THEN 'Total'
ELSE area_cd
END area_cd,
CASE
WHEN grouping (area_cd) = 1
THEN 'Total'
ELSE area_desc
END area_desc,
CASE
WHEN grouping (category) = 1
AND grouping (area_cd) = 0
THEN 'Total'
ELSE category
END category,
CASE
WHEN grouping (sub_category) = 1
AND grouping (category) = 0
THEN 'Total'
ELSE sub_category
END sub_category,
CASE
WHEN grouping (sub_category_desc) = 1
AND grouping (category) = 0
THEN 'Total'
ELSE sub_category_desc
END sub_category_desc,
COUNT (1) member_count
FROM (SELECT cluster_cd, cluster_desc,
CASE
WHEN t.team_cd IN ('K1','K2')
THEN 'NATL'
ELSE area_cd
END area_cd,
CASE
WHEN t.team_cd IN ('K1','K2')
THEN 'National'
ELSE area_desc
END area_desc,
CASE
WHEN role_cd IN ('PHR', 'TSR','AM','CEC')
THEN 'Field Representatives'
ELSE 'Field Management'
END category,
CASE
WHEN role_cd IN ('PHR', 'TSR')
THEN team_cd
WHEN role_cd IN ('VP', 'RC', 'DAO', 'SAA','CERC')
THEN NULL
ELSE role_cd
END sub_category,
CASE
WHEN role_cd IN ('PHR', 'TSR')
THEN team_desc
WHEN role_cd IN ('VP', 'RC', 'DAO', 'SAA','CERC')
THEN NULL
ELSE role_desc
END sub_category_desc
FROM da_ff_territory t
WHERE t.active_fg = 'Y'
AND t.territory_type_cd = 'FL'
--and t.role_cd <> 'TSR'
--and t.cluster_cd in ('MM', 'CV', 'NS', 'UR', 'APM' )
AND t.cluster_cd NOT IN ( 'CH','SM')
)
WHERE sub_category IS NOT NULL
GROUP BY (cluster_cd, cluster_desc),
cube ((area_cd, area_desc), category, (sub_category, sub_category_desc))
ORDER BY cluster_cd, area_cd, category, sub_category

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-28 : 14:30:58

Try Changing GROUP BY clause as follows:

GROUP BY 
  cluster_cd, cluster_desc, area_cd, area_desc, category,
  sub_category, sub_category_desc WITH CUBE

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

venkatkrishna
Starting Member

12 Posts

Posted - 2007-05-29 : 00:27:17

quote:
Originally posted by harsh_athalye

Try Changing GROUP BY clause as follows:

GROUP BY
cluster_cd, cluster_desc, area_cd, area_desc, category,
sub_category, sub_category_desc WITH CUBE

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Hello Harsh,
if i change the query it is not displaying the same results what the oracle query results. in the oracle stmt it was specified like GROUP BY (cluster_cd, cluster_desc),
cube ((area_cd, area_desc), category, (sub_category, sub_category_desc))
ORDER BY cluster_cd, area_cd, category, sub_category in brackets etc.will it give any difference if i change as specified in ur stmt.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 00:44:28

It's hard to tell without knowing what your expected output is. Please post some sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

venkatkrishna
Starting Member

12 Posts

Posted - 2007-05-30 : 05:31:25

Hello harsh,

I could not find the proper solution to convert the stmt to sqlserver 2000.can u help me how to convert the folllwing oracle stmt into sql server2000. here are the keywords used does not support in sqlserver sys_connect_by_path,start with ,connect by prior.

It is in urgent basis.

select f.area_cd,
f.area_desc,
f.region_cd,
f.region_desc,
f.territory_id,
f.region_office_city,
f.region_office_state,
f.territory_city,
f.territory_state,
f.role_cd,
fe.emplid,
fe.preferred_name,
fe.first_name,
fe.last_name,
fe.voice_mail,
fe.internet_address,
am_count,
cec_count,
am_count + cec_count total
from (select t.area_cd,
t.area_desc,
t.region_cd,
t.region_desc,
t.territory_id,
t.region_office_city,
t.region_office_state,
t.territory_city,
t.territory_state,
t.role_cd,
count (case
when h.role_cd = 'AM'
then 1
end) am_count,
count (case
when h.role_cd = 'CEM'
then 1
end) cec_count
from v_da_ff_territory t,
(select cluster_cd,
team_cd,
loc_id,
role_cd,
substr
(sys_connect_by_path (t.territory_id,
','),
2,
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
2
)
- 2
) rm_territory_id,
case
when level = 2
then territory_id
else substr
(sys_connect_by_path
(t.territory_id,
','
),
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
2
)
+ 1,
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
3
)
- instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
2
)
- 1
)
end dm_territory_id,
case
when level = 3
then territory_id
else substr
(sys_connect_by_path
(t.territory_id,
','
),
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
3
)
+ 1,
instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
4
)
- instr
(sys_connect_by_path
(t.territory_id,
','
),
',',
1,
3
)
- 1
)
end cem_territory_id
from v_da_ff_territory t
where cluster_cd = 'MM'
and t.active_fg = 'Y'
and t.territory_type_cd = 'FL'
--and t.role_cd <> 'TSR'
start with role_cd in ('MMRM', 'TM')
connect by prior territory_id = t.rpt_lvl1_terr) h
where t.cluster_cd = h.cluster_cd
and t.role_cd != 'RC'
and t.territory_id in
(rm_territory_id, dm_territory_id, cem_territory_id)
group by t.area_cd,
t.area_desc,
t.region_cd,
t.region_desc,
t.territory_id,
t.region_office_city,
t.region_office_state,
t.territory_city,
t.territory_state,
t.role_cd) f
left join
(select *
from v_da_field_employee fe1
where fe1.field_active = 'A') fe
on f.territory_id = fe.territory_id
order by area_cd, region_cd

Regards,
Krishna Murthy.K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-30 : 06:26:59

http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources