General Computations

Read(257) Label: general computation,

This chapter lists code examples about functions of general computations, including IS NULL/NVL/COALESCE, CAST/CONVERT, CASE/DECODE, AND/OR/NOT/<>, LIKE, COUNT/SUM/AVG/MAX/MIN, and IN/EXISTS.

IS NULL/NVL/COALESCE

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE,DEPT, SALARY from EMPLOYEE")

 

2

=A1.select(DEPT!=null)

Not null

3

=A1.select(DEPT ==null)

Null

4

=demo.query("select NAME,UNITPRICE,QUANTITY from RECEIPT")

 

5

=A4.(NAME).ifn()

The first non-null member

 

IS NULL/NVL/COALESCE

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE,DEPT, SALARY from EMPLOYEE")

 

2

=A1.select(DEPT!=null)

Not null

3

=A1.select(DEPT ==null)

Null

4

=demo.query("select NAME,UNITPRICE,QUANTITY from RECEIPT")

 

5

=A4.(NAME).ifn()

The first non-null member

 

CAST/CONVERT

 

A

 

1

=date("1983-09-12")

Convert string to date

2

=string(A1,"yyyyMMdd")

Convert date to string

3

=int("5")

Convert string to integer

4

=string(5)

Convert integer to string

5

=decimal(A3)

Convert integer to big decimal

6

=ifnumber(A5)

Judge if A5 is a number

7

=float("234")

Convert string to float

 

CAST/CONVERT

 

A

 

1

=date("1983-09-12")

Convert string to date

2

=string(A1,"yyyyMMdd")

Convert date to string

3

=int("5")

Convert string to integer

4

=string(5)

Convert integer to string

5

=decimal(A3)

Convert integer to big decimal

6

=ifnumber(A5)

Judge if A5 is a number

7

=float("234")

Convert string to float

 

CASE/DECODE

 

A

 

1

1

 

2

=case(A1,1,"ClassOne",2,"ClassTwo","ClassThree")

 

3

=if(A1==1, "ClassOne", A1==2,"ClassTwo","ClassThree")

 

 

CASE/DECODE

 

A

 

1

1

 

2

=case(A1,1,"ClassOne",2,"ClassTwo","ClassThree")

 

3

=if(A1==1, "ClassOne", A1==2,"ClassTwo","ClassThree")

 

 

AND/OR/NOT, <>

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.select(CLIENT=="PWQ" || CLIENT=="QUICK")

OR

3

=A1.select(AMOUNT>5000 && AMOUNT<10000)

AND

4

=A1.select(!(CLIENT=="TRADH"))

NOT

5

=A1.select(CLIENT!="TRADH")

<> 

 

AND/OR/NOT, <>

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.select(CLIENT=="PWQ" || CLIENT=="QUICK")

OR

3

=A1.select(AMOUNT>5000 && AMOUNT<10000)

AND

4

=A1.select(!(CLIENT=="TRADH"))

NOT

5

=A1.select(CLIENT!="TRADH")

<> 

 

LIKE

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.select(like(CLIENT,"*AY*"))

 

 

LIKE

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.select(like(CLIENT,"*AY*"))

 

 

COUNT/SUM/AVG/MAX/MIN

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.sum(AMOUNT)

 

3

=A1.count()

 

4

=A1.avg(AMOUNT)

 

5

=A1.max(AMOUNT)

 

6

=A1.min(AMOUNT)

 

 

COUNT/SUM/AVG/MAX/MIN

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.sum(AMOUNT)

 

3

=A1.count()

 

4

=A1.avg(AMOUNT)

 

5

=A1.max(AMOUNT)

 

6

=A1.min(AMOUNT)

 

 

IN/EXISTS

 

A

 

1

[1,3,5,7,9]

 

2

=demo.query("select * from EMPLOYEE")

 

3

=A2.select(A1.pos(EID)>0)

IN

4

[English,Math]

 

5

=demo.query("select * from SCORES where CLASS='Class one'")

 

6

=A5.select(SCORE>75).group(STUDENTID)

 

7

=A6.select(~.(SUBJECT).pos(A4)!=null)

EXISTS

8

=A7.(STUDENTID)

 

 

IN/EXISTS

 

A

 

1

[1,3,5,7,9]

 

2

=demo.query("select * from EMPLOYEE")

 

3

=A2.select(A1.pos(EID)>0)

IN

4

[English,Math]

 

5

=demo.query("select * from SCORES where CLASS='Class one'")

 

6

=A5.select(SCORE>75).group(STUDENTID)

 

7

=A6.select(~.(SUBJECT).pos(A4)!=null)

EXISTS

8

=A7.(STUDENTID)