How to write a complex query~

[tianhb]

How to write a complex query�

now,I have a query as follows。

        select tbl.a,

                            tbl b,

                            tbl c

        from   table tbl

    where 1=1

          and   (a.start_date >= '2015-04-01' and a.start_date <= '2016-03-31') or (a.end_date >= '2015-04-01' and a.end_date <= '2016-03-31')

  var tableInfo = new GlideRecord('x_smei_table');

    .........................?

    .........................?

    .........................?

    .........................?

    .........................?

    .........................?

    .........................?

    tableInfo .query();

  How to implement this query?   help~~ï¼� help~~!

[nitin_kumar]

Look into the following link, it will contain the query functions

GlideRecord - ServiceNow Wiki

Thanks,

Nitin.

PS: Hit answered, like, Helpful or Correct depending on the impact of the response.

[Geoffrey2]

Build the query in the list view, using the filer.

Run the filter.

Right-click on the query breadcrumbs, select Copy query.

Then use

var query = 'start_date!=NULL^type=normal^state=-5^start_date>=javascript:gs.daysAgoStart(0)';
var tableInfo = new GlideRecord('x_smei_table');
tableInfo.addEncodedQuery(query);
tableInfo .query();

[tianhb]

Geoffrey,thanks a lot.

my query as follows:

var tableInfo = new GlideRecord('x_smei_table'); var dateQuery = "ent_start_dateBETWEENjavascript:gs.dateGenerate('" + strStarDate + "','start')@javascript:gs.dateGenerate('" + strEndDate + "','end')^ORent_end_dateBETWEENjavascript:gs.dateGenerate('" + strStarDate + "','start')@javascript:gs.dateGenerate('" + strEndDate + "','end')";     tableInfo.addEncodedQuery(dateQuery);     tableInfo .query();

And see the error:

        Function dateGenerate is not allowed in scope sn_custom_app.

it is why?

thank you very much.

[zica]

Tian,

Try this one :

var dateQuery = "ent_start_dateBETWEENjavascript:gs.dateGenerate('" + strStarDate + "','start')@javascript:gs.dateGenerate('" + strEndDate + "','end')^ORent_end_dateBETWEENjavascript:gs.dateGenerate('" + strStarDate + "','start')@javascript:gs.dateGenerate('" + strEndDate + "','end')";

var tableInfo = new GlideRecord("x_smei_table");  

tableInfo.addEncodedQuery(dateQuery);  

tableInfo.query();

if (tableInfo.next()) {

        alert("record found");

}