Hello All!

I have some questions about Infor ERPLN 6.1 query language :

1. How to use the construction "case when ... then ... else ... end" in the query "select ... from ... where ... selectdo ... selectempty ... endselect"?
When using a query of the form (syntax is taken from the Help 4gl):
STRING str1 (1000)
select (case when 2> 1 then tccom100.nama end) AS str1
from tccom100
where tccom100.bpid = "000 000 733"
selectdo
message (str1)
selectempty
endselect
compile error occurs: «Fatal Error: Macro 'end': Illegal number of arguments.»
2. What functions can be used in SELECT statement to provide CASE functionality?

3. What functions can be invoked in a query? So far only «trim» works fine. Maybe I can call something like «nvl», «decode», «upper» and so on?
Maybe, I can call my functions (from Oracle packages or 4gl)?

Where did you get this example? I'm sure that's not Baan query syntax.
In select statement you can put:

* |all fields in all tables in the FROM statement
table.* |all fields in that table
table.field |a specific field

(case doesn't exists!)

also, you can use alias for tables and/or fields, but surely you cannot 'put a condition' on which field to select.

Maybe it's better to explain what result you want to obtain in a real case...

I got this example from "Infor ERP LN 6.1 Programmers Guide 7.6.0" (progguide.chm):

<searched case expression>
::= CASE
WHEN <search condition> THEN <result>
[{ WHEN <search condition> THEN <result> }...]
[ ELSE <result> ]
END

<result>
::= <value expression>
| NULL


So CASE exists!
I can use alias in SELECT for functions (trim, cast).

I need to use CASE (or its analog) in SELECT statement.

Hello,
that's the whole CASE (searched)

CASE expression (searched)
The searched CASE expression specifies a conditional value.

Syntax

<searched case expression>
::= CASE
WHEN Search condition THEN <result>
[{ WHEN Search condition THEN <result> }...]
[ ELSE <result> ]
END

<result>
::= Value expression
| NULL
Semantics

The searched CASE expression evaluates the first <search condition> and if it evaluates to TRUE then the result is the value of the first <result>. Otherwise, the searched CASE expression evaluates the second <search condition> and if it evaluates to TRUE then the result is the value of the second <result>. And so on. If every <search condition> evaluates to FALSE or UNKNOWN, then the result is the value of the <result> specified in the ELSE clause. If the ELSE clause is missing then the result is the NULL value.

Examples

The following searched CASE expression returns 'yes':

CASE WHEN 2>1 THEN 'yes' END
The following searched CASE expression results in 'Male' if the column sex equals dbsex.male; it results in 'Female' if column sex equals dbsex.female; otherwise it results in the NULL value.

CASE
WHEN sex = dbsex.male THEN 'Male'
WHEN sex = dbsex.female THEN 'Female'
END

Regards

Yes, that's the whole CASE (searched), but it does not work. When I try to compile this query, compile error occurs: «Fatal Error: Macro 'end': Illegal number of arguments.»

Really it doesn't work... But have to. It seems to be Infor big fault!
Official help says that we can use CASE in SELECT but in fact we can't, because "END" is interpreted as a function call but not as a part of CASE inside SELECT.
May be gurus can explain this difference between official help and reality?

Really it doesn't work... But have to. It seems to be Infor big fault!
Official help says that we can use CASE in SELECT but in fact we can't, because "END" is interpreted as a function call but not as a part of CASE inside SELECT.
May be gurus can explain this difference between official help and reality?

Hello,

SQL reserved words
This section lists the reserved words of the Infor ERP LN SQL language.

Reserved words

ALIKE ALL AND ARRAY AS ASC AVG
BETWEEN BOTH BUFFER BY
CASE CAST CLEAR CLEARUNREF COUNT CROSS CURRENT_DATE CURRENT_TIMESTAMP
DATE DATE.NUM DATE.TO.NUM DELETE DESC DISTINCT
ELSE EMPTY END ENUM_DESCRIPTION ESCAPE EXISTS
FALSE FETCHING FIRST FOR FROM FULL
GROUP
HAVING HINT HINTS
IN INDEX INNER INRANGE INTEGER IS
JOIN
LAST LEADING LEFT LIKE
MAX MIN
NO NOT NULL
ON OR ORDER ORDERED OUTER
PATH PREPARED
RAW REAL REFERS REPEAT RETRY RIGHT ROW ROWS
SELECT SET SETUNREF SKIP SIZE STRING SUBHINT SUM
TEXT_CONTENT THEN TIMESTAMP TO TRAILING TRIM TRUE
UNION UNREF UPDATE USE
WHEN WHERE WITH
Usage notes

Reserved words can be used as names of parameters. The following example contains the keyword SELECT as a parameter name, but is nonetheless legal.

Use as:


........
selectdo

ON CASE <variable>
CASE <value_1> BREAK
CASE <value_2> BREAK
..............
ENDCASE



should work


Regards

Learning never stops.
After almost 20 years of baan tools, I finally found something new ;)
The main problem is that it seems not to work :rolleyes:

Hello,

SQL reserved words
This section lists the reserved words of the Infor ERP LN SQL language.

Reserved words

