MySQL Forums
Forum List  »  General

Help in issue in my Code
Posted by: Abdo Badreldin
Date: September 29, 2018 01:29PM

good morning ,
after my database has been migrated from firebird to mysql . i have jasber report

for sales invoice . and not working . i am trying to fix the bugs but i have syntax error once i run the query in mysql DB .i spent too much time its advanced level for me . and no luck any idea ?

here is the Code
select
so.id as soid,
COALESCE(address.name,so.shiptoname) as soldtoname,

CASE WHEN position(ASCII_CHAR(10), so.shiptoaddress) > 0
THEN substring(so.shiptoaddress from 1 for (position(ASCII_CHAR(10), so.shiptoaddress) -1))
ELSE so.shiptoaddress
END as soldtoadr1,

CASE WHEN position(ascii_char(10), so.shiptoaddress) > 0 and position(ascii_char(10), so.shiptoaddress, position(ascii_char(10), so.shiptoaddress) + 1) = 0
THEN substring(so.shiptoaddress from (position(ascii_char(10), so.shiptoaddress) + 1))
WHEN position(ascii_char(10), so.shiptoaddress) > 0 and position(ascii_char(10), so.shiptoaddress, position(ascii_char(10), so.shiptoaddress) + 1) > 0
THEN substring(so.shiptoaddress from (position(ascii_char(10), so.shiptoaddress) + 1) for (position(ascii_char(10), so.shiptoaddress, position(ascii_char(10), so.shiptoaddress) + 1) - (position(ascii_char(10), so.shiptoaddress) + 1)))
ELSE ''
END as soldtoadr2,

CASE WHEN position(ascii_char(10), so.shiptoaddress, position(ascii_char(10), so.shiptoaddress) + 1) > 0
THEN substring(so.shiptoaddress from position(ascii_char(10), so.shiptoaddress, position(ascii_char(10), so.shiptoaddress) + 1) + 1)
ELSE ''
END as soldtoadr3,

so.shiptocity as soldtocity,
soldstate.code as soldtostate,
so.shiptozip as soldtozip,
countryconst.name as soldtoCountry,
phone.datus as soldtophone,
fax.datus as soldtofax,
soldtoemail.datus as soldtoemail,

so.billtoname as billtoname,
(case
when position(ascii_char(10), so.billtoaddress) > 0 then substring(so.billtoaddress from 1 for (position(ascii_char(10), so.billtoaddress) - 1))
else so.billtoaddress
end) as billtoadr1,
(case
when position(ascii_char(10), so.billtoaddress) > 0 and position(ascii_char(10), so.billtoaddress, position(ascii_char(10), so.billtoaddress) + 1) = 0 then substring(so.billtoaddress from (position(ascii_char(10), so.billtoaddress) + 1))
when position(ascii_char(10), so.billtoaddress) > 0 and position(ascii_char(10), so.billtoaddress, position(ascii_char(10), so.billtoaddress) + 1) > 0 then substring(so.billtoaddress from (position(ascii_char(10), so.billtoaddress) + 1) for (position(ascii_char(10), so.billtoaddress, position(ascii_char(10), so.billtoaddress) + 1) - (position(ascii_char(10), so.billtoaddress) + 1)))
else ''
end) as billtoadr2,
(case
when position(ascii_char(10), so.billtoaddress, position(ascii_char(10), so.billtoaddress) + 1) > 0 then substring(so.billtoaddress from position(ascii_char(10), so.billtoaddress, position(ascii_char(10), so.billtoaddress) + 1) + 1)
else ''
end) as billtoadr3,

so.billtocity as billtocity,
billstate.code as billtostate,
so.billtozip as billtozip,
billcountry.name as billcountry,
so.customercontact as billtoattn,
billtoemail.datus as billtoemail,
billphone.datus as billphone,

so.num as invoiceno,
so.datecompleted as invoicedate,
so.customerpo as orderno,
DATEADD(paymentterms.netdays day to so.datecompleted) as duedate,
customer.number as customerno,

lt.allitems-lt.shipping-lt.dryice as amtsubtotal,
lt.shipping as amtshipping,
lt.dryice as amtdryice,
so.totaltax as amttax,
lt.allitems+so.totaltax as amttotal,
COALESCE(pt.amt,0) as amtpayments,
(lt.allitems+so.totaltax)-COALESCE(pt.amt,0) as amtamountdue,
pt.ct as paymentcount


from so
left join addressmultilinesoview on addressmultilinesoview.soid = so.id
join customer on customer.id = so.customerid
left join paymentterms on paymentterms.id = so.paymenttermsid
left join address on address.address = so.shiptoaddress and address.accountid = customer.accountid
LEFT JOIN address billaddress on so.billtoaddress = billaddress.address and billaddress.accountid = customer.accountid and billaddress.typeid = 20
left join addressmultilineview on addressmultilineview.id = address.id
left join contact as phone on phone.addressid = address.id and phone.defaultflag = 1 and phone.typeid = 50
left join contact as fax on fax.typeid = 40 and fax.addressid = address.id and fax.defaultflag =1
LEFT JOIN contact as soldtoEmail on soldtoemail.typeid = 60 and soldtoemail.addressid = address.id and soldtoemail.defaultflag = 1
LEFT JOIN contact as billtoEmail on billtoemail.typeid = 60 and billtoemail.addressid = billaddress.id and billtoemail.defaultflag = 1
left join contact as billphone on billphone.addressid = billaddress.id and billphone.defaultflag = 1 and billphone.typeid = 50
LEFT JOIN stateConst billState ON so.billToStateId = billState.id
LEFT JOIN stateConst soldState ON so.shiptostateid = soldstate.id
LEFT JOIN countryconst on so.shiptocountryid = countryconst.id
LEFT JOIN countryconst billcountry on so.billtocountryid = billcountry.id

left join (select soid, count(id) as ct, sum(amount) amt from postransaction group by soid ) as pt on pt.soid = so.id
left join (select
soitem.soid,
sum(soitem.totalprice) as allitems,
SUM( IIF( soitem.typeid = 60 /* Shipping Line Item TYpes */ , soitem.totalprice , 0 ) ) as shipping,
SUM( IIF( soitem.productnum in ('0000','0001') , soitem.totalprice , 0 ) ) as dryice
from soitem
group by soitem.soid) as lt on lt.soid = so.id

where so.num = $P{soNum}

thanks

Options: ReplyQuote


Subject
Written By
Posted
Help in issue in my Code
September 29, 2018 01:29PM
September 29, 2018 02:08PM
September 29, 2018 02:13PM
September 29, 2018 03:59PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.