BiTel

select phone.phone1,phone.phone2,phone.phone3,phone.phone4,phone.phone5
from   contract_parameter_type_9 AS Phone,
   LEFT JOIN
   contract_parameter_type_5 as flag
   ON flag.cid = phone.cid
where   flag.pid=42
   and flag.val=1
order by phone.phone1

select phone.phone1,phone.phone2,phone.phone3,phone.phone4,phone.phone5
from   contract_parameter_type_9 AS phone
   LEFT JOIN
   contract_parameter_type_5 as flag
   ON flag.cid = phone.cid
where   flag.pid=42
   and flag.val=1
order by phone.phone1

import bitel.billing.server.util.*;
import bitel.billing.server.contract.bean.*;
import java.sql.*;
import java.math.*;
public void main( setup, con, conSlave )
{

String query_dolg = "SELECT cnt.id, cnt.title, cnt.comment, balance.summa1 FROM contract_balance AS balance,contract AS cnt LEFT JOIN contract_payment AS pay ON cnt.id = pay.cid AND pay.dt BETWEEN '2011-03-01' AND '2011-03-31' WHERE pay.id IS NULL AND cnt.date2 IS NULL AND cnt.gr = gr & (1 << 2) AND balance.cid = cnt.id AND balance.yy = '2011' AND balance.mm = '3' AND balance.summa1 < 0 ORDER BY cnt.title";
PreparedStatement ps = con.prepareStatement( query_dolg );
ResultSet rs = ps.executeQuery();
int id;
int summa;
while( rs.next() )
{
String title = rs.getString("title");
String comment = rs.getString("comment");
summa = rs.getInt("summa1");
id = rs.getInt("id");
print ("title -> " + title);
print ("comment -> " + comment);
print ("summa -> " + summa);
print ("id -> " + id);
}
rs.close();
ps.close();

String query = "select phone.phone1,phone.phone2,phone.phone3,phone.phone4,phone.phone5 from contract, contract_parameter_type_9 AS phone LEFT JOIN contract_parameter_type_5 as flag ON flag.cid = phone.cid where flag.pid=42 and flag.val=1 and contract.id = "+id+" and contract.id order by phone.phone1";
PreparedStatement ps = con.prepareStatement( query );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
String phone = rs.getString("phone1");
String phone1 = rs.getString("phone2");
String phone2 = rs.getString("phone3");
String phone3 = rs.getString("phone4");
String phone4 = rs.getString("phone5");
print ("phone1 -> " + phone);
print ("phone2 -> " + phone1);
print ("phone3 -> " + phone2);
print ("phone4 -> " + phone3);
print ("phone5 -> " + phone4);
}
rs.close();
    ps.close();
}

import bitel.billing.server.util.*;
import bitel.billing.server.contract.bean.*;
import java.sql.*;
import java.math.*;

public void main( setup, con, conSlave )
{
    String query_dolg = "SELECT cnt.id, cnt.title, cnt.comment, balance.summa1 FROM contract_balance AS balance,contract AS cnt LEFT JOIN contract_payment AS pay ON cnt.id = pay.cid AND pay.dt BETWEEN '2011-03-01' AND '2011-03-31' WHERE pay.id IS NULL AND cnt.date2 IS NULL AND cnt.gr = gr & (1 << 2) AND balance.cid = cnt.id AND balance.yy = '2011' AND balance.mm = '3' AND balance.summa1 < 0 ORDER BY cnt.title";
    String query      = "select phone.phone1,phone.phone2,phone.phone3,phone.phone4,phone.phone5 from contract, contract_parameter_type_9 AS phone LEFT JOIN contract_parameter_type_5 as flag ON flag.cid = phone.cid where flag.pid=42 and flag.val=1 and contract.id = ? and contract.id order by phone.phone1";

    PreparedStatement ps_dolg = con.prepareStatement(query_dolg);
    ResultSet         rs_dolg = ps.executeQuery();

    int id;
    int summa;

    String title   = "";
    String comment = "";

    String phone  = "";
    String phone1 = "";
    String phone2 = "";
    String phone3 = "";
    String phone4 = "";

    while(rs_dolg.next())
    {
        title   = rs_dolg.getString("title");
        comment = rs_dolg.getString("comment");
        summa   = rs_dolg.getInt("summa1");
        id      = rs_dolg.getInt("id");

        print ("title -> " + title);
        print ("comment -> " + comment);
        print ("summa -> " + summa);
        print ("id -> " + id);

        PreparedStatement ps = con.prepareStatement( query );
        ps.setInt(1, id);
        ResultSet         rs = ps.executeQuery();

        phone  = rs.getString("phone1");
        phone1 = rs.getString("phone2");
        phone2 = rs.getString("phone3");
        phone3 = rs.getString("phone4");
        phone4 = rs.getString("phone5");

        print ("phone1 -> " + phone);
        print ("phone2 -> " + phone1);
        print ("phone3 -> " + phone2);
        print ("phone4 -> " + phone3);
        print ("phone5 -> " + phone4);
    }
}

    PreparedStatement ps_dolg = con.prepareStatement(query_dolg);
    ResultSet         rs_dolg = ps.executeQuery();

