ADODB текст запроса из файла #610858

можно ли исполнить sql запрос из файла, например "c:zapros.sql" т.е. не использовать а что-нибудь типа задача исполнить запрос из файла c:zapros.sql и загрузить его в ТЗ

нет

Опен(ТекстДок.ПолучитьТекст) тогда уж

работает но 1ска вылетает (

текст запроса  в студию

AI.ANAME AS ADVCOMMENT,    AI.POSTDISPACH,    (select t.SFNUM from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCNUM,    (select t.tdate from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCDATE  from    Accounts A, Accountsinfo AI, Servicetypes ST,      (select distinct S.ID,S.SRVTYPEID from services S) S,(select '' AS "UNAME",1 AS "SRVTYPEID" from dual /*union select '????. ?????: ',-1 from dual*/) SS  where  A.ID=AI.ID AND S.ID=A.ID AND    AI.ANAME AS ADVCOMMENT,    AI.CONTRACTNUM,    P.SUMM as SUMM,    AI.SFNEED,    P.QUANTITY as AMOUTH,    AI.POSTDISPACH,    (select t.SFNUM from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCNUM,    (select t.tdate from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCDATE  from    Accounts A, Accountsinfo AI, Servicetypes ST, services SS, payments P      --(select distinct S.ID,S.SRVTYPEID from ) S,(/*select '' AS "UNAME",1 AS "SRVTYPEID" from dual union*/ select '????. ?????: ' AS "UNAME",-1 AS "SRVTYPEID" from dual) SS  where  AND P.PDATE between :SD AND :ED)

в жабе запрос исполняется на ура

явно не текст запроса

да, там на русском, скопировалось коряво, сейчас подправлю

AI.ANAME AS ADVCOMMENT,    AI.POSTDISPACH,    (select t.SFNUM from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCNUM,    (select t.tdate from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCDATE  from    Accounts A, Accountsinfo AI, Servicetypes ST,      (select distinct S.ID,S.SRVTYPEID from services S) S,(select '' AS "UNAME",1 AS "SRVTYPEID" from dual /*union select 'Абон. плата: ',-1 from dual*/) SS  where  A.ID=AI.ID AND S.ID=A.ID AND    AI.ANAME AS ADVCOMMENT,    AI.CONTRACTNUM,    P.SUMM as SUMM,    AI.SFNEED,    P.QUANTITY as AMOUTH,    AI.POSTDISPACH,    (select t.SFNUM from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCNUM,    (select t.tdate from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCDATE  from    Accounts A, Accountsinfo AI, Servicetypes ST, services SS, payments P      --(select distinct S.ID,S.SRVTYPEID from ) S,(/*select '' AS "UNAME",1 AS "SRVTYPEID" from dual union*/ select 'Абон. плата: ' AS "UNAME",-1 AS "SRVTYPEID" from dual) SS  where  AND P.PDATE between :SD AND :ED)

AS "UNAME" что за кавычки в запросе?

согласен для 1с-ки они не приемлимы, но оракл их глотает на ура. Сейчас попробую без кавычек

не помогло, 1с-ка по-прежнему вылетает запрос без кавычек SELECT * from  (select    SS.SRVTYPEID*ST.SRVTYPEID AS SRVTYPEID,    SS.UNAME||ST.UNAME AS UNAME,    A.ID,      AI.ANAME AS ADVCOMMENT,    AI.POSTDISPACH,    (select t.SFNUM from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCNUM,    (select t.tdate from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCDATE  from    Accounts A, Accountsinfo AI, Servicetypes ST,      (select distinct S.ID,S.SRVTYPEID from services S) S,(select '' AS UNAME,1 AS SRVTYPEID from dual /*union select 'Абон. плата: ',-1 from dual*/) SS  where  A.ID=AI.ID AND S.ID=A.ID AND  select    -1*ST.SRVTYPEID AS SRVTYPEID,    'Абон. плата: '||ST.UNAME AS UNAME,    A.ID,      AI.ANAME AS ADVCOMMENT,    AI.CONTRACTNUM,    P.SUMM as SUMM, --    0 as DUR, --    'шт' as DUR_UNIT,    AI.SFNEED,    P.QUANTITY as AMOUTH,    AI.POSTDISPACH,    (select t.SFNUM from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCNUM,    (select t.tdate from transactions t where t.id = a.id and t.tdate = (select max(t.tdate) from transactions t where t.id = a.id) and t.SFNUM is not null and rownum=1) as DOCDATE  from    Accounts A, Accountsinfo AI, Servicetypes ST, services SS, payments P      --(select distinct S.ID,S.SRVTYPEID from ) S,(/*select '' AS UNAME,1 AS SRVTYPEID from dual union*/ select 'Абон. плата: ' AS UNAME,-1 AS SRVTYPEID from dual) SS  where  A.ID=AI.ID  AND P.PDATE between :SD AND :ED)

видимо на параметрах даты глючит between :SD AND :ED

переделал параметры всё заработало на может кому пригодится...

Wobland,shuhard - Спасибо!

двоеточия мешались? ну я всё не глядел, больно страшно. мог бы после кавычек сразу догадаться ;)

вот я и догадался после кавычек )))

Если НЕ Recordset.EOF Тогда может  Если НЕ Recordset.BOF Тогда

да вроде верно "current record position is after the last record"

блин...опять не туда смотрю верно. если не 1я запись то на первую.   shuhard спасибо.

я вижу тут люди знают больше меня.. скажите уж по пути, что не так? после открытия base.eof сразу истина.

не буду спорить, не силён в ADODB задача исполнить запрос из файла c:zapros.sql и загрузить его в ТЗ как то так получилось