query - 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 2008 Forums

Transact-SQL (2008)

query

Author

Topic

redd
Starting Member

6 Posts

Posted - 2012-12-13 : 02:09:20

Hi...Im facing some problem when i migrate the sql from oracle to ms sql.when i execute the query the error occured is :

Msg 102, Level 15, State 1, Line 300
Incorrect syntax near ')'.

This is my first time doing ms ms sql.I hope there is some answer to solve my problem.Please help..Thanks

Below is my query:

select
tbl_a.division_id,
tbl_a.division_name,
tbl_a.department_name,
tbl_a.course_id,
tbl_a.course_name, tbl_a.price,
tbl_a.offering_id,
tbl_a.offering_start_date,
tbl_a.offering_end_date,
tbl_a.main_or_sub_category_name,
tbl_a.delivery_id,
tbl_a.delivery_type,
case
when tbl_a.ILT_type is null then
tbl_b.ilt_type
else
tbl_a.ILT_type
end as ILT,
tbl_a.ILT_type,
tbl_b.ilt_type,
tbl_a.training_hours,
tbl_a.completed_date,
tbl_a.action_status,
tbl_a.preferred_name,
tbl_a.staff_id,
tbl_a.job_title,
tbl_a.job_grade,
tbl_a.ic_no,
tbl_a.manager_username,
tbl_a.manager_name,
tbl_a.ethnicity,
tbl_a.gender,
tbl_a.staff_status
from(
select
sbu.parent_id as division_id,
(
select
bu.name2
from
tpv_pub_smp_business_unit bu
where
bu.id = sbu.parent_id
) as division_name,
sbu.name2 as department_name,
oap.offering_temp_id as course_id,
oap.course_title as course_name,
(
select
ent.amount
from
tpv_pub_pl_list_entry ent
where
oap.offering_temp_id = ent.part_id
) as price,
oap.offering_action_id as offering_id,
oap.offering_start_date as offering_start_date,
oap.end_date as offering_end_date,
fd.dt_id as delivery_id,
fd.name as delivery_type,
oap.custom3 as ILT_type,
(
select
fol.name
from
tp2.tpv_nlevel_folder fol,
tp2.FGT_GEN gen
where
fol.locale_id = 'local000000000000001'
and oap.offering_temp_id = gen.id2
and gen.id1 = fol.id
) as main_or_sub_category_name,
sum(oap.duration/60) as training_hours,
oap.completion_date as completed_date,
oap.action_status as action_status,
emp.custom0 as preferred_name,
emp.employee_no as staff_id,
emp.job_title as job_title,
emp.custom2 as job_grade,
emp.ss_no as ic_no,
(
select
person.username
from
tp2.tpv_pub_smp_employees person
where
emp.manager_id = person.id
) as manager_username,
(
select
person.custom0
from
tp2.tpv_pub_smp_employees person
where
emp.manager_id = person.id
) as manager_name,
emp.ethnicity as ethnicity,
case
when emp.gender = '0' then 'Male'
when emp.gender = '1' then 'Female'
when emp.gender = '2' then 'Unknown'
when emp.gender = '9' then 'Not specific'
end as gender,
case
when (emp.terminated_on is null or emp.terminated_on >= GETDATE()) then 'Active'
when emp.terminated_on <= GETDATE() then 'Inactive'
end as staff_status
from
tpv_pub_smp_off_action_profile ap,
tp2.lev_full_delivery fd,
tp2.tpv_pub_offer_action_prof oap,
cmv_pub_smp_person_internal pi,
tpv_pub_smp_business_unit sbu,
tpv_pub_pl_list_entry list,
tpv_pub_smp_employees emp,
tp2.tpv_job_type job
where
ap.id = oap.offering_action_id
and ap.delivery_type = fd.id
and fd.dt_id in (
'eqcat000000000000005',
'eqcat000000000000004',
'eqcat000000000000008',
'eqcat000000000000014',
'eqcat000000000000011',
'eqcat000000000000010',
'eqcat000000000000007',
'eqcat000000000001431')

and fd.locale_id = 'local000000000000001'
--and oap.end_date >= '2011-12-16'
--and oap.end_date <= '2011-12-16'
and oap.status != '400'
and ap.party_id = pi.id
and pi.company_id = sbu.id
and oap.offering_temp_id = list.part_id
and oap.party_id = emp.id
and emp.jobtype_id = job.id
and job.locale_id = 'local000000000000001'
group by sbu.name2,
sbu.parent_id,
oap.offering_temp_id,
oap.course_title,
oap.offering_action_id,
oap.offering_start_date,
oap.end_date,
fd.dt_id,
fd.name,
oap.custom3,
oap.completion_date,
oap.action_status,
emp.custom0,
emp.employee_no,
emp.job_title,
emp.custom2,
emp.ss_no,
emp.manager_id,
emp.ethnicity,
emp.gender,
emp.terminated_on
)tbl_a
inner join
(
select
offering_id,
delivery_type,
ilt_type
from
(
select
(
select
bu.name2
from
tpv_pub_smp_business_unit bu
where
bu.id = sbu.parent_id
) as division_name,
sbu.name2 as department_name,
oap.offering_temp_id as course_id,
oap.course_title as course_name,
oap.offering_action_id as offering_id,
(
select
ent.amount
from
tpv_pub_pl_list_entry ent
where
oap.offering_temp_id = ent.part_id
) as price,
oap.offering_start_date as offering_start_date,
oap.end_date as offering_end_date,
fd.dt_id as delivery_id,
fd.name as delivery_type,
(
select
offses.custom4
from
rpv_le_offering_session offses,
tpv_pub_base_registration reg
where
oap.offering_action_id = reg.offering_action_id
and reg.class_id = offses.id
and offses.locale_id = 'local000000000000001'
) as ILT_type,
(
select
fol.name
from
tp2.tpv_nlevel_folder fol,
tp2.fgt_gen gen
where
fol.locale_id = 'local000000000000001'
and oap.offering_temp_id = gen.id2
and gen.id1 = fol.id) as main_or_sub_category_name,
sum(oap.duration/60) as training_hours,
oap.completion_date as completed_date,
oap.action_status as action_status,
emp.custom0 as preferred_name,
emp.employee_no as staff_id,
emp.job_title as job_title,
emp.custom2 as job_grade,
emp.ss_no as ic_no,
(
select
person.username
from
tp2.tpv_pub_smp_employees person
where
emp.manager_id = person.id
) as manager_username,
(
select
person.custom0
from
tp2.tpv_pub_smp_employees person
where
emp.manager_id = person.id
) as manager_name,
emp.ethnicity as ethnicity,
case
when emp.gender = '0' then 'Male'
when emp.gender = '1' then 'Female'
when emp.gender = '2' then 'Unknown'
when emp.gender = '9' then 'Not specific'
end as gender
from
tpv_pub_smp_off_action_profile ap,
tp2.lev_full_delivery fd,
tp2.tpv_pub_offer_action_prof oap,
cmv_pub_smp_person_internal pi,
tpv_pub_smp_business_unit sbu,
tpv_pub_pl_list_entry list,
tpv_pub_smp_employees emp,
tp2.tpv_job_type job

where
ap.id = oap.offering_action_id
and ap.delivery_type = fd.id
and fd.dt_id in (
'eqcat000000000000005',
'eqcat000000000000004',
'eqcat000000000000008',
'eqcat000000000000014',
'eqcat000000000000011',
'eqcat000000000000010',
'eqcat000000000000007',
'eqcat000000000001431')
and fd.locale_id = 'local000000000000001'
--and oap.end_date >= '2011-12-16'
--and oap.end_date <= '2011-12-16'
and oap.status != '400'
and ap.party_id = pi.id
and pi.company_id = sbu.id
and oap.offering_temp_id = list.part_id
and oap.party_id = emp.id
and emp.jobtype_id = job.id
and job.locale_id = 'local000000000000001'

group by sbu.name2,
sbu.parent_id,
oap.offering_temp_id,
oap.course_title,
oap.offering_action_id,
oap.offering_start_date,
oap.end_date,
fd.dt_id,
fd.name,
oap.completion_date,
oap.action_status,
emp.custom0,
emp.job_title,
emp.custom2,
emp.ss_no,
emp.manager_id,
emp.ethnicity,
emp.gender,
emp.employee_no
)
)tbl_b
on
tbl_a.offering_id = tbl_b.offering_id
where division_id like
case when '' is null then '%'
else
''
end