SELECT
    phone.phone1,
    phone.phone2,
    phone.phone3,
    phone.phone4,
    phone.phone5
FROM
    contract,
    contract_parameter_type_9 AS phone
LEFT JOIN
contract_parameter_type_5 AS flag ON flag.cid = phone.cid
WHERE
flag.pid = 42 AND
flag.val = 1 AND
contract.id = ? AND
contract.id
ORDER BY phone.phone1

contract.id = ? AND contract.id

SELECT
    phone.phone1,
    phone.phone2,
    phone.phone3,
    phone.phone4,
    phone.phone5
FROM
    contract,
    contract_parameter_type_9 AS phone
LEFT JOIN
contract_parameter_type_5 AS flag ON flag.cid = phone.cid
WHERE
flag.pid = 42 AND
flag.val = 1 AND
contract.id = 100 AND
contract.id
ORDER BY phone.phone1

      79222349158      
   73512610311   73519046043      
      79222317114   79090760990   
      79227106197      
      79222317114   79090760990   
      79193139776      
      79028901676      
      79630742833      
      79127992055      
      73519027405      
      73517259860   79630887357   
      79525089337      
      79127992055      
      73519027405      
      79193399111   79227130055   
      79026037191      
   73517371925   79823166224      
      79068014888      
   73512471747   79127792591      
      79226300333      
      79068643610   79517777193   79227040583
      79525089337      
      79127983578      
      79127919693      
      79630742833      
      79227028840      
      79127983578      
   73512610311   73519046043      
73432222222   73432172662         
73512111222            

import bitel.billing.server.util.*;
import bitel.billing.server.contract.bean.*;
import java.sql.*;
import java.math.*;

public void main( setup, con, conSlave )
{
    String query_dolg = "SELECT c.id, c.title, c.comment, cb.summa1 FROM contract_balance AS cb, contract AS c LEFT JOIN contract_payment AS pay ON c.id = cp.cid WHERE (cp.dt BETWEEN '2011-03-01' AND '2011-03-31') AND cp.id IS NULL AND c.date2 IS NULL AND c.gr = gr & (1 << 2) AND cb.cid = c.id AND cb.yy = '2011' AND cb.mm = '3' AND cb.summa1 < 0 ORDER BY c.title";
    String query      = "SELECT cpt9.phone1, cpt9.phone2, cpt9.phone3, cpt9.phone4, cpt9.phone5 FROM contract, contract_parameter_type_9 AS cpt9 LEFT JOIN contract_parameter_type_5 AS flag ON cpt5.cid = cpt9.cid WHERE cpt5.pid = 42 AND cpt5.val = 1 AND contract.id = ? ORDER BY cpt9.phone1";

    PreparedStatement ps_dolg = con.prepareStatement(query_dolg);
    ResultSet         rs_dolg = ps_dolg.executeQuery();

    int id;
    int summa;

    String title   = "";
    String comment = "";

    String phone  = "";
    String phone1 = "";
    String phone2 = "";
    String phone3 = "";
    String phone4 = "";

    while(rs_dolg.next())
    {
        id      = rs_dolg.getInt("id");
        title   = rs_dolg.getString("title");
        comment = rs_dolg.getString("comment");
        summa   = rs_dolg.getInt("summa1");

        print ("id      -> " + id);
        print ("title   -> " + title);
        print ("comment -> " + comment);
        print ("summa   -> " + summa);

        PreparedStatement ps = con.prepareStatement(query);
        ps.setInt(1, id);
        ResultSet         rs = ps.executeQuery();

        phone1 = rs.getString("phone1");
        phone2 = rs.getString("phone2");
        phone3 = rs.getString("phone3");
        phone4 = rs.getString("phone4");
        phone5 = rs.getString("phone5");

        print ("phone1 -> " + phone1);
        print ("phone2 -> " + phone2);
        print ("phone3 -> " + phone3);
        print ("phone4 -> " + phone4);
        print ("phone5 -> " + phone5);
    }
}

COMPLETE_TIME: 28.03.11 23:47:57
PROCESS_TIME: 21

OUT:


ERROR:


EXCEPTIONS:
Typed variable declaration : Method Invocation ps_dolg.executeQuery
Typed variable declaration : Method Invocation ps_dolg.executeQuery : at Line: 12 : in file: snark2 : ps_dolg .executeQuery ( )

Called from method: main : at Line: -1 : in file: <Called from Java Code> : <Compiled Java Code>
Target exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'cp.dt' in 'where clause'

   at bsh.BSHMethodInvocation.eval(Unknown Source)
   at bsh.BSHPrimaryExpression.eval(Unknown Source)
   at bsh.BSHPrimaryExpression.eval(Unknown Source)
   at bsh.BSHVariableDeclarator.eval(Unknown Source)
   at bsh.BSHTypedVariableDeclaration.eval(Unknown Source)
   at bsh.BSHBlock.evalBlock(Unknown Source)
   at bsh.BSHBlock.eval(Unknown Source)
   at bsh.BshMethod.invokeImpl(Unknown Source)
   at bsh.BshMethod.invoke(Unknown Source)
   at bsh.BshMethod.invoke(Unknown Source)
   at bsh.This.invokeMethod(Unknown Source)
   at bsh.This.invokeMethod(Unknown Source)
   at ru.bitel.bgbilling.kernel.script.server.bean.ScriptInstance.invoke(ScriptInstance.java:329)
   at bitel.billing.server.script.global.bean.GlobalScriptMachine.runScriptImpl(GlobalScriptMachine.java:130)
   at bitel.billing.server.script.global.bean.GlobalScriptMachine.runScript(GlobalScriptMachine.java:65)
   at bitel.billing.server.script.global.bean.GlobalScriptManager.executeGlobalScript(GlobalScriptManager.java:36)
   at bitel.billing.server.script.global.ActionExecuteScript$1ScriptThread.run(ActionExecuteScript.java:33)


WARNINGS:

SELECT
    c.id,
    c.title,
    c.comment,
    cb.summa1
FROM
    contract_balance AS cb,
    contract AS cnt
LEFT JOIN
    contract_payment AS cp ON c.id = cp.cid
WHERE
    cp.id IS NULL
    AND
    (cp.dt BETWEEN '2011-03-01' AND '2011-03-31')
    AND
    c.date2 IS NULL
    AND
    c.gr = gr & (1 << 2)
    AND
    cb.cid = c.id
    AND
    cb.yy = '2011'
    AND
    cb.mm = '3'
    AND
    cb.summa1 < 0
ORDER BY c.title

SELECT
    cid
FROM
    contract_balance
WHERE
    summa1 <= 0
    AND
    yy = YEAR(NOW())
    AND
    mm = MONTH(NOW())

SELECT
    cid
FROM
    contract_balance
WHERE
    summa2 = 0
    AND
    yy = YEAR(NOW())
    AND
    mm = MONTH(NOW())

SELECT
    cid
FROM
    contract_balance
WHERE
    summa1 <= 0
    AND
    summa2 = 0
    AND
    yy = YEAR(NOW())
    AND
    mm = MONTH(NOW())

SELECT
    title
FROM
    contract
WHERE
    id IN (
        SELECT
            cid
        FROM
            contract_balance
        WHERE
            summa1 <= 0
            AND
            summa2 = 0
            AND
            yy = YEAR(NOW())
            AND
            mm = MONTH(NOW())
    )

SELECT
    title
FROM
    contract
WHERE
    id IN (
        SELECT
            cid
        FROM
            contract_balance
        WHERE
            summa1 <= 0
            AND
            summa2 = 0
            AND
            yy = YEAR(NOW())
            AND
            mm = MONTH(NOW())
    )
    AND
    mode = 1

SELECT
    cid
FROM
    contract_balance
WHERE
    ...

SELECT
    cid,
    summa1
FROM
    contract_balance
WHERE
    ...

SELECT
    title
FROM
    contract
WHERE
    id IN (
        SELECT
            cid
        FROM
            contract_balance
        WHERE
            summa1 <= 0
            AND
            summa2 = 0
            AND
            yy = YEAR(NOW())
            AND
            mm = MONTH(NOW())
    )
    AND
    mode = 1

SELECT
    c.title,
    cb.summa1
FROM
    contract AS c
LEFT JOIN
    contract_balance AS cb
ON (c.id = cb.cid)
WHERE
    c.mode = 1
    AND
    cb.summa1 <= 0
    AND
    cb.summa2 = 0
    AND
    cb.yy = YEAR(NOW())
    AND
    cb.mm = MONTH(NOW())