ALIKE ALL AND ARRAY AS ASC AVG
BETWEEN BOTH BUFFER BY
CASE CAST CLEAR CLEARUNREF COUNT CROSS CURRENT_DATE CURRENT_TIMESTAMP
DATE DATE.NUM DATE.TO.NUM DELETE DESC DISTINCT
ELSE EMPTY END ENUM_DESCRIPTION ESCAPE EXISTS
FALSE FETCHING FIRST FOR FROM FULL
GROUP
HAVING HINT HINTS
IN INDEX INNER INRANGE INTEGER IS
JOIN
LAST LEADING LEFT LIKE
MAX MIN
NO NOT NULL
ON OR ORDER ORDERED OUTER
PATH PREPARED
RAW REAL REFERS REPEAT RETRY RIGHT ROW ROWS
SELECT SET SETUNREF SKIP SIZE STRING SUBHINT SUM
TEXT_CONTENT THEN TIMESTAMP TO TRAILING TRIM TRUE
UNION UNREF UPDATE USE
WHEN WHERE WITH
Usage notes

Reserved words can be used as names of parameters. The following example contains the keyword SELECT as a parameter name, but is nonetheless legal.

Use as:


........
selectdo

ON CASE <variable>
CASE <value_1> BREAK
CASE <value_2> BREAK
..............
ENDCASE



should work


Regards

You did not understand what
It's not about

Thank you, bdittmar!
Of cause I know about "ON CASE ... ENDCASE" in SELECTDO, but Bezique needs CASE inside SELECT (or something like DECODE function in Oracle).
You forced me to read help and error message more attentively.
So what I found:
There is SQL reserved word END, but also there is a standart (system) macro "END(...)". So, when preprocessor finds END in any place of code (even inside of SELECT) it tries to replace that END with macro's body (and gets error). Of cause it's a fault of Infor system developers (and testers)!!!
We can undef this evil macro in DECLARATION section:

#ifdef end(i)
#undef end()
#endif

and then our "CASE ... END" in SELECT will work!

It's very strange that gurus don't know how to use CASE in SELECT. It's common and very powerful construction (in Oracle) and I afraid to imagine development of SQL in other Oracle systems without CASE or DECODE.

Thank you, bdittmar!
Of cause I know about "ON CASE ... ENDCASE" in SELECTDO, but Bezique needs CASE inside SELECT (or something like DECODE function in Oracle).
You forced me to read help and error message more attentively.
So what I found:
There is SQL reserved word END, but also there is a standart (system) macro "END(...)". So, when preprocessor finds END in any place of code (even inside of SELECT) it tries to replace that END with macro's body (and gets error). Of cause it's a fault of Infor system developers (and testers)!!!
We can undef this evil macro in DECLARATION section:

#ifdef end(i)
#undef end()
#endif

and then our "CASE ... END" in SELECT will work!

It's very strange that gurus don't know how to use CASE in SELECT. It's common and very powerful construction (in Oracle) and I afraid to imagine development of SQL in other Oracle systems without CASE or DECODE.

Many thanks to SerBel!!!!!!

you're a real guru

It works!!!Declaration:
#ifdef end(X)
#undef end()
#endif
Functions:

function void query_sql_1(){
string orby1(100)
long orby2
SELECT (case when :sort_by = 1 then tdpur400.orno
when :sort_by = 2 then tdpur400.ccon
when :sort_by = 3 then tdpur400.otbp
else ""
end) as orby1,
(case when :sort_by = 1 then tdpur401.pono
else 0
end) as orby2,
tdpur400.ccur, tdpur400.orno, tdpur400.otbp, tdpur400.ccon,
tdpur401.orno, tdpur401.otbp, tdpur401.cono, tdpur401.pono,
tdpur401.odat, tdpur401.item, tdpur401.qoor,
tdpur401.cuqp, tdpur401.pric, tdpur401.cpon
FROM tdpur400 INNER JOIN tdpur401 ON (tdpur400.orno=tdpur401.orno and tdpur401.item>=:item.from and tdpur401.item<=:item.to)
WHERE tdpur400.orno >= :orno.from and tdpur400.orno <= :orno.to and
tdpur400.ccon >= :ccon.from and tdpur400.ccon <= :ccon.to and
tdpur400.otbp >= :bpid.from and tdpur400.otbp <= :bpid.to and
tdpur400.odat >= :odat.from and tdpur400.odat <= :odat.to and
tdpur400.hdst <> :closed and tdpur400.hdst <> :cancelled
ORDER BY 1, 2
selectdo
....................
endselectOnce again thanks to all

I take it this is an LN feature not available to us 4c4 users? I tried some simple versions, but could not get it to compile. The quote by the first case was always the error.

Error will not occur when compiling if you declare:
#ifdef end(X)
#undef end()
#endif
in ERP LN 6.1

It's very strange that gurus don't know how to use CASE in SELECT. It's common and very powerful construction (in Oracle) and I afraid to imagine development of SQL in other Oracle systems without CASE or DECODE.

Isn't strange, I (and many others I think) survived without this functionallity for a lot of time. It's nice to know there is this possibility, but for now I cannot even figure a situation on which this future is absolutely mandatory.

PS.: What happens if after I have undefined the end() macro I have to use it?

Error will not occur when compiling if you declare:
#ifdef end(X)
#undef end()
#endif
in ERP LN 6.1

Yes - that compiles fine in 4c4, but "SELECT (case" generates the error. Seems like this is another LN feature, but I will try it on our latest SP next week.