public void testValues1() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvgreenplum); sqlparser.sqltext = "VALUES (1, 'one'), (2, 'two'), (3, 'three');"; Assert.IsTrue(sqlparser.parse() == 0); TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0); TValueClause valueClause = select.ValueClause; Assert.IsTrue(valueClause.ValueRows.size() == 3); TValueRowItem rowItem = valueClause.ValueRows.getValueRowItem(0); Assert.IsTrue(rowItem.ExprList.getExpression(0).ToString().Equals("1", StringComparison.CurrentCultureIgnoreCase)); Assert.IsTrue(rowItem.ExprList.getExpression(1).ToString().Equals("'one'", StringComparison.CurrentCultureIgnoreCase)); }
public void testSetSchema() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvgreenplum); sqlparser.sqltext = "ALTER TABLE myschema.distributors SET SCHEMA yourschema;"; Assert.IsTrue(sqlparser.parse() == 0); TAlterTableStatement alterTable = (TAlterTableStatement)sqlparser.sqlstatements.get(0); Assert.IsTrue(alterTable.TableName.ToString().Equals("myschema.distributors", StringComparison.CurrentCultureIgnoreCase)); TAlterTableOption ato = alterTable.AlterTableOptionList.getAlterTableOption(0); Assert.IsTrue(ato.OptionType == EAlterTableOptionType.setSchema); Assert.IsTrue(ato.SchemaName.ToString().Equals("yourschema", StringComparison.CurrentCultureIgnoreCase)); }
public void testDefaultAligntype() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "CREATE TABLE dept(deptno NUMBER(2),\n" + " dname VARCHAR2(14),\n" + " loc VARCHAR2(13)) "; sqlparser.parse(); option.defaultAligntype = TAlignStyle.AsWrapped; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("CREATE TABLE dept(deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13))", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }
internal virtual void remove(TGSqlParser sqlparser) { int i = sqlparser.parse(); if (i == 0) { TCustomSqlStatement stat = sqlparser.sqlstatements.get(0); getParserString(stat); result = stat.ToScript(); } else { Console.Error.WriteLine(sqlparser.Errormessage); } }
public void testSelect_fromclause_Style() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "SELECT last_name,\n" + " department_name dept_name \n" + "FROM employees,\n" + " departments; "; sqlparser.parse(); option.selectFromclauseStyle = TAlignStyle.AsWrapped; string result = FormatterFactory.pp(sqlparser, option); Console.WriteLine(result); Assert.IsTrue(result.Trim().Equals("SELECT last_name,\n" + " department_name dept_name\n" + "FROM employees, departments;", StringComparison.OrdinalIgnoreCase)); }
static void outputPlainFormat(TGSqlParser parser) { int ret = parser.parse(); if (ret == 0) { GFmtOpt option = GFmtOptFactory.newInstance(); string result = FormatterFactory.pp(parser, option); Console.WriteLine(result); } else { Console.WriteLine(parser.Errormessage); } }
public void testSelect_keywords_alignOption_update() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "UPDATE employees \n" + "SET department_id = 70 \n" + "WHERE employee_id = 113"; sqlparser.parse(); option.selectKeywordsAlignOption = TAlignOption.AloRight; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("UPDATE employees\n" + " SET department_id = 70\n" + " WHERE employee_id = 113", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }
public virtual void testSQLServerHintOption() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = @"SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (HASH GROUP, FAST 10);"; sqlparser.parse(); // Console.Out.WriteLine(formatSql(EDbVendor.dbvmssql,sqlparser.sqlstatements.get(0).ToScript())); Assert.IsTrue(verifyScript(EDbVendor.dbvmssql, sqlparser.sqlstatements.get(0).ToString(), sqlparser.sqlstatements.get(0).ToScript())); }
public virtual void testCrossApply() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "SELECT d.department_name, v.employee_id, v.last_name\n" + " FROM departments d CROSS APPLY (SELECT * FROM employees e\n" + " WHERE e.department_id = d.department_id) v"; sqlparser.parse(); //System.out.println(sqlparser.sqlstatements.get(0).ToScript()); Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, sqlparser.sqlstatements.get(0).ToString(), sqlparser.sqlstatements.get(0).ToScript())); // TScriptGenerator scriptGenerator = new TScriptGenerator( EDbVendor.dbvoracle ); // scriptGenerator.generateScript( sqlparser.sqlstatements.get( 0 ) ); // Assert.IsTrue( scriptGenerator.verifyScript( sqlparser.sqlstatements.get( 0 ) ) ); }
public virtual void testSQLServerCallTarget() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = @"SELECT STUFF(( SELECT N',' + CONVERT( nvarchar(18), Id ) FROM dbo.MyTable FOR XML PATH( '' ), TYPE ).value( N'(./text())[1]', N'nvarchar(MAX)' ), 1, 1, N'' )"; sqlparser.parse(); // Console.Out.WriteLine(formatSql(EDbVendor.dbvmssql,sqlparser.sqlstatements.get(0).ToScript())); Assert.IsTrue(verifyScript(EDbVendor.dbvmssql, sqlparser.sqlstatements.get(0).ToString(), sqlparser.sqlstatements.get(0).ToScript())); }
public void testExtractXML() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "SELECT warehouse_name,\n" + " EXTRACT(warehouse_spec, '/Warehouse/Docks')\n" + " \"Number of Docks\"\n" + " FROM warehouses\n" + " WHERE warehouse_name = 'San Francisco';"; Assert.IsTrue(sqlparser.parse() == 0); TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0); TResultColumn column = select.ResultColumnList.getResultColumn(1); TExpression expr = column.Expr; TFunctionCall f = expr.FunctionCall; Assert.IsTrue(f.FunctionType == EFunctionType.extractxml_t); Assert.IsTrue(f.XMLType_Instance.ToString().Equals("warehouse_spec", StringComparison.CurrentCultureIgnoreCase)); Assert.IsTrue(f.XPath_String.ToString().Equals("'/Warehouse/Docks'", StringComparison.CurrentCultureIgnoreCase)); }
public virtual void testModifyAlias() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = @"Select X as [Y]"; sqlparser.parse(); TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0); TResultColumn resultColumn = select.ResultColumnList.getResultColumn(0); TAliasClause aliasClause = resultColumn.AliasClause; aliasClause.AliasName = parser.parseObjectName("\"Y\""); //Console.WriteLine(sqlparser.sqlstatements.get(0).ToScript()); }
public void testCaseWhenThenInSameLine() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "SELECT productnumber,\n" + " name,\n" + " 'Price Range' = CASE WHEN listprice = 0 THEN 'Mfg item - not for resale' \n" + " WHEN listprice < 50 THEN 'Under $50' \n" + " WHEN listprice >= 50 AND listprice < 250 \n" + " THEN 'Under $250' WHEN listprice >= 250 \n" + " AND listprice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END \n" + "FROM production.product \n" + "ORDER BY productnumber; "; sqlparser.parse(); option.caseWhenThenInSameLine = true; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("SELECT productnumber,\n" + " name,\n" + " 'Price Range' = CASE\n" + " WHEN listprice = 0 THEN 'Mfg item - not for resale'\n" + " WHEN listprice < 50 THEN 'Under $50'\n" + " WHEN listprice >= 50\n" + " AND listprice < 250 THEN 'Under $250'\n" + " WHEN listprice >= 250\n" + " AND listprice < 1000 THEN 'Under $1000'\n" + " ELSE 'Over $1000'\n" + " END\n" + "FROM production.product\n" + "ORDER BY productnumber;", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }
public void testNetezzaSelectFromExternalTable() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza); sqlparser.sqltext = "insert into \"$hist_column_access_1\"(npsid, npsinstanceid, opid, logentryid, seqid, sessionid, dbid, dbname, schemaid, schemaname, tableid, tablename, columnid, columnname, usage) select 1, * from external '/nz/data.1.0/hist/loading/alc_20140215_151309.473439/alc_co_20140215_151309.473439' (npsinstanceid int, opid bigint, logentryid bigint, seqid integer, sessionid bigint, dbid int, dbname nvarchar (128), schemaid int , schemaname nvarchar (128), tableid int, tablename nvarchar (128), columnid int, columnname nvarchar (128), usage int) using( logdir '/nz/data.1.0/hist/loading/alc_20140215_151309.473439' encoding 'internal' escapechar '\\' fillrecord true ctrlchars true crinstring true)"; Assert.IsTrue(sqlparser.parse() == 0); TInsertSqlStatement insertSqlStatement = (TInsertSqlStatement)sqlparser.sqlstatements.get(0); TTable table = insertSqlStatement.tables.getTable(0); Assert.IsTrue(table.TableName.ToString().Equals("\"$hist_column_access_1\"", StringComparison.CurrentCultureIgnoreCase)); TSelectSqlStatement select = (TSelectSqlStatement)insertSqlStatement.SubQuery; table = select.tables.getTable(0); Assert.IsTrue(table.TableName.ToString().Equals("'/nz/data.1.0/hist/loading/alc_20140215_151309.473439/alc_co_20140215_151309.473439'", StringComparison.CurrentCultureIgnoreCase)); }
public virtual void testModifyTableInCreateTable() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvimpala); sqlparser.sqltext = "create table if not exists campaign_1 ( id int, name string )"; int ret = sqlparser.parse(); TCustomSqlStatement stmt = sqlparser.sqlstatements.get(0); TTable table = stmt.tables.getTable(0); table.TableName = parser.parseObjectName("prefix_." + table.TableName.ToString()); Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle , table.ToScript() , "prefix_.campaign_1" )); }
public void testSelectItemInNewLine() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "SELECT e.employee_id,\n" + " d.locatioin_id \n" + "FROM employees e,departments d \n" + "group by e.employee_id,d.locatioin_id,d.locatioin_id2 "; sqlparser.parse(); option.selectItemInNewLine = true; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("SELECT \n" + " e.employee_id,\n" + " d.locatioin_id\n" + "FROM employees e,\n" + " departments d\n" + "GROUP BY\n" + " e.employee_id,\n" + " d.locatioin_id,\n" + " d.locatioin_id2", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }
public void testMssqlCreateFunction_ReturnStmt() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "CREATE FUNCTION Sales.fn_SalesByStore (@storeid int)\n" + "RETURNS TABLE\n" + "AS\n" + "RETURN \n" + "(\n" + " SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'\n" + " FROM Production.Product AS P \n" + " JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID\n" + " JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID\n" + " WHERE SH.CustomerID = @storeid\n" + " GROUP BY P.ProductID, P.Name\n" + "); "; sqlparser.parse(); option.beStyleBlockIndentSize = 3; string result = FormatterFactory.pp(sqlparser, option); // System.out.println(result); Assert.IsTrue(result.Trim().Equals("CREATE FUNCTION sales.Fn_salesbystore (@storeid INT\n" + ") \n" + "RETURNS TABLE \n" + "AS \n" + " RETURN \n" + " (\n" + " SELECT p.productid,\n" + " p.name,\n" + " Sum(sd.linetotal) AS 'YTD Total'\n" + " FROM production.product AS p\n" + " JOIN sales.salesorderdetail AS sd\n" + " ON sd.productid = p.productid\n" + " JOIN sales.salesorderheader AS sh\n" + " ON sh.salesorderid = sd.salesorderid\n" + " WHERE sh.customerid = @storeid\n" + " GROUP BY p.productid,\n" + " p.name\n" + " );", StringComparison.OrdinalIgnoreCase)); }
public void testSelect_keywords_alignOption_delete() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "INSERT INTO employees\n" + "(employee_id,\n" + " first_name,\n" + " last_name,\n" + " email,\n" + " phone_number,\n" + " hire_date,\n" + " job_id,\n" + " salary,\n" + " commission_pct,\n" + " manager_id,\n" + " department_id) \n" + "VALUES(113,\n" + "'Louis',\n" + "'Popp',\n" + "'Ldd',\n" + "'515.124.222',\n" + "sysdate,\n" + "'Ac_account',\n" + "8900,\n" + "NULL,\n" + "205,\n" + "100)\n" + " \n" + " "; sqlparser.parse(); option.selectKeywordsAlignOption = TAlignOption.AloRight; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("INSERT INTO employees\n" + " (employee_id,\n" + " first_name,\n" + " last_name,\n" + " email,\n" + " phone_number,\n" + " hire_date,\n" + " job_id,\n" + " salary,\n" + " commission_pct,\n" + " manager_id,\n" + " department_id)\n" + " VALUES (113,\n" + " 'Louis',\n" + " 'Popp',\n" + " 'Ldd',\n" + " '515.124.222',\n" + " sysdate,\n" + " 'Ac_account',\n" + " 8900,\n" + " NULL,\n" + " 205,\n" + " 100)", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }
public virtual void testModifyColumnDefinition() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmysql); sqlparser.sqltext = @"CREATE TABLE `DEPT_MANAGER_TBL` ( `EMP_NO` INT(4) unsigned zerofill NOT NULL DEFAULT 1000, `DEPT_NO` CHAR(4) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `TO_DATE` GEOMETRY NOT NULL , `FROM_DATE` DATE NOT NULL, PRIMARY KEY (`EMP_NO`, `DEPT_NO`) ) COLLATE=utf8_unicode_ci;"; sqlparser.parse(); Console.WriteLine(sqlparser.sqlstatements.get(0).ToScript()); }
public void test1() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "CREATE OR REPLACE FORCE VIEW \"POTC_PO_LINES\" (\"A$CHART_OF_ACCOUNTS_NAME\", \"A$CONTRACT_NUMBER\", \"A$HAZARD_CLASS\", \"A$ITEM_DESCRIPTION\", \"A$JOB_NAME\", \"A$LEDGER_NAME\", \"A$LINE_CREATION_DATE\", \"A$LINE_TYPE\", \"A$OPERATING_UNIT_NAME\", \"A$PO_NUMBER\", \"A$TERMS\", \"A$UN_HAZARD_MATL_NUMBER\", \"A$VENDOR_NAME\", \"A$VENDOR_NUMBER\", \"A$VENDOR_PRODUCT_NUMBER\", \"A$ZZ__________________________\", \"ALLOW_PRICE_OVERRIDE_FLAG\", \"AMOUNT\", \"AUCTION_DISPLAY_NUMBER\", \"AUCTION_LINE_NUMBER\", \"AUTHORIZATION_STATUS\", \"BASE_CURRENCY_CODE\", \"BID_LINE_NUMBER\", \"BID_NUMBER\", \"CANCEL_DATE\", \"CANCEL_FLAG\", \"CANCEL_REASON\", \"CAPITAL_EXPENSE_FLAG\", \"CHART_OF_ACCOUNTS_NAME\", \"CLOSED_CODE\", \"CLOSED_DATE\", \"CLOSED_REASON\", \"CONSIGNED_CONSUMPTION_FLAG\", \"CONTRACT_NUMBER\", \"CONTRACTOR_ASSIGN_END_DATE\", \"CONTRACTOR_ASSIGN_START_DATE\", \"CONTRACTOR_FIRST_NAME\", \"CONTRACTOR_LAST_NAME\", \"CURRENCY\", \"HAZARD_CLASS\", \"INCOME_TAX_TYPE\", \"ITEM$INVENTORY_ITEM_ID\", \"ITEM$ORGANIZATION_ID\", \"ITEM$SV$ITEM\", \"ITEMCAT$CV$ITEM_CATEGORY_KFF\", \"ITEMCAT$CATEGORY_ID\", \"ITEM_DESCRIPTION\", \"ITEM_REVISION\", \"JOB_NAME\", \"LEDGER_NAME\", \"LINE_CREATION_DATE\", \"LINE_TYPE\", \"LIST_PRICE_PER_UNIT\", \"LIST_PRICE_PER_UNIT_BASE\", \"MANUAL_PRICE_CHANGE_FLAG\", \"MARKET_PRICE\", \"MARKET_PRICE_BASE\", \"MATCHING_BASIS\", \"NEGOTIATED_BY_PREPARER_FLAG\", \"NOTE_TO_VENDOR\", \"OPEN_FLAG\", \"OPERATING_UNIT_NAME\", \"OVER_TOLERANCE_ERROR_FLAG\", \"POLIN$ATTRIBUTE_CATEGORY\", \"POLIN$ATTRIBUTE_13\", \"POLIN$ORDER_SEQUENCE\", \"PO_LINE_NUMBER\", \"PO_NUMBER\", \"PRICE_TYPE\", \"PRICE_TYPE_LOOKUP_CODE\", \"PURCHASE_BASIS\", \"QTY_RECEIVED_TOLERANCE_PCT\", \"QUANTITY\", \"RATE\", \"RATE_DATE\", \"RATE_TYPE\", \"SET_OF_BOOKS_NAME\", \"SHIPMENT_LINE_NUMBER\", \"SHIPMENT_LINE_STATUS\", \"SHIPMENT_QUANTITY\", \"TAXABLE_FLAG\", \"TERMS\", \"TRANSACTION_REASON_CODE\", \"UN_HAZARD_MATL_NUMBER\", \"UNIT_OF_MEASURE\", \"UNIT_PRICE\", \"UNIT_PRICE_BASE\", \"UNORDERED_FLAG\", \"VEND$SUPPLIER_HOME_PAGE\", \"VENDOR_NAME\", \"VENDOR_NUMBER\", \"VENDOR_PRODUCT_NUMBER\", \"Z$$_________________________\", \"Z$POTC_PO_LINES\") AS SELECT XMAP.CHART_OF_ACCOUNTS_NAME A$Chart_Of_Accounts_Name, Contract_Number.SEGMENT1 A$Contract_Number, Hazard_Class.HAZARD_CLASS A$Hazard_Class, POLIN.ITEM_DESCRIPTION A$Item_Description, Job_Name.NAME A$Job_Name, XMAP.LEDGER_NAME A$Ledger_Name, POLIN.CREATION_DATE A$Line_Creation_Date, Line_Type.LINE_TYPE A$Line_Type, XMAP.OPERATING_UNIT_NAME A$Operating_Unit_Name, POHDR.SEGMENT1 A$PO_Number, Terms.NAME A$Terms, UN_Hazard_Matl_Number.UN_NUMBER A$UN_Hazard_Matl_Number, VEND.VENDOR_NAME A$Vendor_Name, VEND.SEGMENT1 A$Vendor_Number, POLIN.VENDOR_PRODUCT_NUM A$Vendor_Product_Number, 'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' A$ZZ__________________________, NVL(POLIN.ALLOW_PRICE_OVERRIDE_FLAG,'Y') Allow_Price_Override_Flag, NVL(POLIN.AMOUNT,0) Amount, POLIN.AUCTION_DISPLAY_NUMBER Auction_Display_Number, POLIN.AUCTION_LINE_NUMBER Auction_Line_Number, Authorization_Status.MEANING Authorization_Status, GLSOB.CURRENCY_CODE Base_Currency_Code, POLIN.BID_LINE_NUMBER Bid_Line_Number, POLIN.BID_NUMBER Bid_Number, POLIN.CANCEL_DATE Cancel_Date, NVL(POLIN.CANCEL_FLAG,'N') Cancel_Flag, POLIN.CANCEL_REASON Cancel_Reason, NVL(POLIN.CAPITAL_EXPENSE_FLAG,'N') Capital_Expense_Flag, XMAP.CHART_OF_ACCOUNTS_NAME Chart_Of_Accounts_Name, NVL(POLIN.CLOSED_CODE,'OPEN') Closed_Code, POLIN.CLOSED_DATE Closed_Date, POLIN.CLOSED_REASON Closed_Reason, NVL(POHDR.CONSIGNED_CONSUMPTION_FLAG,'N') Consigned_Consumption_Flag, Contract_Number.SEGMENT1 Contract_Number, POLIN.EXPIRATION_DATE Contractor_Assign_End_Date, POLIN.START_DATE Contractor_Assign_Start_Date, POLIN.CONTRACTOR_FIRST_NAME Contractor_First_Name, POLIN.CONTRACTOR_LAST_NAME Contractor_Last_Name, POHDR.CURRENCY_CODE Currency, Hazard_Class.HAZARD_CLASS Hazard_Class, POLIN.TYPE_1099 Income_Tax_Type, MSTK1.INVENTORY_ITEM_ID Item$Inventory_Item_Id, MSTK1.ORGANIZATION_ID Item$Organization_Id, MSTK1.SV$Item Item$SV$Item, ItemCat.CV$Item_Category_KFF ItemCat$CV$Item_Category_KFF, ItemCat.CATEGORY_ID ItemCat$Category_Id, POLIN.ITEM_DESCRIPTION Item_Description, POLIN.ITEM_REVISION Item_Revision, Job_Name.NAME Job_Name, XMAP.LEDGER_NAME Ledger_Name, POLIN.CREATION_DATE Line_Creation_Date, Line_Type.LINE_TYPE Line_Type, POLIN.LIST_PRICE_PER_UNIT List_Price_Per_Unit, POLIN.LIST_PRICE_PER_UNIT * NVL(POHDR.RATE, DECODE(POHDR.CURRENCY_CODE,GLSOB.CURRENCY_CODE,1,NULL)) List_Price_Per_Unit_Base, NVL(POLIN.MANUAL_PRICE_CHANGE_FLAG,'N') Manual_Price_Change_Flag, POLIN.MARKET_PRICE Market_Price, POLIN.MARKET_PRICE * NVL(POHDR.RATE, DECODE(POHDR.CURRENCY_CODE,GLSOB.CURRENCY_CODE,1,NULL)) Market_Price_Base, Matching_Basis.MEANING Matching_Basis, NVL(POLIN.NEGOTIATED_BY_PREPARER_FLAG,'N') Negotiated_By_Preparer_Flag, POLIN.NOTE_TO_VENDOR Note_To_Vendor, DECODE(NVL(POLIN.CLOSED_CODE,'OPEN')||NVL(POLIN.CANCEL_FLAG,'N'),'OPENN','Y','N') Open_Flag, XMAP.OPERATING_UNIT_NAME Operating_Unit_Name, NVL(POLIN.OVER_TOLERANCE_ERROR_FLAG,'N') Over_Tolerance_Error_Flag, POLIN.ATTRIBUTE_CATEGORY POLIN$ATTRIBUTE_CATEGORY, POLIN.ATTRIBUTE13 POLIN$Attribute_13, decode(POLIN.ATTRIBUTE_CATEGORY,'2052',POLIN.ATTRIBUTE1,null) POLIN$Order_Sequence, POLIN.LINE_NUM PO_Line_Number, POHDR.SEGMENT1 PO_Number, Price_Type.MEANING Price_Type, POLIN.PRICE_TYPE_LOOKUP_CODE Price_Type_Lookup_Code, Purchase_Basis.MEANING Purchase_Basis, POLIN.QTY_RCV_TOLERANCE Qty_Received_Tolerance_Pct, NVL(POLIN.QUANTITY,0) Quantity, POHDR.RATE Rate, POHDR.RATE_DATE Rate_Date, POHDR.RATE_TYPE Rate_Type, TO_CHAR(NULL) Set_Of_Books_Name, TO_NUMBER(NULL) Shipment_Line_Number, TO_CHAR(NULL) Shipment_Line_Status, TO_NUMBER(NULL) Shipment_Quantity, NVL(POLIN.TAXABLE_FLAG,'N') Taxable_Flag, Terms.NAME Terms, POLIN.TRANSACTION_REASON_CODE Transaction_Reason_Code, UN_Hazard_Matl_Number.UN_NUMBER UN_Hazard_Matl_Number, Unit_Of_Measure.DESCRIPTION Unit_Of_Measure, POLIN.UNIT_PRICE Unit_Price, POLIN.UNIT_PRICE * NVL(POHDR.RATE, DECODE(POHDR.CURRENCY_CODE,GLSOB.CURRENCY_CODE,1,NULL)) Unit_Price_Base, NVL(POLIN.UNORDERED_FLAG,'N') Unordered_Flag, VEND.ATTRIBUTE14 VEND$Supplier_Home_Page, VEND.VENDOR_NAME Vendor_Name, VEND.SEGMENT1 Vendor_Number, POLIN.VENDOR_PRODUCT_NUM Vendor_Product_Number, 'Z$$_________________________' Z$$_________________________, POLIN.rowid Z$POTC_PO_Lines FROM PO.PO_HEADERS_ALL Contract_Number, dev_602_medium_tc.XXK_Mtl_Cat_S2 ItemCat, HR.PER_JOBS Job_Name, PO.PO_UN_NUMBERS_TL UN_Hazard_Matl_Number, PO.PO_HAZARD_CLASSES_TL Hazard_Class, PO.PO_LINE_TYPES_TL Line_Type, INV.MTL_UNITS_OF_MEASURE_TL Unit_Of_Measure, AP.AP_TERMS_TL Terms, dev_602_medium_tc.N_PO_LOOKUPS_VL Matching_Basis, dev_602_medium_tc.N_PO_LOOKUPS_VL Purchase_Basis, dev_602_medium_tc.N_PO_LOOKUPS_VL Price_Type, dev_602_medium_tc.N_PO_LOOKUPS_VL Authorization_Status, dev_602_medium_tc.XXK_Sys_Item MSTK1, INV.MTL_SYSTEM_ITEMS_B ITEM, GL.GL_LEDGERS GLSOB, AP.FINANCIALS_SYSTEM_PARAMS_ALL FIN, AP.AP_SUPPLIERS VEND, PO.PO_HEADERS_ALL POHDR, dev_602_medium_tc.POTC_OU_ACL_Map_Base XMAP, PO.PO_LINES_ALL POLIN WHERE 'Copyright Noetix Corporation 1992-2010' is not null AND POLIN.PO_HEADER_ID = POHDR.PO_HEADER_ID AND NVL(POLIN.ORG_ID, -9999) = XMAP.ORG_ID AND POHDR.VENDOR_ID = VEND.VENDOR_ID AND POHDR.TYPE_LOOKUP_CODE = 'STANDARD' AND POLIN.ITEM_ID = ITEM.INVENTORY_ITEM_ID(+) AND NVL(ITEM.ORGANIZATION_ID,FIN.INVENTORY_ORGANIZATION_ID) = FIN.INVENTORY_ORGANIZATION_ID AND NVL(POHDR.ORG_ID,-9999) = NVL(FIN.ORG_ID,-9999) AND FIN.SET_OF_BOOKS_ID = GLSOB.LEDGER_ID AND POHDR.AUTHORIZATION_STATUS = Authorization_Status.LOOKUP_CODE(+) AND Authorization_Status.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS' AND Authorization_Status.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND Authorization_Status.SECURITY_GROUP_ID(+) = NOETIX_APPS_SECURITY_PKG.LOOKUP_SECURITY_GROUP (Authorization_Status.LOOKUP_TYPE(+),Authorization_Status.VIEW_APPLICATION_ID(+)) AND POLIN.PRICE_TYPE_LOOKUP_CODE = Price_Type.LOOKUP_CODE(+) AND Price_Type.LOOKUP_TYPE(+) = 'PRICE TYPE' AND Price_Type.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND Price_Type.SECURITY_GROUP_ID(+) = NOETIX_APPS_SECURITY_PKG.LOOKUP_SECURITY_GROUP (Price_Type.LOOKUP_TYPE(+),Price_Type.VIEW_APPLICATION_ID(+)) AND POLIN.PURCHASE_BASIS = Purchase_Basis.LOOKUP_CODE(+) AND Purchase_Basis.LOOKUP_TYPE(+) = 'PURCHASE BASIS' AND Purchase_Basis.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND Purchase_Basis.SECURITY_GROUP_ID(+) = NOETIX_APPS_SECURITY_PKG.LOOKUP_SECURITY_GROUP (Purchase_Basis.LOOKUP_TYPE(+),Purchase_Basis.VIEW_APPLICATION_ID(+)) AND POLIN.MATCHING_BASIS = Matching_Basis.LOOKUP_CODE(+) AND Matching_Basis.LOOKUP_TYPE(+) = 'MATCHING BASIS' AND Matching_Basis.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND Matching_Basis.SECURITY_GROUP_ID(+) = NOETIX_APPS_SECURITY_PKG.LOOKUP_SECURITY_GROUP (Matching_Basis.LOOKUP_TYPE(+),Matching_Basis.VIEW_APPLICATION_ID(+)) AND POHDR.TERMS_ID = Terms.TERM_ID(+) AND Terms.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.UNIT_MEAS_LOOKUP_CODE = Unit_Of_Measure.UNIT_OF_MEASURE(+) AND Unit_Of_Measure.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.LINE_TYPE_ID = Line_Type.LINE_TYPE_ID(+) AND Line_Type.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.HAZARD_CLASS_ID = Hazard_Class.HAZARD_CLASS_ID(+) AND Hazard_Class.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.UN_NUMBER_ID = UN_Hazard_Matl_Number.UN_NUMBER_ID(+) AND UN_Hazard_Matl_Number.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE AND POLIN.JOB_ID = Job_Name.JOB_ID(+) AND POLIN.CATEGORY_ID = ItemCat.CATEGORY_ID(+) AND POLIN.CONTRACT_ID = Contract_Number.PO_HEADER_ID(+) AND ITEM.INVENTORY_ITEM_ID = MSTK1.INVENTORY_ITEM_ID (+) AND ITEM.ORGANIZATION_ID = MSTK1.ORGANIZATION_ID (+)"; // System.out.println(sqlparser.sqltext); Assert.IsTrue(sqlparser.parse() == 0); string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("CREATE OR REPLACE FORCE VIEW \"POTC_PO_LINES\" (\"A$CHART_OF_ACCOUNTS_NAME\",\n" + " \"A$CONTRACT_NUMBER\",\n" + " \"A$HAZARD_CLASS\",\n" + " \"A$ITEM_DESCRIPTION\",\n" + " \"A$JOB_NAME\",\n" + " \"A$LEDGER_NAME\",\n" + " \"A$LINE_CREATION_DATE\",\n" + " \"A$LINE_TYPE\",\n" + " \"A$OPERATING_UNIT_NAME\",\n" + " \"A$PO_NUMBER\",\n" + " \"A$TERMS\",\n" + " \"A$UN_HAZARD_MATL_NUMBER\",\n" + " \"A$VENDOR_NAME\",\n" + " \"A$VENDOR_NUMBER\",\n" + " \"A$VENDOR_PRODUCT_NUMBER\",\n" + " \"A$ZZ__________________________\",\n" + " \"ALLOW_PRICE_OVERRIDE_FLAG\",\n" + " \"AMOUNT\",\n" + " \"AUCTION_DISPLAY_NUMBER\",\n" + " \"AUCTION_LINE_NUMBER\",\n" + " \"AUTHORIZATION_STATUS\",\n" + " \"BASE_CURRENCY_CODE\",\n" + " \"BID_LINE_NUMBER\",\n" + " \"BID_NUMBER\",\n" + " \"CANCEL_DATE\",\n" + " \"CANCEL_FLAG\",\n" + " \"CANCEL_REASON\",\n" + " \"CAPITAL_EXPENSE_FLAG\",\n" + " \"CHART_OF_ACCOUNTS_NAME\",\n" + " \"CLOSED_CODE\",\n" + " \"CLOSED_DATE\",\n" + " \"CLOSED_REASON\",\n" + " \"CONSIGNED_CONSUMPTION_FLAG\",\n" + " \"CONTRACT_NUMBER\",\n" + " \"CONTRACTOR_ASSIGN_END_DATE\",\n" + " \"CONTRACTOR_ASSIGN_START_DATE\",\n" + " \"CONTRACTOR_FIRST_NAME\",\n" + " \"CONTRACTOR_LAST_NAME\",\n" + " \"CURRENCY\",\n" + " \"HAZARD_CLASS\",\n" + " \"INCOME_TAX_TYPE\",\n" + " \"ITEM$INVENTORY_ITEM_ID\",\n" + " \"ITEM$ORGANIZATION_ID\",\n" + " \"ITEM$SV$ITEM\",\n" + " \"ITEMCAT$CV$ITEM_CATEGORY_KFF\",\n" + " \"ITEMCAT$CATEGORY_ID\",\n" + " \"ITEM_DESCRIPTION\",\n" + " \"ITEM_REVISION\",\n" + " \"JOB_NAME\",\n" + " \"LEDGER_NAME\",\n" + " \"LINE_CREATION_DATE\",\n" + " \"LINE_TYPE\",\n" + " \"LIST_PRICE_PER_UNIT\",\n" + " \"LIST_PRICE_PER_UNIT_BASE\",\n" + " \"MANUAL_PRICE_CHANGE_FLAG\",\n" + " \"MARKET_PRICE\",\n" + " \"MARKET_PRICE_BASE\",\n" + " \"MATCHING_BASIS\",\n" + " \"NEGOTIATED_BY_PREPARER_FLAG\",\n" + " \"NOTE_TO_VENDOR\",\n" + " \"OPEN_FLAG\",\n" + " \"OPERATING_UNIT_NAME\",\n" + " \"OVER_TOLERANCE_ERROR_FLAG\",\n" + " \"POLIN$ATTRIBUTE_CATEGORY\",\n" + " \"POLIN$ATTRIBUTE_13\",\n" + " \"POLIN$ORDER_SEQUENCE\",\n" + " \"PO_LINE_NUMBER\",\n" + " \"PO_NUMBER\",\n" + " \"PRICE_TYPE\",\n" + " \"PRICE_TYPE_LOOKUP_CODE\",\n" + " \"PURCHASE_BASIS\",\n" + " \"QTY_RECEIVED_TOLERANCE_PCT\",\n" + " \"QUANTITY\",\n" + " \"RATE\",\n" + " \"RATE_DATE\",\n" + " \"RATE_TYPE\",\n" + " \"SET_OF_BOOKS_NAME\",\n" + " \"SHIPMENT_LINE_NUMBER\",\n" + " \"SHIPMENT_LINE_STATUS\",\n" + " \"SHIPMENT_QUANTITY\",\n" + " \"TAXABLE_FLAG\",\n" + " \"TERMS\",\n" + " \"TRANSACTION_REASON_CODE\",\n" + " \"UN_HAZARD_MATL_NUMBER\",\n" + " \"UNIT_OF_MEASURE\",\n" + " \"UNIT_PRICE\",\n" + " \"UNIT_PRICE_BASE\",\n" + " \"UNORDERED_FLAG\",\n" + " \"VEND$SUPPLIER_HOME_PAGE\",\n" + " \"VENDOR_NAME\",\n" + " \"VENDOR_NUMBER\",\n" + " \"VENDOR_PRODUCT_NUMBER\",\n" + " \"Z$$_________________________\",\n" + " \"Z$POTC_PO_LINES\") \n" + "AS \n" + " SELECT xmap.chart_of_accounts_name a$chart_of_accounts_name,\n" + " contract_number.segment1 a$contract_number,\n" + " hazard_class.hazard_class a$hazard_class,\n" + " polin.item_description a$item_description,\n" + " job_name.NAME a$job_name,\n" + " xmap.ledger_name a$ledger_name,\n" + " polin.creation_date a$line_creation_date,\n" + " line_type.line_type a$line_type,\n" + " xmap.operating_unit_name a$operating_unit_name,\n" + " pohdr.segment1 a$po_number,\n" + " terms.NAME a$terms,\n" + " un_hazard_matl_number.un_number a$un_hazard_matl_number,\n" + " vend.vendor_name a$vendor_name,\n" + " vend.segment1 a$vendor_number,\n" + " polin.vendor_product_num a$vendor_product_number,\n" + " 'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' a$zz__________________________,\n" + " Nvl(polin.allow_price_override_flag,'Y') allow_price_override_flag,\n" + " Nvl(polin.amount,0) amount,\n" + " polin.auction_display_number auction_display_number,\n" + " polin.auction_line_number auction_line_number,\n" + " authorization_status.meaning authorization_status,\n" + " glsob.currency_code base_currency_code,\n" + " polin.bid_line_number bid_line_number,\n" + " polin.bid_number bid_number,\n" + " polin.cancel_date cancel_date,\n" + " Nvl(polin.cancel_flag,'N') cancel_flag,\n" + " polin.cancel_reason cancel_reason,\n" + " Nvl(polin.capital_expense_flag,'N') capital_expense_flag,\n" + " xmap.chart_of_accounts_name chart_of_accounts_name,\n" + " Nvl(polin.closed_code,'OPEN') closed_code,\n" + " polin.closed_date closed_date,\n" + " polin.closed_reason closed_reason,\n" + " Nvl(pohdr.consigned_consumption_flag,'N') consigned_consumption_flag,\n" + " contract_number.segment1 contract_number,\n" + " polin.expiration_date contractor_assign_end_date,\n" + " polin.start_date contractor_assign_start_date,\n" + " polin.contractor_first_name contractor_first_name,\n" + " polin.contractor_last_name contractor_last_name,\n" + " pohdr.currency_code currency,\n" + " hazard_class.hazard_class hazard_class,\n" + " polin.type_1099 income_tax_type,\n" + " mstk1.inventory_item_id item$inventory_item_id,\n" + " mstk1.organization_id item$organization_id,\n" + " mstk1.sv$item item$sv$item,\n" + " itemcat.cv$item_category_kff itemcat$cv$item_category_kff,\n" + " itemcat.category_id itemcat$category_id,\n" + " polin.item_description item_description,\n" + " polin.item_revision item_revision,\n" + " job_name.NAME job_name,\n" + " xmap.ledger_name ledger_name,\n" + " polin.creation_date line_creation_date,\n" + " line_type.line_type line_type,\n" + " polin.list_price_per_unit list_price_per_unit,\n" + " polin.list_price_per_unit * Nvl(pohdr.rate, Decode(pohdr.currency_code,glsob.currency_code,1,NULL)) list_price_per_unit_base,\n" + " Nvl(polin.manual_price_change_flag,'N') manual_price_change_flag,\n" + " polin.market_price market_price,\n" + " polin.market_price * Nvl(pohdr.rate, Decode(pohdr.currency_code,glsob.currency_code,1,NULL)) market_price_base,\n" + " matching_basis.meaning matching_basis,\n" + " Nvl(polin.negotiated_by_preparer_flag,'N') negotiated_by_preparer_flag,\n" + " polin.note_to_vendor note_to_vendor,\n" + " Decode(Nvl(polin.closed_code,'OPEN')||Nvl(polin.cancel_flag,'N'),'OPENN','Y','N') open_flag,\n" + " xmap.operating_unit_name operating_unit_name,\n" + " Nvl(polin.over_tolerance_error_flag,'N') over_tolerance_error_flag,\n" + " polin.attribute_category polin$attribute_category,\n" + " polin.attribute13 polin$attribute_13,\n" + " Decode(polin.attribute_category,'2052',polin.attribute1,NULL) polin$order_sequence,\n" + " polin.line_num po_line_number,\n" + " pohdr.segment1 po_number,\n" + " price_type.meaning price_type,\n" + " polin.price_type_lookup_code price_type_lookup_code,\n" + " purchase_basis.meaning purchase_basis,\n" + " polin.qty_rcv_tolerance qty_received_tolerance_pct,\n" + " Nvl(polin.quantity,0) quantity,\n" + " pohdr.rate rate,\n" + " pohdr.rate_date rate_date,\n" + " pohdr.rate_type rate_type,\n" + " To_char(NULL) set_of_books_name,\n" + " To_number(NULL) shipment_line_number,\n" + " To_char(NULL) shipment_line_status,\n" + " To_number(NULL) shipment_quantity,\n" + " Nvl(polin.taxable_flag,'N') taxable_flag,\n" + " terms.NAME terms,\n" + " polin.transaction_reason_code transaction_reason_code,\n" + " un_hazard_matl_number.un_number un_hazard_matl_number,\n" + " unit_of_measure.description unit_of_measure,\n" + " polin.unit_price unit_price,\n" + " polin.unit_price * Nvl(pohdr.rate, Decode(pohdr.currency_code,glsob.currency_code,1,NULL)) unit_price_base,\n" + " Nvl(polin.unordered_flag,'N') unordered_flag,\n" + " vend.attribute14 vend$supplier_home_page,\n" + " vend.vendor_name vendor_name,\n" + " vend.segment1 vendor_number,\n" + " polin.vendor_product_num vendor_product_number,\n" + " 'Z$$_________________________' z$$_________________________,\n" + " polin.ROWID z$potc_po_lines\n" + " FROM po.po_headers_all contract_number,\n" + " dev_602_medium_tc.xxk_mtl_cat_s2 itemcat,\n" + " hr.per_jobs job_name,\n" + " po.po_un_numbers_tl un_hazard_matl_number,\n" + " po.po_hazard_classes_tl hazard_class,\n" + " po.po_line_types_tl line_type,\n" + " inv.mtl_units_of_measure_tl unit_of_measure,\n" + " ap.ap_terms_tl terms,\n" + " dev_602_medium_tc.n_po_lookups_vl matching_basis,\n" + " dev_602_medium_tc.n_po_lookups_vl purchase_basis,\n" + " dev_602_medium_tc.n_po_lookups_vl price_type,\n" + " dev_602_medium_tc.n_po_lookups_vl authorization_status,\n" + " dev_602_medium_tc.xxk_sys_item mstk1,\n" + " inv.mtl_system_items_b item,\n" + " gl.gl_ledgers glsob,\n" + " ap.financials_system_params_all fin,\n" + " ap.ap_suppliers vend,\n" + " po.po_headers_all pohdr,\n" + " dev_602_medium_tc.potc_ou_acl_map_base xmap,\n" + " po.po_lines_all polin\n" + " WHERE 'Copyright Noetix Corporation 1992-2010' IS NOT NULL\n" + " AND polin.po_header_id = pohdr.po_header_id\n" + " AND Nvl(polin.org_id, -9999) = xmap.org_id\n" + " AND pohdr.vendor_id = vend.vendor_id\n" + " AND pohdr.type_lookup_code = 'STANDARD'\n" + " AND polin.item_id = item.inventory_item_id(+)\n" + " AND Nvl(item.organization_id,fin.inventory_organization_id) = fin.inventory_organization_id\n" + " AND Nvl(pohdr.org_id,-9999) = Nvl(fin.org_id,-9999)\n" + " AND fin.set_of_books_id = glsob.ledger_id\n" + " AND pohdr.authorization_status = authorization_status.lookup_code(+)\n" + " AND authorization_status.lookup_type(+) = 'AUTHORIZATION STATUS'\n" + " AND authorization_status.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND authorization_status.security_group_id(+) = noetix_apps_security_pkg.Lookup_security_group (authorization_status.lookup_type(+),authorization_status.view_application_id(+))\n" + " AND polin.price_type_lookup_code = price_type.lookup_code(+)\n" + " AND price_type.lookup_type(+) = 'PRICE TYPE'\n" + " AND price_type.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND price_type.security_group_id(+) = noetix_apps_security_pkg.Lookup_security_group (price_type.lookup_type(+),price_type.view_application_id(+))\n" + " AND polin.purchase_basis = purchase_basis.lookup_code(+)\n" + " AND purchase_basis.lookup_type(+) = 'PURCHASE BASIS'\n" + " AND purchase_basis.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND purchase_basis.security_group_id(+) = noetix_apps_security_pkg.Lookup_security_group (purchase_basis.lookup_type(+),purchase_basis.view_application_id(+))\n" + " AND polin.matching_basis = matching_basis.lookup_code(+)\n" + " AND matching_basis.lookup_type(+) = 'MATCHING BASIS'\n" + " AND matching_basis.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND matching_basis.security_group_id(+) = noetix_apps_security_pkg.Lookup_security_group (matching_basis.lookup_type(+),matching_basis.view_application_id(+))\n" + " AND pohdr.terms_id = terms.term_id(+)\n" + " AND terms.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.unit_meas_lookup_code = unit_of_measure.unit_of_measure(+)\n" + " AND unit_of_measure.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.line_type_id = line_type.line_type_id(+)\n" + " AND line_type.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.hazard_class_id = hazard_class.hazard_class_id(+)\n" + " AND hazard_class.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.un_number_id = un_hazard_matl_number.un_number_id(+)\n" + " AND un_hazard_matl_number.LANGUAGE (+) = noetix_env_pkg.get_language\n" + " AND polin.job_id = job_name.job_id(+)\n" + " AND polin.category_id = itemcat.category_id(+)\n" + " AND polin.contract_id = contract_number.po_header_id(+)\n" + " AND item.inventory_item_id = mstk1.inventory_item_id (+)\n" + " AND item.organization_id = mstk1.organization_id (+)", StringComparison.OrdinalIgnoreCase)); // System.out.println(result); }
public static void doSearch(String sqlText) { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); // sqlparser.sqltext = "select sal.income + sal.bonus * emp.age + 5 as real_sal,\n" + // " emp.name as title \n" + // "from employee emp, salary sal \n" + // "where emp.id=sal.eid"; // sqlparser.sqltext = "SELECT CASE \n" + // " WHEN \"employees\".\"firstname1\" = ''THEN \"employees\".\"lastname1\" \n" + // " ELSE \"employees\".\"firstname2\" + ' ' \n" + // " + \"employees\".\"lastname2\" \n" + // " END AS \"Full Name\" \n" + // "FROM \"NORTHWIND\".\"DBO\".\"employees\" \"Employees\" "; // sqlparser.sqltext = "SELECT e.ename as employeename,\n" + // " m.ename,\n" + // " d.name,\n" + // " e.first_name||d.dname,\n" + // " (select max(sal) from emp) as max_sal\n" + // "FROM employees e\n" + // " LEFT OUTER JOIN employees m ON ( e.mgr_id = m.id )\n" + // " LEFT OUTER JOIN department d ON ( e.department_id = d.department_id )"; // sqlparser.sqltext = "SELECT a.customerid, \n" + // " (SELECT CASE \n" + // " WHEN b.city = 'Berlin' THEN b.country + ' ' + b.city \n" + // " ELSE b.postalcode \n" + // " END \n" + // " FROM customers b \n" + // " WHERE a.customerid = b.customerid) AS Location \n" + // "FROM customers a "; sqlparser.sqltext = sqlText; int ret = sqlparser.parse(); if (ret == 0) { for (int i = 0; i < sqlparser.sqlstatements.size(); i++) { iterateStmt(sqlparser.sqlstatements.get(i)); } } else { Console.WriteLine(sqlparser.Errormessage); } }
public void testOwnStmtOfExpr() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = @"DELETE FROM employee e WHERE NOT EXISTS ( SELECT * FROM department d WHERE e.emp_dept = d.dpt_id)"; Assert.IsTrue(sqlparser.parse() == 0); TDeleteSqlStatement delete = (TDeleteSqlStatement)sqlparser.sqlstatements.get(0); TExpression condition = delete.WhereClause.Condition; TExpression subqueryExpr = condition.RightOperand; TSelectSqlStatement subquery = subqueryExpr.SubQuery; condition = subquery.WhereClause.Condition; TCustomSqlStatement ownStmt = condition.OwnerStmt; Assert.IsTrue(ownStmt.sqlstatementtype == ESqlStatementType.sstselect); }
public void testAlignJoinWithFromKeyword() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "SELECT p.name AS product,\n" + " p.listprice AS 'List Price',\n" + " p.discount AS 'discount' \n" + "FROM \n" + " production.product p \n" + " JOIN production.productsubcategory s \n" + " ON p.productsubcategoryid = s.productsubcategoryid \n" + "WHERE s.name LIKE @product \n" + " AND p.listprice < @maxprice;"; sqlparser.parse(); option.alignJoinWithFromKeyword = true; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("SELECT p.name AS product,\n" + " p.listprice AS 'List Price',\n" + " p.discount AS 'discount'\n" + "FROM production.product p\n" + "JOIN production.productsubcategory s\n" + " ON p.productsubcategoryid = s.productsubcategoryid\n" + "WHERE s.name LIKE @product\n" + " AND p.listprice < @maxprice;", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }
public void testIndentLen() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "DECLARE @s VARCHAR(1000), @s2 VARCHAR(10)"; sqlparser.parse(); option.linebreakAfterDeclare = true; option.indentLen = 3; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("DECLARE\n" + " @s VARCHAR(1000),\n" + " @s2 VARCHAR(10)", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }
/// <summary> /// don't use BEStyle_Function_BodyIndent, replace it by beStyleBlockIndentSize /// </summary> public void testBEStyle_Function_BodyIndent() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "CREATE PROCEDURE humanresources.Uspgetallemployees \n" + "AS \n" + "SELECT lastname,\n" + "firstname,\n" + "jobtitle,\n" + "department \n" + "FROM humanresources.vemployeedepartment; \n"; sqlparser.parse(); option.beStyleBlockIndentSize = 3; string result = FormatterFactory.pp(sqlparser, option); //System.out.println(result); Assert.IsTrue(result.Trim().Equals("CREATE PROCEDURE humanresources.uspgetallemployees \n" + "AS \n" + " SELECT lastname,\n" + " firstname,\n" + " jobtitle,\n" + " department\n" + " FROM humanresources.vemployeedepartment;", StringComparison.OrdinalIgnoreCase)); }
public void testCreatetable_Fieldlist_Align_option() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "CREATE TABLE dept(deptno NUMBER(2),\n" + " dname VARCHAR2(14),\n" + " loc VARCHAR2(13)) "; sqlparser.parse(); option.createtableFieldlistAlignOption = TAlignOption.AloRight; option.beStyleCreatetableLeftBEOnNewline = true; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("CREATE TABLE dept\n" + " (deptno NUMBER(2),\n" + " dname VARCHAR2(14),\n" + " loc VARCHAR2(13))", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }
public void testDefaultCommaOption() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "INSERT INTO employees\n" + " (employee_id,\n" + " first_name,\n" + " department_id) VALUES (113, NULL, 100);"; option.defaultCommaOption = TLinefeedsCommaOption.LfbeforeCommaWithSpace; option.insertColumnlistStyle = TAlignStyle.AsStacked; option.insertValuelistStyle = TAlignStyle.AsStacked; sqlparser.parse(); string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("INSERT INTO employees\n" + " (employee_id\n" + " , first_name\n" + " , department_id)\n" + "VALUES (113\n" + " , NULL\n" + " , 100);", StringComparison.OrdinalIgnoreCase)); // System.out.println(result); }
public static string formatSql(EDbVendor dbVendor, string inputQuery) { string Result = inputQuery; TGSqlParser sqlparser = new TGSqlParser(dbVendor); sqlparser.sqltext = inputQuery; int ret = sqlparser.parse(); if (ret == 0) { GFmtOpt option = GFmtOptFactory.newInstance(); option.caseFuncname = TCaseOption.CoNoChange; Result = FormatterFactory.pp(sqlparser, option); } return(Result); }
public void testEloPreserve() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "CREATE FUNCTION dbo.isoweek (@DATE datetime)\n" + "RETURNS INT\n" + "WITH EXECUTE AS caller\n" + "AS\n" + "BEGIN\n" + " DECLARE @ISOweek INT\n" + " \n" + " \n" + " SET @ISOweek= datepart(wk,@DATE)+1\n" + " -datepart(wk,CAST(datepart(yy,@DATE) AS CHAR(4))+'0104')\n" + " \n" + "--Special cases: Jan 1-3 may belong to the previous year\n" + " IF (@ISOweek=0)\n" + " SET @ISOweek=dbo.isoweek(CAST(datepart(yy,@DATE)-1\n" + " AS CHAR(4))+'12'+ CAST(24+datepart(DAY,@DATE) AS CHAR(2)))+1\n" + "--Special case: Dec 29-31 may belong to the next year\n" + " IF ((datepart(mm,@DATE)=12) AND\n" + " ((datepart(dd,@DATE)-datepart(dw,@DATE))>= 28))\n" + " SET @ISOweek=1\n" + " \n" + " \n" + " \n" + " \n" + " RETURN(@ISOweek)\n" + "END;\n" + "GO "; sqlparser.parse(); option.emptyLines = TEmptyLinesOption.EloPreserve; //option.insertBlankLineInBatchSqls = true; string result = FormatterFactory.pp(sqlparser, option); Assert.IsTrue(result.Trim().Equals("CREATE FUNCTION dbo.Isoweek (@DATE DATETIME\n" + ") \n" + "RETURNS INT WITH EXECUTE AS caller \n" + "AS \n" + " BEGIN \n" + " DECLARE @ISOweek INT \n" + "\n" + " SET @ISOweek= Datepart(wk,@DATE) + 1 - Datepart(wk,Cast(Datepart(yy,@DATE) AS CHAR(4))+'0104') \n" + "\n" + "--Special cases: Jan 1-3 may belong to the previous year\n" + " IF ( @ISOweek = 0 ) SET @ISOweek=dbo.Isoweek(Cast(Datepart(yy,@DATE)-1 AS CHAR(4))+'12'+ Cast(24+Datepart(DAY,@DATE) AS CHAR(2))) + 1 \n" + "--Special case: Dec 29-31 may belong to the next year\n" + " IF ( ( Datepart(mm,@DATE) = 12 )\n" + " AND ( ( Datepart(dd,@DATE) - Datepart(dw,@DATE) ) >= 28 ) ) SET @ISOweek=1 \n" + "\n" + "\n" + "\n" + "\n" + " RETURN(@ISOweek) \n" + " END;\n" + "GO", StringComparison.OrdinalIgnoreCase)); // System.out.println(result); }
public void testCTE_NewlineBeforeAs() { GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name); TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql); sqlparser.sqltext = "WITH mycte(x) AS (SELECT x = Convert( VARCHAR(1000), 'hello' ) UNION ALL \n" + " SELECT Convert( VARCHAR(1000), x + 'a' ) FROM mycte \n" + " WHERE Len( x ) < 10 UNION ALL SELECT Convert( VARCHAR(1000), x + 'b' ) \n" + " FROM mycte WHERE Len( x ) < 10)\n" + "SELECT x FROM mycte ORDER BY Len( x ), x; "; sqlparser.parse(); option.cteNewlineBeforeAs = false; string result = FormatterFactory.pp(sqlparser, option); //Console.WriteLine(result); Assert.IsTrue(result.Trim().Equals("WITH mycte(x) AS (SELECT x = CONVERT(VARCHAR(1000), 'hello')\n UNION ALL\n SELECT CONVERT(VARCHAR(1000), x + 'a')\n FROM mycte\n WHERE len(x) < 10\n UNION ALL\n SELECT CONVERT(VARCHAR(1000), x + 'b')\n FROM mycte\n WHERE len(x) < 10) \n SELECT x\n FROM mycte\n ORDER BY len(x),\n x;", StringComparison.OrdinalIgnoreCase)); //System.out.println(result); }