Re: Выборки MSSQL
Добрый день!
Осуществили миграцию базы из IB в MS SQL 2000 SE SP3 средствами TCS.
В режиме выборок делаю фильтрацию:
«Вес заготовки»=’45.7’
Результат, данных нет! Проверяю, все есть.
Атрибут имеет вещественный тип.
SQL Profiler:
SELECT DISTINCT
PVMain.prj_id,
PVMain.prjlist_id,
PV0.prjpar_value PVF0,
PV1.prjpar_value PVF1,
cast( PV2.prjpar_value as double precision ) PVF2,
Projects.PRJ_ID,
Projects.PRJTREE_ID,
Projects.PRJTYPE_ID,
Projects.PRJ_NAME,
Projects.PRJ_NOTE,
Projects.PRJ_COMMENT,
Projects.PRJ_HAVEPRJ,
Projects.ROUTING_ID,
Prjtypes.PRJTYPE_NOTE,
Prjtypes.PRJTYPE_ICO,
Routing.ROUTING_NOTE,
PRJVERSION.PRJVER_ID,
PRJVERSION.start_state_id,
PRJVERSION.finish_state_id,
PRJVERSION.prjver_name,
PRJVERSION.prjver_number,
PRJVERSION.prjver_rem,
PRJVERSION.prjverstate_id,
PRJVERSION.PRJVER_ACT,
PRJVERSION.prjver_locked_by,
PRJVERSION.CREATOR,
PRJVERSION.CREATE_DATE,
PRJVERSION.CHANGER,
PRJVERSION.CHANGE_DATE,
PRJVERACTFILE.filetype_id,
PRJVERSTATES.prjverstate_name,
PRJVERSTATES.TCS_ACCESS,
LOCKUSR.user_name as LOCKUSR_NAME,
CR_USER.USER_NAME as CR_USERNAME
FROM
PRJPARVALUES_EX PVMain
LEFT JOIN PROJECTS ON PROJECTS.prj_id = PVMain.prj_id
LEFT JOIN PRJTREEtype_ACCESS PTTA ON ((Projects.PRJTREETYPE_ID=PTTa.PRJTREETYPE_ID)
AND(ptta.TCS_ACCESS > 0)AND(ptta.user_id=cast(user as integer)))
LEFT JOIN PRJTREE PrjTree ON (Projects.PRJTREE_ID = PrjTree.PRJTREE_ID)
left join prjverlists on prjverlists.prjverlist_id=PVMain.prjlist_id
left join PRJPARVALUES_EX PV0 on PV0.prj_id =PVMain.prj_id
and ((PV0.prjver_id is Null ) or (PV0.prjver_id=PVMain.prjver_id))
and ((PV0.prjlist_id is Null ) or (PV0.prjlist_id=PVMain.prjlist_id))
and PV0.prjpar_id=7
left join PRJPARVALUES_EX PV1 on PV1.prj_id =PVMain.prj_id
and ((PV1.prjver_id is Null ) or (PV1.prjver_id=PVMain.prjver_id))
and ((PV1.prjlist_id is Null ) or (PV1.prjlist_id=PVMain.prjlist_id))
and PV1.prjpar_id=8
left join PRJPARVALUES_EX PV2 on PV2.prj_id =PVMain.prj_id
and ((PV2.prjver_id is Null ) or (PV2.prjver_id=PVMain.prjver_id))
and ((PV2.prjlist_id is Null ) or (PV2.prjlist_id=PVMain.prjlist_id))
and PV2.prjpar_id=10
LEFT JOIN PRJTYPES Prjtypes ON (Projects.PRJTYPE_ID = Prjtypes.PRJTYPE_ID)
LEFT JOIN ROUTING Routing ON (Projects.ROUTING_ID = Routing.ROUTING_ID)
LEFT JOIN PRJVERSION ON (PVMain.prjver_id is null and PROJECTS.prj_id = PRJVERSION.prj_id) or (PVMain.prjver_id = PRJVERSION.prjver_id)
LEFT JOIN USERS CR_USER ON (PRJVERSION.CREATOR = CR_USER.USER_ID)
LEFT JOIN USERS LOCKUSR ON (PRJVERSION.PRJVER_LOCKED_BY = LOCKUSR.USER_ID)
LEFT JOIN PRJVERSTATES ON (PRJVERSION.PRJVERSTATE_ID = PRJVERSTATES.PRJVERSTATE_ID)
LEFT JOIN PRJVERACTFILE ON (PRJVERSION.PRJVER_ID = PRJVERACTFILE.PRJVER_ID) WHERE
(PTTa.PRJTREETYPE_ID IS NOT Null)
AND PVMain.prjpar_id in (7, 8, 10)
and ((PV0.prjpar_id=7)and(PV0.prjpar_value IS NOT NULL AND PV0.prjpar_value<>""))
and ((PV1.prjpar_id=8)and(PV1.prjpar_value IS NOT NULL AND PV1.prjpar_value<>""))
and ((PV2.prjpar_id=10)and(PV2.prjpar_value IS NOT NULL)) AND (PV2.prjpar_value=45.7)
Изменяю саму выбору, явно указываю условие «Вес заготовки»=’45.7’
SQL Profiler Trace смотрите выше.
Выходит сообщение: “SQL Server Error: Error converting data type varchar to numeric”.
В IB все работает.
Помогите?
Жду ваших рекомендаций.