1. Мегаконкурс в апреле "Приведи друзей на codeby". Дарим деньги, подписку на журнал хакер и выдаем статус "Paid Access". Подробнее ...

    Скрыть объявление

Синтаксис Sql (access)

Тема в разделе "Остальные БД", создана пользователем korel, 5 июн 2008.

Наш партнер Genesis Hackspace
  1. korel

    korel Гость

    Помогите пожалуйста преобразовать этот скрипт под Access. Был бы очень благодарен за справочные материалы
    по особенностям синтаксиса запросов под Access


    select info_contact.LastName + ' ' + info_contact.FirstName + ' ' + info_contact.MiddleName as Name,
    info_company.name as companyname,
    category.name as categoryname,
    specialization.name as specializationname,
    department.name as departmentname,
    position.name as positionname,
    count(datediff(m,min(datefrom),max(datefrom))+1) as cntmes
    from info_task
    inner join info_taskstate on info_task.taskstate_id = info_taskstate.id and isfinish = 1
    left join info_contact on info_task.contact_id = info_contact.id
    left join info_company on info_task.company_id = info_company.id
    left join info_dictionary category on info_contact.category_id = category.id
    left join info_dictionary specialization on info_contact.specialization_id = specialization.id
    left join info_dictionary department on info_contact.department_id = department.id
    left join info_dictionary position on info_contact.position_id = position.id
    group by info_contact.LastName, info_contact.FirstName, info_contact.MiddleName, info_company.name, category.name, specialization.name,department.name, position.name
    order by 1,2
     
  2. etc

    etc Гость

    А что, оно в Access не работает? И что говорит?
     
  3. korel

    korel Гость

    Говорит - Ошибка синтаксиса(пропущен оператор) в выражении запроса ".

    Немножко поправил синтаксис(на sql работает как часы :) ), а на Access не хочет:

    select info_contact.LastName + ' ' + info_contact.FirstName + ' ' + info_contact.MiddleName as Name,
    info_company.name as companyname, category.name as categoryname, specialization.name as specializationname,
    department.name as departmentname, position.name as positionname,
    count(*) as cnt,
    datediff(m,min(datefrom),max(datefrom))+1 as cntmes
    from info_task
    inner join info_taskstate on info_task.taskstate_id = info_taskstate.id and isfinish = 1
    left join info_contact on info_task.contact_id = info_contact.id
    left join info_company on info_task.company_id = info_company.id
    left join info_dictionary category on info_contact.category_id = category.id
    left join info_dictionary specialization on info_contact.specialization_id = specialization.id
    left join info_dictionary department on info_contact.department_id = department.id
    left join info_dictionary position on info_contact.position_id = position.id
    group by info_contact.LastName, info_contact.FirstName, info_contact.MiddleName, info_company.name, category.name, specialization.name,department.name, position.name
    order by 1,2
     
  4. korel

    korel Гость

    С учетом специфики синтаксиса аксесса - но все равно выдает сообщение ошибка синтаксиса в операции join:
    буду очень благодарен если подскажете "где собака зарыта".

    select info_contact.LastName + ' ' + info_contact.FirstName + ' ' + info_contact.MiddleName as Name,
    info_company.name as companyname, category.name as categoryname, specialization.name as specializationname,
    department.name as departmentname, position.name as positionname,
    count(*) as cnt,
    datediff(m,min(datefrom),max(datefrom))+1 as cntmes
    from info_task inner join
    (info_taskstate left join(info_contact left join(info_company left join(info_dictionary category left join(info_dictionary specialization left join(info_dictionary department left join(info_dictionary position on info_contact.position_id = position.id)on info_contact.department_id = department.id) on info_contact.specialization_id = specialization.id) on info_contact.category_id = category.id)
    on info_task.company_id = info_company.id)on info_task.contact_id = info_contact.id)on info_task.taskstate_id = info_taskstate.id and isfinish = 1;
    group by info_contact.LastName, info_contact.FirstName, info_contact.MiddleName, info_company.name, category.name, specialization.name,department.name, position.name
    order by 1,2;
     
  5. korel

    korel Гость

    Microsoft SQL Server 2000
     
  6. korel

    korel Гость

    Всем спасибо!!!!!!!!!!!!!!!
    Помощь утопающих - дело рук самих утопающих!
    ТЕМА ЗАКРЫТА! :)
     
Загрузка...

Поделиться этой страницей