| Author |
 Topic  |
|
|
redd
Starting Member
6 Posts |
 Posted - 12/13/2012 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/13/2012 : 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 - 12/13/2012 : 05:19:02
|
| it works...thanks Chandu |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/13/2012 : 05:26:05
|
quote:
Originally posted by redd
it works...thanks Chandu
Welcome
--
Chandu |
|
|
|
redd
Starting Member
6 Posts |
Posted - 12/14/2012 : 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
India
47023 Posts |
Posted - 12/14/2012 : 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 - 12/14/2012 : 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
India
47023 Posts |
Posted - 12/14/2012 : 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 - 12/14/2012 : 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
India
47023 Posts |
Posted - 12/14/2012 : 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 - 12/17/2012 : 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 |
|
|
| |
Topic |
|
|
|
query
