База на mysql получена после миграции из ASA 9. В приложение внес изменения в datawindow где использовались функции которых нет в MYSQL. Есть много sql запросов в самом коде приложения.
Подключение к базе идет с использованием ODBC
(пробовал MySQL ODBC 3.51 Driver и MySQL ODBC 5.1 Driver)
При выполнение запроса:
SELECT bruker.b_aktiv_mobil
INTO :ls_aktiv
FROM bruker
WHERE bruker.b_bruker = :ls_brukernavn
USING gtrans_login;
билдер вылетает.
Запрос успешно выполняется в DataBase painter
Заранее благодарен за помощь.

запрос:

SELECT BRUKER.B_PASSORD
INTO :ls_riktigpassord
FROM BRUKER
WHERE b_bruker = :brukernavn
USING atrans_login;

информация из trace
(4d73a48): PREPARE:
(4d73a48): select BRUKER.B_PASSORD FROM BRUKER USING atrans_login(DBI_PARSE) (0.888 MS / 1110.445 MS)
(4d73a48): *** ERROR 1064 ***(rc -1) : [MySQL][ODBC 3.51 Driver][mysqld-5.1.55-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING atrans_login' at line 1
(4d73a48): CANCEL:(DBI_CANCEL) (0.003 MS / 1110.448 MS)
(4d73a48): PREPARE:
(4d73a48): select BRUKER.B_PASSORD FROM BRUKER (DBI_PARSE) (0.786 MS / 1111.234 MS)
(4d73a48): PREPARE:
(4d73a48): select BRUKER.B_PASSORD FROM BRUKER (DBI_PREPARE) (0.068 MS / 1111.302 MS)

буду благодарен за помощь.

следующий вопрос:
-- Ошибки при вызове функций COUNT() , SUM()

(29b4008): select max ( kl_id ) from kortliste (DBI_PARSE) (0.665 MS / 1094.087 MS)
(29b4008): *** ERROR 1630 ***(rc -1) : [MySQL][ODBC 3.51 Driver][mysqld-5.1.55-community]FUNCTION sk_base.max does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
(29b4008): PREPARE:
(29b4008): SELECT COUNT ( o_faktura_skrevet ) FROM ordre WHERE o_faktura_skrevet =' ' (DBI_PARSE) (0.771 MS / 1094.858 MS)
(29b4008): *** ERROR 1630 ***(rc -1) : [MySQL][ODBC 3.51 Driver][mysqld-5.1.55-community]FUNCTION sk_base.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
(29b4008): PREPARE:
(29b4008): select sum ( pc_issued_amount ) from paymentcoupon where pc_issued_order =0 (DBI_PARSE) (0.735 MS / 1095.593 MS)
(29b4008): *** ERROR 1630 ***(rc -1) : [MySQL][ODBC 3.51 Driver][mysqld-5.1.55-community]FUNCTION sk_base.sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
(29b4008): (DBI_CURRENT_OF_CURSOR) (0.003 MS / 1095.596 MS)

буду благодарен.

А в исходнике запроса в Powerscript случайно коментариев нет? - Нет. Очень редко встречаются в DW.

В ASA есть команда "LIST". А есть что то похожее в MYSQL? Спасибо.

В ASA есть команда "LIST". А есть что то похожее в MYSQL? Спасибо.
вопрос решен. GROUP_CONCAT()

SELECT ordre.o_ordrenr,
ordrelinje.ol_ordrenr,
ordre.o_kundenr,
ordre.o_kundenavn,
kunde.k_adresse1,
kunde.k_postnr,
kunde.k_post,
kunde.k_telefon,
kunde.k_telefax,
ordrelinje.ol_varenr,
ordrelinje.ol_tekst,
ordrelinje.ol_enhet,
ordrelinje.ol_enhetspris,
ordrelinje.ol_antall,
ordrelinje.ol_beloep,
ordrelinje.ol_momsbeloep,
eget_firma.firmanavn,
eget_firma.adr1,
eget_firma.adr2,
eget_firma.postnr,
eget_firma.poststed,
eget_firma.foretaksnummer,
ordrelinje.ol_linjenr,
ordrelinje.ol_rabatt,
ordrelinje.ol_rabattbeloep,
eget_firma.telefon,
eget_firma.fax,
kunde.k_betbet,
kunde.k_adresse2,
eget_firma.bank,
ordrelinje.ol_ordredato,
ordrelinje.ol_fakturanr,
ordrelinje.ol_fakturadato,
ordrelinje.ol_forfalldato,
project.p_id,
project.p_name,
ordre.o_kunde_ref,
vare.v_sortnavn,
vare.v_teknisk,
ordre.o_selgernr,
selger.s_selgernavn,
convert('', char(20)) as invoice_other_org_name,
convert('', char(20)) as invoice_other_name,
convert('', char(20)) as invoice_other_adresse,
convert('', char(20)) as invoice_other_zipcode,
convert('', char(20)) as invoice_other_post_place,
(select GROUP_CONCAT('(' || cad.cad_name || ': ' || caf.ca_value || ')', ' ; ') as customer_article_field
from custom_articlefield caf
join vare v on v.v_mother = caf.ca_art_id and v.v_varenr = vare.v_varenr
join custom_articlefield_def cad on cad.cad_id = caf.ca_cad_id and upper(cad.cad_receipt) = 'Y'
where ( trim(caf.ca_value) <> '' ) AND ( trim(caf.ca_value) <> '1900/01/01' )
) as customer_article_field,
kunde.k_kundenavn
FROM {oj ordre LEFT OUTER JOIN project ON ordre.o_project = project.p_id LEFT OUTER JOIN selger ON ordre.o_selgernr = selger.s_selgernr},
ordrelinje,
kunde,
vare,
eget_firma
WHERE( ordre.o_invoice_other = kunde.k_kundenr) and
( ordre.o_ordrenr = ordrelinje.ol_ordrenr ) and
( ordrelinje.ol_varenr = vare.v_varenr ) and
( eget_firma.id = 1 ) AND
(ordrelinje.ol_fakturanr = 1) AND
(ordrelinje.ol_sw1 = 'F') AND
(vare.v_sales_product <> 'N' OR
vare.v_sales_product is null)
ORDER BY ordre.o_kundenavn ASC
;
имеется запрос.
данный запрос выполнялся и выполняется без ошибок когда используется ASA (тут LIST вместо GROUP_CONCAT).
при использование MySQL 5.1 получаю ошибку:
Error code 1054, SQL state 42S22: Unknown column 'vare.v_varenr' in 'on clause'.
Буду благодарен за помощь.

v.v_varenr = vare.v_varenr -перенес в часть WHERE. ошибки не получаю. Осталось проверить на данных. Спасибо

а вот это еще не удалось побороть. идут записи в pbtrace.log

-- Ошибки при вызове функций COUNT() , SUM()

(29b4008): select max ( kl_id ) from kortliste (DBI_PARSE) (0.665 MS / 1094.087 MS)
(29b4008): *** ERROR 1630 ***(rc -1) : [MySQL][ODBC 3.51 Driver][mysqld-5.1.55-community]FUNCTION sk_base.max does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
(29b4008): PREPARE:
(29b4008): SELECT COUNT ( o_faktura_skrevet ) FROM ordre WHERE o_faktura_skrevet =' ' (DBI_PARSE) (0.771 MS / 1094.858 MS)
(29b4008): *** ERROR 1630 ***(rc -1) : [MySQL][ODBC 3.51 Driver][mysqld-5.1.55-community]FUNCTION sk_base.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
(29b4008): PREPARE:
(29b4008): select sum ( pc_issued_amount ) from paymentcoupon where pc_issued_order =0 (DBI_PARSE) (0.735 MS / 1095.593 MS)
(29b4008): *** ERROR 1630 ***(rc -1) : [MySQL][ODBC 3.51 Driver][mysqld-5.1.55-community]FUNCTION sk_base.sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
(29b4008): (DBI_CURRENT_OF_CURSOR) (0.003 MS / 1095.596 MS)

буду благодарен.

в коде пробелов нет (пример из кода):
select count(butikk_id) into :ll_count from hk_butikk where butikk_id = :ls_butikkid using itr_hk;

в файле pbtrace.log пробелы показано:
(3a06818): select count ( butikk_id ) from hk_butikk where butikk_id =' ' (DBI_PARSE) (0.529 MS / 1141.897 MS)
(3a06818): *** ERROR 1630 ***(rc -1) : SQLSTATE = S1000
[MySQL][ODBC 3.51 Driver][mysqld-5.1.55-community]FUNCTION sk_base.count does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual


почему?

в ODBC выбрана опция "Ignore space after function names"

спасибо

не нашел.
еще у меня отиечено "Ignore schema in column specification"

спасибо.
SQL_MODE=IGNORE_SPACE
добавил в строку подключения.

SQLCA.DBParm = "ConnectString='DSN="+ls_database+";UID="+ls_brukernavn+";PWD="+ls_passord + ";option=135168,DelimitIdentifier='No',OJSyntax='ANSI_Escape',SQL_MODE='IGNORE_SPACE'" + "'"

SELECT ordre_backup.o_ordredato,
ordre_backup.o_ordrenr,
ordre_backup.o_kundenr,
ifnull(kunde.k_kundenavn, '') as kunde_k_kundenavn,
ordre_backup.o_sum,
selger.s_selgernavn,
ordre_backup.o_salgstidspunkt,
(
select (if(sum(t1.is_gift) > 0 , 1 , 0 ))
from
(
select (if (count(paymentcoupon.pc_issued_order) > 0 , 1 , 0 )) as is_gift
from paymentcoupon
where paymentcoupon.pc_issued_order = ordre_backup.o_ordrenr
union
select (if(count(paymentcoupon.pc_used_order) > 0 , 1 , 0 )) as is_gift
from paymentcoupon
where paymentcoupon.pc_used_order = ordre_backup.o_ordrenr
) as t1
) as is_gift_card,
(
select ifnull(rabatt_restriksjon_gruppe.min_db, 0)
from rabatt_restriksjon_gruppe, selger
where rabatt_restriksjon_gruppe.gruppe = selger.s_gruppe and selger.s_selgernr = ordre_backup.o_selgernr
) as min_db_selger,
(
select sum(ifnull(ordrelinje_backup.ol_rabattbeloep, 0))
from ordrelinje_backup
where ordrelinje_backup.ol_ordrenr = ordre_backup.o_ordrenr
) as discount_order_amount,
(
select sum(ifnull(ordrelinje_backup.ol_kostpris, 0) * ifnull(ordrelinje_backup.ol_antall,0))
from ordrelinje_backup
where ordrelinje_backup.ol_ordrenr = ordre_backup.o_ordrenr
) as kost_order_amount,
(
select sum((
if ( ordrelinje_backup.ol_antall > 0 ,
case ordrelinje_backup.ol_moms
when 'J' then
case ordrelinje_backup.ol_netto_brutto
when 'B' then ((ordrelinje_backup.ol_enhetspris - (ordrelinje_backup.ol_enhetspris - ((ordrelinje_backup.ol_enhetspris * 100)/(ordrelinje_backup.ol_momsprosent + 100)))) * ordrelinje_backup.ol_antall)
when 'N' then (if (ordrelinje_backup.ol_avg_fritt = 'Y' , (ordrelinje_backup.ol_enhetspris * ordrelinje_backup.ol_antall) , (ordrelinje_backup.ol_enhetspris * ordrelinje_backup.ol_antall) ) )
end
when 'N' then ordrelinje_backup.ol_enhetspris * ordrelinje_backup.ol_antall
end
, 0 )
)
)
from ordrelinje_backup
where ordrelinje_backup.ol_ordrenr = ordre_backup.o_ordrenr
) as net_amount_1_order,
(
select sum((
if ( ordrelinje_backup.ol_antall > 0 ,
case ordrelinje_backup.ol_moms
when 'J' then
case ordrelinje_backup.ol_netto_brutto
when 'B' then
(
(ordrelinje_backup.ol_enhetspris - (ordrelinje_backup.ol_enhetspris - ((ordrelinje_backup.ol_enhetspris * 100)/(ordrelinje_backup.ol_momsprosent + 100))))
- ((ordrelinje_backup.ol_enhetspris - (ordrelinje_backup.ol_enhetspris - ((ordrelinje_backup.ol_enhetspris * 100)/(ordrelinje_backup.ol_momsprosent + 100)))) * ordrelinje_backup.ol_rabatt / 100)
* ordrelinje_backup.ol_antall
)
when 'N' then
(
if (ordrelinje_backup.ol_avg_fritt = 'Y' , ((ordrelinje_backup.ol_enhetspris - (ordrelinje_backup.ol_enhetspris * ordrelinje_backup.ol_rabatt / 100)) * ordrelinje_backup.ol_antall) , ((ordrelinje_backup.ol_enhetspris - (ordrelinje_backup.ol_enhetspris * ordrelinje_backup.ol_rabatt / 100)) * ordrelinje_backup.ol_antall))
)
end
when 'N' then (ordrelinje_backup.ol_enhetspris - (ordrelinje_backup.ol_enhetspris * ordrelinje_backup.ol_rabatt / 100)) * ordrelinje_backup.ol_antall
end
, 0 )
)
)
from ordrelinje_backup
where ordrelinje_backup.ol_ordrenr = ordre_backup.o_ordrenr
) as net_amount_2_order,
(if (ifnull(net_amount_1_order,0) = 0 , 0 , (round((net_amount_1_order - kost_order_amount) * 100 / net_amount_1_order,0)) ) ) as margin_proc_1_order,
(if (ifnull(net_amount_2_order,0) = 0 , 0 , (round((net_amount_2_order - kost_order_amount) * 100 / net_amount_2_order,0)) ) ) as margin_proc_2_order,
(select ifnull(sum(ifnull(paymentcoupon.pc_issued_amount, 0)), 0) from paymentcoupon where paymentcoupon.pc_issued_order = ordre_backup.o_ordrenr) as gift_total_issued ,
(select ifnull(sum(ifnull(paymentcoupon.pc_used_amount, 0)), 0) from paymentcoupon where paymentcoupon.pc_used_order = ordre_backup.o_ordrenr) as gift_total_used,
(
select laybyheader.lbh_complete from laybyheader where laybyheader.lbh_order_id = ordre_backup.o_ordrenr
) as layby_completed,
(
select ifnull(sum(ifnull(laybypayment.lbp_amount,0)),0)
from laybypayment, laybyheader
where laybypayment.lbp_id = laybyheader.lbh_id
and laybyheader.lbh_order_id = ordre_backup.o_ordrenr
) as layby_payment
FROM {oj ordre_backup LEFT OUTER JOIN kunde ON ordre_backup.o_kundenr = kunde.k_kundenr},
selger
WHERE ( ordre_backup.o_selgernr = selger.s_selgernr )
and
(:al_ordrenr = 0 AND
ordre_backup.o_ordredato >= :ad_fra AND
ordre_backup.o_ordredato <= :ad_til AND
ordre_backup.o_selgernr like :as_selger AND
ordre_backup.o_kundenr like :as_kunde AND
ordre_backup.o_sum >= :ac_frabel AND
ordre_backup.o_sum <= :ac_tilbel AND
ordre_backup.o_kunde_ref like :as_ref AND
(
ifnull(kunde.k_kundenavn, '') like :as_customer_name ) and
(:ai_is_gift_card = 2 OR is_gift_card = :ai_is_gift_card) OR
ordre_backup.o_ordrenr = :al_ordrenr )


имеется запрос.
создаем dw получаем ошибку:
Unknown column 'ordre_backup.o_ordrenr' in 'where clause'.

при использование ASA ошибки не возникало.

заранее благодарен

вопрос решен.
select
@ordre_nr := ordre_backup.o_ordrenr,
...
и потом вместо ordre_backup.o_ordrenr используем @ordre_nr
DataWindow хавает.
Спасибо.