Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site













SourceForge.net Logo

SqlExp in examples

 

(with compiled results for PostgreSQL dialect)

 

 

SqlExp C++

PostgreSQL

Select(COLUMN)

.From(TABLE)

select COLUMN

from TABLE

Select(COLUMN.Of(TABLE))

.From(TABLE)

select TABLE.COLUMN

from TABLE

Select(COLUMN.As(A))

.From(TABLE)

select COLUMN as A

from TABLE

Select(TABLE1(COL, A.As("T_A"), SEQ))

.From(TABLE1)

select TABLE1.COL, TABLE1.A as T_A, TABLE1.SEQ

from TABLE1

Select(COLUMN&A)

.From(TABLE)

select COLUMN$A

from TABLE

Select(COLUMN[3])

.From(TABLE)

select COLUMN3

from TABLE

Select(COLUMN1 % COLUMN2)

.From(TABLE)

select mod(COLUMN1, COLUMN2)

from TABLE

Select(COLUMN1 | COLUMN2)

.From(TABLE)

select COLUMN1 || COLUMN2

from TABLE

Select(SqlFunc("any_fn", COLUMN, 2))

.From(TABLE)

select any_fn(COLUMN, 2)

from TABLE

Select(SqlTxt("current_time"))

.From(TABLE1)

select current_time

from TABLE1

Select(Distinct(COLUMN))

.From(TABLE)

select distinct COLUMN

from TABLE

Select(Distinct(SqlSet(COLUMN1, COLUMN2)))

.From(TABLE)

select distinct COLUMN1, COLUMN2

from TABLE

Select(All(COLUMN))

.From(TABLE)

select all COLUMN

from TABLE

Select(Count(COLUMN))

.From(TABLE)

select count(COLUMN)

from TABLE

Select(SqlAll())

.From(TABLE)

select *

from TABLE

SelectAll()

.From(TABLE1)

select *

from TABLE1

Select(SqlCountRows())

.From(TABLE)

select count(*)

from TABLE

Select(COLUMN)

.From(TABLE)

.OrderBy(Descending(COLUMN))

select COLUMN

from TABLE

order by COLUMN desc

Select(SqlMax(COLUMN))

.From(TABLE)

select max(COLUMN)

from TABLE

Select(SqlMin(COLUMN))

.From(TABLE)

select min(COLUMN)

from TABLE

Select(SqlSum(COLUMN))

.From(TABLE)

select sum(COLUMN)

from TABLE

Select(Avg(COLUMN))

.From(TABLE)

select avg(COLUMN)

from TABLE

Select(Abs(COL))

.From(TABLE1)

select abs(COL)

from TABLE1

Select(Stddev(COLUMN))

.From(TABLE)

select stddev(COLUMN)

from TABLE

Select(Variance(COLUMN))

.From(TABLE)

select variance(COLUMN)

from TABLE

Select(Greatest(COLUMN1, COLUMN2))

.From(TABLE)

select greatest(COLUMN1, COLUMN2)

from TABLE

Select(Least(COLUMN1, COLUMN2))

.From(TABLE)

select least(COLUMN1, COLUMN2)

from TABLE

Select(Upper(COLUMN))

.From(TABLE)

select upper(COLUMN)

from TABLE

Select(Lower(COLUMN))

.From(TABLE)

select lower(COLUMN)

from TABLE

Select(Length(COL))

.From(TABLE1)

select length(COL)

from TABLE1

Select(Substr(COL, 2, 1))

.From(TABLE1)

select substr(COL, 2, 1)

from TABLE1

Select(Instr(COLUMN, "hello"))

.From(TABLE)

select INSTR(COLUMN, 'hello')

from TABLE

Select(SqlNvl(COLUMN1, COLUMN2))

.From(TABLE)

select coalesce(COLUMN1, COLUMN2)

from TABLE

Select(NextVal(SEQ)).Get()

select nextval('SEQ')

Select(CurrVal(SEQ)).Get()

select currval('SEQ')

Select(SqlArg())

.From(TABLE)

select ?

from TABLE

Select(Case(COL == 1, "one")

          (COL == 2, "two")

          ("?"))

.From(TABLE1)

