Hello,
I wrote this sql query in Oracle
select
tccom100.t$bpid as "Code",
tccom100.t$nama as "Name",
tcyru023.t$lnbp$l as "Fullname",
tctax400.t$fovn as "INN",
tccom100.t$prst as "Status code",
decode(tccom100.t$prst,1,'Potencial',2,'Active',3,'Inactive') as "Status"
from baan.ttccom100201 tccom100
left join baan.ttctax400201 tctax400 on tccom100.t$bpid = tctax400.t$bpid
left join baan.ttcyru023201 tcyru023 on tccom100.t$cadr = tcyru023.t$cadr$l
order by tccom100.t$bpid

The question is how to wrote this part (decode(tccom100.t$prst,1,'Potencial',2,'Active',3,'Inactive') as "Status") in BAAN?

declaration:
#ifdef end(X)
#undef end()
#endif

|****************************** group section **********************************
group.1:
init.group:
get.screen.defaults()

|****************************** FUNCTION SECTION ***************************
functions:

function extern read.main.table()
{
table ttccom100
string status(100)
string str_bpid(100)

select (case when :tccom100.prst = tccom.prst.active then "Active"
when :tccom100.prst = tccom.prst.potential then "Potential"
when :tccom100.prst = tccom.prst.inactive then "Inactive"
else ""
end)as status,
tccom100.bpid as str_bpid,
tccom100.nama
from tccom100
order by tccom100._index1
selectdo
endselect
}

This is legal Baan SQL? Wow. Well, you live and learn...

But the real question is: Why would you *want* to do it?

Uli

Uli this might have been used in external system pulling from oracle... as you said it not required with in baan .
but this case function in LN is really handy during conditional aggregations , yes this is legal once we undefine the macro END.. ( case end statement clashes with END macro)

Example


select cisli310.srcp, sum(cisli310.dqua):test1,
sum( case cisli305.stdb
when cisli.stdb.settl.invoice then cisli310.slai(1) - ((cisli310.ldai + cisli310.odai) )
else cisli310.amti(1) - ((cisli310.ldai + cisli310.odai) )
end ):test2

uli,

sometime 3 years ago, someone in my office asked about "decode" and i remember there was a discussion in baanboard. personally, i never had a situation when i feel the need to use this, especially with the undef. i'm pretty sure these db guys will find out pretty soon that baan has its own way of handling programming situations rather easily.