New query against same table

[wiltonr]

Is it possible to query the same table in a script without declaring a new var?

I have a script include where I am querying a custom table and when I get the results need to do specific things; then I want to query the same table with different criteria to do different specific things.   I need to query the same table four times to do somewhat similar things.

Example:

	var bills = new GlideRecord('u_citnet_billing');
	bills.addQuery('u_service_billing_profile.u_fund_to_credit', 'CONTAINS', 'NF');
	bills.addQuery('u_billing_status','Ready for Billing');
	bills.addQuery('u_billing_account',account);
	bills.query();
	while(bills.next()){
	//perform some calculations
	}
	Then I want to query the same table but for one difference:
	bills.addQuery('u_service_billing_profile.u_fund_to_credit', 'CONTAINS', 'IN');
	bills.addQuery('u_billing_status','Ready for Billing');
	bills.addQuery('u_billing_account',account);

Do I have to declare a new variable as in:

	var bills2 = new GlideRecord('u_citnet_billing');
	bills2.addQuery('u_service_billing_profile.u_fund_to_credit', 'CONTAINS', 'IN');
	bills2.addQuery('u_billing_status','Ready for Billing');
	bills2.addQuery('u_billing_account',account);
	bills2.query();
	while(bills2.next()){
	//perform some calculations
	}

Or is there a more efficient way to re-query the same table?

[Anurag Tripathi]

in the current scenario you have to use 2 queries to achieve this.

But if functionally its alright, i think you can use OR query and achieve the same thing in 1 query only.

var bills = new GlideRecord('u_citnet_billing');

  bills.addQuery('u_service_billing_profile.u_fund_to_credit', 'CONTAINS', 'NF').addOrCondition('u_service_billing_profile.u_fund_to_credit', 'CONTAINS', 'IN');

  bills.addQuery('u_billing_status','Ready for Billing');

  bills.addQuery('u_billing_account',account);

  bills.query();

  while(bills.next()){

  //perform some calculations

  }

Try the code above

[Alex North]

Be careful trying to throw everything in to a single query. The dot walking combined with the OR condition is likely to have some severe performance implications as these tables grow bigger. Much, much better to run multiple single queries and combine the result sets.

[Michael Ritchie]

Are these nested queries where you are taking data from one record to then query data in another record or are they all separate queries?   If the former, then you will need separate variable declarations.   If the latter, then you can just do something like the following:

var bills = new GlideRecord('u_citnet_billing');

bills.addQuery('u_service_billing_profile.u_fund_to_credit', 'CONTAINS', 'NF');

bills.addQuery('u_billing_status','Ready for Billing');

bills.addQuery('u_billing_account',account);

bills.query();

while(bills.next()){

//perform some calculations

}

bills.initialize();

bills.addQuery('u_service_billing_profile.u_fund_to_credit', 'CONTAINS', 'IN');

bills.addQuery('u_billing_status','Ready for Billing');

bills.addQuery('u_billing_account',account);

[ChrisBurks]

I'm not sure where you are running your script but you should also be able to turn your query into a function and have it accept one or some parameters:

var first = getBills('NF');

while(first.next()){

  //perform some calculations

  var example = first.u_gain - first.u_loss;

}

var second = getBills('IN');

while(second.next()){

  //perform some calculations

  var example2 = second.u_income - second.u_debt;

}

function getBills(profile_credit){

  var bills = new GlideRecord('u_citnet_billing');

  bills.addQuery('u_service_billing_profile.u_fund_to_credit', profile_credit);

  bills.addQuery('u_billing_status','Ready for Billing');

  bills.addQuery('u_billing_account',account);

  bills.query();

  return bills;

}