select case when COL = 1 then E'one'

           when COL = 2 then E'two'

           else E'?' end

from TABLE1

Select((COL > 10).AsValue())

.From(TABLE2)

select COL > 10

from TABLE2

Select((COL > 10).As("GT10"))

.From(TABLE2)

select COL > 10 as GT10

from TABLE2

Select(COLUMN)

.From(TABLE)

.Where(COLUMN / 2 > 1 && COLUMN1 == "A" ||

      COLUMN2 == Date(2006, 1, 1))

select COLUMN

from TABLE

where COLUMN / 2 > 1 and COLUMN1 = 'A' or COLUMN2 = '2006-01-01'

Select(COLUMN)

.From(TABLE)

.Where(!(COLUMN == 1))

select COLUMN

from TABLE

where  not (COLUMN = 1)

Select(COLUMN)

.From(TABLE)

.Where((COLUMN1 == 1) - (COLUMN2 == 1))

select COLUMN

from TABLE

where COLUMN1 = 1 and not COLUMN2 = 1

Select(COLUMN)

.From(TABLE)

.Where(IsNull(COLUMN1))

select COLUMN

from TABLE

where COLUMN1 is NULL

Select(COLUMN)

.From(TABLE)

.Where(NotNull(COLUMN1))

select COLUMN

from TABLE

where COLUMN1 is not NULL

Select(COLUMN)

.From(TABLE)

.Where(Like(COLUMN1, Wild("A*")))

select COLUMN

from TABLE

where COLUMN1 like 'A%%'

Select(COLUMN)

.From(TABLE)

.Where(NotLike(COLUMN1, Wild("A*")))

select COLUMN

from TABLE

where COLUMN1 not like 'A%%'

Select(COLUMN)

.From(TABLE)

.Where(In(COLUMN, Select(COLUMN).From(TABLE1)))

select COLUMN

from TABLE

where COLUMN in (select COLUMN from TABLE1)

Select(COLUMN)

.From(TABLE)

.Where(COLUMN == Select(COLUMN).From(TABLE1))

select COLUMN

from TABLE

where COLUMN in (select COLUMN from TABLE1)

Select(COLUMN)

.From(TABLE)

.Where(NotIn(COLUMN,

      Select(COLUMN).From(TABLE1)))

select COLUMN

from TABLE

where COLUMN not in (select COLUMN from TABLE1)

Select(COLUMN)

.From(TABLE)

.Where(COLUMN != Select(COLUMN).From(TABLE1))

select COLUMN

from TABLE

where COLUMN not in (select COLUMN from TABLE1)

Select(COLUMN)

.From(TABLE)

.Where(Exists(Select(COLUMN).From(TABLE1)))

select COLUMN

from TABLE

where exists (select COLUMN from TABLE1)

Select(COLUMN)

.From(TABLE)

.Where(NotExists(Select(COLUMN).From(TABLE1)))

select COLUMN

from TABLE

where not exists (select COLUMN from TABLE1)

Select(COLUMN1)

.From(TABLE1)

| Select(COLUMN2).From(TABLE2)

((select COLUMN1 from TABLE1)

union (select COLUMN2 from TABLE2))

Select(COLUMN1)

.From(TABLE1)

+ Select(COLUMN2).From(TABLE2)

((select COLUMN1 from TABLE1)

union all (select COLUMN2 from TABLE2))

Select(COLUMN1)

.From(TABLE1)

& Select(COLUMN2).From(TABLE2)

((select COLUMN1 from TABLE1)

intersect (select COLUMN2 from TABLE2))

Select(COLUMN1)

.From(TABLE1)

- Select(COLUMN2).From(TABLE2)

((select COLUMN1 from TABLE1)

except (select COLUMN2 from TABLE2))

Select(COLUMN)

.From(TABLE)

.Where(COLUMN == 0)

.GroupBy(COLUMN)

.Having(COLUMN1 == 0)

.OrderBy(Descending(COLUMN))

select COLUMN

from TABLE

where COLUMN = 0

group by COLUMN

having COLUMN1 = 0

order by COLUMN desc

Select(COLUMN)

.From(TABLE).Limit(100)

select COLUMN