order by tbl_a.department_name

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-13 : 02:41:43

I am not sure... but try once this

emp.gender,
emp.employee_no
) AS anyAlias
)tbl_b
on
tbl_a.offering_id = tbl_b.offering_id
where division_id like
case when '' is null then '%'
else
''
end

order by tbl_a.department_name

--
Chandu

redd
Starting Member

6 Posts

Posted - 2012-12-13 : 05:19:02

it works...thanks Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-13 : 05:26:05

quote:
Originally posted by redd

it works...thanks Chandu

Welcome

--
Chandu

redd
Starting Member

6 Posts

Posted - 2012-12-14 : 04:57:09

Hi...below is my sql:

on
tbl_a.offering_id = tbl_b.offering_id
where division_id like
case when '' is null then '%'
else
''
end

order by tbl_a.department_name

the problem occured when no data shown if i uncomment the query.If i comment the query,can get the data.What is the possibility the query can execute with data??Please help...Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 05:03:57

can you explain what exactly is filter condition you're trying to implement using above where condition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

redd
Starting Member

6 Posts

Posted - 2012-12-14 : 05:20:57

quote:
Originally posted by visakh16

can you explain what exactly is filter condition you're trying to implement using above where condition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

select
tbl_a.division_id,
tbl_a.division_name,
tbl_a.department_name,
tbl_a.course_id,
tbl_a.course_name, tbl_a.price,
tbl_a.offering_id,
tbl_a.offering_start_date,
tbl_a.offering_end_date,
tbl_a.main_or_sub_category_name,
tbl_a.delivery_id,
tbl_a.delivery_type,
case
when tbl_a.ILT_type is null then
tbl_b.ilt_type
else
tbl_a.ILT_type
end as ILT,
tbl_a.ILT_type,
tbl_b.ilt_type,
tbl_a.training_hours,
tbl_a.completed_date,
tbl_a.action_status,
tbl_a.preferred_name,
tbl_a.staff_id,
tbl_a.job_title,
tbl_a.job_grade,
tbl_a.ic_no,
tbl_a.manager_username,
tbl_a.manager_name,
tbl_a.ethnicity,
tbl_a.gender,
tbl_a.staff_status
from(
select
sbu.parent_id as division_id,
(
select
bu.name2
from
tpv_pub_smp_business_unit bu
where
bu.id = sbu.parent_id
) as division_name,
sbu.name2 as department_name,
oap.offering_temp_id as course_id,
oap.course_title as course_name,
(
select
ent.amount
from
tpv_pub_pl_list_entry ent
where
oap.offering_temp_id = ent.part_id
) as price,
oap.offering_action_id as offering_id,
oap.offering_start_date as offering_start_date,
oap.end_date as offering_end_date,
fd.dt_id as delivery_id,
fd.name as delivery_type,
oap.custom3 as ILT_type,
(
select
fol.name
from
tp2.tpv_nlevel_folder fol,
tp2.FGT_GEN gen
where
fol.locale_id = 'local000000000000001'
and oap.offering_temp_id = gen.id2
and gen.id1 = fol.id
) as main_or_sub_category_name,
sum(oap.duration/60) as training_hours,
oap.completion_date as completed_date,
oap.action_status as action_status,
emp.custom0 as preferred_name,
emp.employee_no as staff_id,
emp.job_title as job_title,
emp.custom2 as job_grade,
emp.ss_no as ic_no,
(
select
person.username
from
tp2.tpv_pub_smp_employees person
where
emp.manager_id = person.id
) as manager_username,
(
select
person.custom0
from
tp2.tpv_pub_smp_employees person
where
emp.manager_id = person.id
) as manager_name,
emp.ethnicity as ethnicity,
case
when emp.gender = '0' then 'Male'
when emp.gender = '1' then 'Female'
when emp.gender = '2' then 'Unknown'
when emp.gender = '9' then 'Not specific'
end as gender,
case
when (emp.terminated_on is null or emp.terminated_on >= GETDATE()) then 'Active'
when emp.terminated_on <= GETDATE() then 'Inactive'
end as staff_status
from
tpv_pub_smp_off_action_profile ap,
tp2.lev_full_delivery fd,
tp2.tpv_pub_offer_action_prof oap,
cmv_pub_smp_person_internal pi,
tpv_pub_smp_business_unit sbu,
tpv_pub_pl_list_entry list,
tpv_pub_smp_employees emp,
tp2.tpv_job_type job
where
ap.id = oap.offering_action_id
and ap.delivery_type = fd.id
and fd.dt_id in (
'eqcat000000000000005',
'eqcat000000000000004',
'eqcat000000000000008',
'eqcat000000000000014',
'eqcat000000000000011',
'eqcat000000000000010',
'eqcat000000000000007',
'eqcat000000000001431')

and fd.locale_id = 'local000000000000001'
--and oap.end_date >= '2011-12-16'
--and oap.end_date <= '2011-12-16'
and oap.status != '400'
and ap.party_id = pi.id
and pi.company_id = sbu.id
and oap.offering_temp_id = list.part_id
and oap.party_id = emp.id
and emp.jobtype_id = job.id
and job.locale_id = 'local000000000000001'
group by sbu.name2,
sbu.parent_id,
oap.offering_temp_id,
oap.course_title,
oap.offering_action_id,
oap.offering_start_date,
oap.end_date,
fd.dt_id,
fd.name,
oap.custom3,
oap.completion_date,
oap.action_status,
emp.custom0,
emp.employee_no,
emp.job_title,
emp.custom2,
emp.ss_no,
emp.manager_id,
emp.ethnicity,
emp.gender,
emp.terminated_on
)tbl_a
inner join
(
select
offering_id,
delivery_type,
ilt_type
from
(
select
(
select
bu.name2
from
tpv_pub_smp_business_unit bu
where
bu.id = sbu.parent_id
) as division_name,
sbu.name2 as department_name,
oap.offering_temp_id as course_id,
oap.course_title as course_name,
oap.offering_action_id as offering_id,
(
select
ent.amount
from
tpv_pub_pl_list_entry ent
where
oap.offering_temp_id = ent.part_id
) as price,
oap.offering_start_date as offering_start_date,
oap.end_date as offering_end_date,
fd.dt_id as delivery_id,
fd.name as delivery_type,
(
select
offses.custom4
from
rpv_le_offering_session offses,
tpv_pub_base_registration reg
where
oap.offering_action_id = reg.offering_action_id
and reg.class_id = offses.id
and offses.locale_id = 'local000000000000001'
) as ILT_type,
(
select
fol.name
from
tp2.tpv_nlevel_folder fol,
tp2.fgt_gen gen
where
fol.locale_id = 'local000000000000001'
and oap.offering_temp_id = gen.id2
and gen.id1 = fol.id) as main_or_sub_category_name,
sum(oap.duration/60) as training_hours,
oap.completion_date as completed_date,
oap.action_status as action_status,
emp.custom0 as preferred_name,
emp.employee_no as staff_id,
emp.job_title as job_title,
emp.custom2 as job_grade,
emp.ss_no as ic_no,
(
select
person.username
from
tp2.tpv_pub_smp_employees person
where
emp.manager_id = person.id
) as manager_username,
(
select
person.custom0
from
tp2.tpv_pub_smp_employees person
where
emp.manager_id = person.id
) as manager_name,
emp.ethnicity as ethnicity,
case
when emp.gender = '0' then 'Male'
when emp.gender = '1' then 'Female'
when emp.gender = '2' then 'Unknown'
when emp.gender = '9' then 'Not specific'
end as gender
from
tpv_pub_smp_off_action_profile ap,
tp2.lev_full_delivery fd,
tp2.tpv_pub_offer_action_prof oap,
cmv_pub_smp_person_internal pi,
tpv_pub_smp_business_unit sbu,
tpv_pub_pl_list_entry list,
tpv_pub_smp_employees emp,
tp2.tpv_job_type job

where
ap.id = oap.offering_action_id
and ap.delivery_type = fd.id
and fd.dt_id in (
'eqcat000000000000005',
'eqcat000000000000004',
'eqcat000000000000008',
'eqcat000000000000014',
'eqcat000000000000011',
'eqcat000000000000010',
'eqcat000000000000007',
'eqcat000000000001431')
and fd.locale_id = 'local000000000000001'
--and oap.end_date >= '2011-12-16'
--and oap.end_date <= '2011-12-16'
and oap.status != '400'
and ap.party_id = pi.id
and pi.company_id = sbu.id
and oap.offering_temp_id = list.part_id
and oap.party_id = emp.id
and emp.jobtype_id = job.id
and job.locale_id = 'local000000000000001'

group by sbu.name2,
sbu.parent_id,
oap.offering_temp_id,
oap.course_title,
oap.offering_action_id,
oap.offering_start_date,
oap.end_date,
fd.dt_id,
fd.name,
oap.completion_date,
oap.action_status,
emp.custom0,
emp.job_title,
emp.custom2,
emp.ss_no,
emp.manager_id,
emp.ethnicity,
emp.gender,
emp.employee_no
)
)tbl_b
on
tbl_a.offering_id = tbl_b.offering_id
where division_id like
case when '' is null then '%'
else
''
end

order by tbl_a.department_name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 05:22:28

hmm...i was asking to explain it in words, not post entire code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

redd
Starting Member

6 Posts

Posted - 2012-12-14 : 05:35:57

quote:
Originally posted by visakh16

hmm...i was asking to explain it in words, not post entire code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oppss..sorry.I want to filter division_id by organization

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 06:01:02

why set '' as return value for one of branch ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

redd
Starting Member

6 Posts

Posted - 2012-12-17 : 23:05:56

quote:
Originally posted by visakh16

why set '' as return value for one of branch ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

is it correct if my last code like this:

on
tbl_a.offering_id = tbl_b.offering_id
where division_id like
case when division_id is null then '%'
else
division_id
end

order by tbl_a.department_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources