public virtual void testRemoveIntoClause()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = @"SELECT * INTO newTbl FROM ( SELECT * FROM Instructor) AS tmp;";
sqlparser.parse();
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
// remove into clause
select.IntoClause = null;
// remove * in the select list
select.ResultColumnList.removeElementAt(0);
// add a new select list item
TResultColumn resultColumn = new TResultColumn();
resultColumn.Expr = sqlparser.parseExpression("count(*)");
TAliasClause aliasClause = new TAliasClause();
aliasClause.AliasName = sqlparser.parseObjectName("count");
aliasClause.HasAs = true;
resultColumn.AliasClause = aliasClause;
select.ResultColumnList.addResultColumn(resultColumn);
// Console.WriteLine(sqlparser.sqlstatements.get(0).ToScript());
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, @"select
count(*) as count
from
( select
*
from
Instructor) as tmp"
));
}
public virtual void testModifySelectList()
{
parser.sqltext = "select t1.f1, t2.f2 as f2 from table1 t1 left join table2 t2 on t1.f1 = t2.f2 ";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ResultColumnList.removeElementAt(1);
select.ResultColumnList.removeElementAt(0);
TResultColumn resultColumn1 = new TResultColumn();
resultColumn1.Expr = parser.parseExpression("t1.f3");
TAliasClause aliasClause1 = new TAliasClause();
aliasClause1.AliasName = parser.parseObjectName("f1");
aliasClause1.HasAs = true;
resultColumn1.AliasClause = aliasClause1;
select.ResultColumnList.addResultColumn(resultColumn1);
TResultColumn resultColumn2 = new TResultColumn();
resultColumn2.Expr = parser.parseExpression("t2.f3");
select.ResultColumnList.addResultColumn(resultColumn2);
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT t1.f3 AS f1,\n" +
" t2.f3\n" +
"FROM table1 t1\n" +
" LEFT JOIN table2 t2\n" +
" ON t1.f1 = t2.f2"
));
}