from TABLE limit 100

Select(COLUMN)

.From(TABLE).Limit(100, 10)

select COLUMN

from TABLE limit 100, 10

Select(COLUMN)

.From(TABLE).Offset(20)

select COLUMN

from TABLE offset 20

Select(850).Get()

select 850

Select(COLUMN)

.From(TABLE).Hint("hint")

/*+ hint */ select COLUMN

from TABLE

Select(COLUMN)

.From(TABLE)

.InnerJoin(TABLE1)

.On(COLUMN.Of(TABLE) == COLUMN1.Of(TABLE1))

select COLUMN

from TABLE

inner join TABLE1

on TABLE.COLUMN = TABLE1.COLUMN1

Select(COLUMN)

.From(TABLE)

.LeftJoin(TABLE1)

.On(COLUMN.Of(TABLE) == COLUMN1.Of(TABLE1))

select COLUMN

from TABLE

left outer join TABLE1

on TABLE.COLUMN = TABLE1.COLUMN1

Select(COLUMN)

.From(TABLE)

.RightJoin(TABLE1)

.On(COLUMN.Of(TABLE) == COLUMN1.Of(TABLE1))

select COLUMN

from TABLE

right outer join TABLE1

on TABLE.COLUMN = TABLE1.COLUMN1

Select(COLUMN)

.From(TABLE)

.FullJoin(TABLE1)

.On(COLUMN.Of(TABLE) == COLUMN1.Of(TABLE1))

select COLUMN

from TABLE

full outer join TABLE1

on TABLE.COLUMN = TABLE1.COLUMN1

Insert(TABLE)

(COLUMN1, 12)

(COLUMN2, "hello")

(COLUMN, Date(2007, 1, 1))

insert into TABLE(COLUMN1, COLUMN2, COLUMN)

values (12, 'hello', '2007-01-01')

Insert(TABLE)(COLUMN1, 12)(COLUMN2)(COLUMN)

.From(TABLE1)

.Where(COLUMN >= 0)

insert into TABLE(COLUMN1, COLUMN2, COLUMN)

select 12, COLUMN2, COLUMN

from TABLE1

where COLUMN >= 0

Insert(TABLE1)(COLUMN1,"unique")

.Where(NotExists(Select(ID)

                .From(TABLE1)

                .Where(COLUMN1 == "unique")))

insert into TABLE1(COLUMN1)

select 'unique' where not exists

(select ID

 from TABLE1

 where COLUMN1 = 'unique')

Insert(TABLE)(COLUMN, Select(COLUMN1)

.From(TABLE1)

.Where(COLUMN2 == 21).AsValue())

insert into TABLE(COLUMN) values ((select

COLUMN1

from TABLE1

where COLUMN2 = 21))

Insert(TABLE1)(COLUMN1)(COL, SqlSum(COLUMN2))

.From(TABLE1)

.Where(COL >= 0)

.GroupBy(COLUMN1).Having(COLUMN2 > 10)

insert into TABLE1(COLUMN1, COL)

select COLUMN1, sum(COLUMN2) from TABLE1

where COL >= 0

group by COLUMN1

having COLUMN2 > 10

ValueMap m;

m(COLUMN1, 1)(COLUMN2, "hello");

Insert(TABLE1)(m)

 

Update(TABLE2)(m).Where(ID == 123)

 

 

insert into TABLE1(COLUMN1, COLUMN2)

values (1, E'hello')

update TABLE2

set COLUMN1 = 1, COLUMN2 = E'hello'

where ID = 123

Update(TABLE)

(COLUMN1, 13)

(COLUMN2, "world")

.Where(COLUMN > Date(2007, 1, 1))

update TABLE

set COLUMN1 = 13, COLUMN2 = 'world'

where COLUMN > '2007-01-01'

Delete(TABLE)

.Where(COLUMN < 0)

delete

from TABLE

where COLUMN < 0

Select(COLUMN)

.From(Select(COLUMN).From(TABLE1))

select COLUMN

from (select COLUMN from TABLE1)

Select(COLUMN)

.From(Select(COLUMN).From(TABLE1)

.AsTable(TABLE2))

select COLUMN

