SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

redd
Starting Member

6 Posts

Posted - 12/13/2012 :  02:09:20  Show Profile  Reply with Quote
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
2168 Posts

Posted - 12/13/2012 :  02:41:43  Show Profile  Reply with Quote
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
Go to Top of Page

redd
Starting Member

6 Posts

Posted - 12/13/2012 :  05:19:02  Show Profile  Reply with Quote
it works...thanks Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/13/2012 :  05:26:05  Show Profile  Reply with Quote
quote:
Originally posted by redd

it works...thanks Chandu


Welcome

--
Chandu
Go to Top of Page

redd
Starting Member

6 Posts

Posted - 12/14/2012 :  04:57:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/14/2012 :  05:03:57  Show Profile  Reply with Quote
can you explain what exactly is filter condition you're trying to implement using above where condition?

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

Go to Top of Page

redd
Starting Member

6 Posts

Posted - 12/14/2012 :  05:20:57  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/14/2012 :  05:22:28  Show Profile  Reply with Quote
hmm...i was asking to explain it in words, not post entire code

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

Go to Top of Page

redd
Starting Member

6 Posts

Posted - 12/14/2012 :  05:35:57  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/14/2012 :  06:01:02  Show Profile  Reply with Quote
why set '' as return value for one of branch ?

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

Go to Top of Page

redd
Starting Member

6 Posts

Posted - 12/17/2012 :  23:05:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000