from ((select COLUMN from TABLE1) as TABLE2)

Select(COLUMN)

.From(TABLE)

.LeftJoin(Select(COLUMN).From(TABLE1))

.On(COLUMN.Of(TABLE) == COLUMN1.Of(TABLE1))

select COLUMN

from TABLE left outer join

(select COLUMN from TABLE1)

on TABLE.COLUMN = TABLE1.COLUMN1

Select(COLUMN)

.From(TABLE)

.LeftJoin(Select(COLUMN).From(TABLE1)

         .AsTable(TABLE2))

.On(COLUMN.Of(TABLE) == COLUMN1.Of(TABLE1))

select COLUMN

from TABLE

left outer join

((select COLUMN from TABLE1) as TABLE2)

on TABLE.COLUMN = TABLE1.COLUMN1

Select(TABLE(COLUMN, COLUMN1)).From(TABLE)

select TABLE.COLUMN, TABLE.COLUMN1 from TABLE

Select(ID(ID, NAME, LASTNAME))

.From(TABLE1).LeftJoinRef(TABLE2)

.Where(BDATE == Date(2011, 12, 9))

select ID.ID, ID.NAME, ID.LASTNAME

from TABLE1 left outer join TABLE2

on TABLE2.TABLE1_ID = TABLE1.NAME

where BDATE = date '2011-12-09'

Note: Based on .sch file REFERENCES

Select(ID(ID, NAME, LASTNAME))

.From(TABLE1).LeftJoinRef(TABLE2)

.On(IsNull(BDATE))

.Where(BDATE == Date(2011, 12, 9))

select ID.ID, ID.NAME, ID.LASTNAME

from TABLE1 left outer join TABLE2

on TABLE2.TABLE1_ID = TABLE1.NAME

and BDATE is NULL

where BDATE = date '2011-12-09'

Note: Based on .sch file REFERENCES

Select(TABLE1(SqlAll()))

.From(TABLE1)

select TABLE1.* from TABLE1

Select(SqlAll().Of(TABLE1))

.From(TABLE1)

select TABLE1.* from TABLE1

Select(TABLE1(S_TABLE2()))

.From(TABLE1)

select TABLE1.ID, TABLE1.TABLE1_ID, TABLE1.NAME, TABLE1.LASTNAME, TABLE1.BDATE, TABLE1.COLUMN2, TABLE1.SEQ

from TABLE1

Vector<int> m;

m << 1 << 2 << 3;

Select(ID)

.From(TABLE1)

.Where(ID == SqlSetFrom(m))

 

 

select ID

from TABLE1

where ID in (1, 2, 3)

WithRecursive(TN)(ID, NAME, PARENT_ID)

.As(Select(ID, NAME, PARENT_ID)

   .From(TREENODE)

   .Where(IsNull(PARENT_ID))

    +

    Select(TREENODE(ID, NAME, PARENT_ID))

    .From(TREENODE, TN)

    .Where(TREENODE(PARENT_ID) == TN(ID)))

.With(TNSIZE)

.As(Select(PARENT_ID,

          Select(NAME).From(TREENODE)

          .Where(ID == TN(PARENT_ID))

          .AsValue(),

          SqlCountRows())

   .From(TN)

   .GroupBy(PARENT_ID)) (SelectAll().From(TNSIZE))

with recursive TN(ID, NAME, PARENT_ID)

as ((select ID, NAME, PARENT_ID

    from TREENODE

    where PARENT_ID is NULL)

    union all

    (select TREENODE.ID, TREENODE.NAME,

            TREENODE.PARENT_ID

     from TREENODE, TN

     where TREENODE.PARENT_ID = TN.ID)),

TNSIZE

as (select PARENT_ID,

          (select NAME from TREENODE

           where ID = TN.PARENT_ID),

           count(*)

   from TN

   group by PARENT_ID)

select * from TNSIZE

Temporary("TT").As(

Select(NAME, LASTNAME).From(TABLE1)

.Where(COLUMN1 == 12))

create temporary table TT as

(select NAME, LASTNAME from TABLE1

where COLUMN1 = 12)

 

Last edit by cxl on 06/04/2017. Do you want to contribute?. T++