private void parseSubQueryColumnDefinition(TCreateViewSqlStatement createView, TSelectSqlStatement stmt, TableMetaData viewMetaData, ColumnImpactModel columnImpactModel)
{
if (stmt.SetOperatorType != ESetOperatorType.none)
{
parseSubQueryColumnDefinition(createView, stmt.LeftStmt, viewMetaData, columnImpactModel);
parseSubQueryColumnDefinition(createView, stmt.RightStmt, viewMetaData, columnImpactModel);
}
else
{
int columnCount = stmt.ResultColumnList.size();
string[] aliasNames = new string[columnCount];
if (createView.ViewAliasClause != null)
{
columnCount = createView.ViewAliasClause.ViewAliasItemList.size();
aliasNames = new string[columnCount];
TViewAliasItemList items = createView.ViewAliasClause.ViewAliasItemList;
for (int i = 0; i < items.size(); i++)
{
aliasNames[i] = items.getViewAliasItem(i).Alias.ToString();
}
}
for (int i = 0; i < columnCount; i++)
{
TResultColumn resultColumn = stmt.ResultColumnList.getResultColumn(i);
parseColumnDefinition(resultColumn, viewMetaData, columnImpactModel, aliasNames[i]);
}
}
}
public View(TCreateViewSqlStatement view)
{
if (view == null)
{
throw new System.ArgumentException("Table arguments can't be null.");
}
id = ++Table.TABLE_ID;
this.viewObject = view;
TSourceToken startToken = viewObject.startToken;
TSourceToken endToken = viewObject.endToken;
if (viewObject.ViewName != null)
{
startToken = viewObject.ViewName.startToken;
endToken = viewObject.ViewName.endToken;
this.name = viewObject.ViewName.ToString();
}
else
{
this.name = "";
Console.Error.WriteLine();
Console.Error.WriteLine("Can't get view name. View is ");
Console.Error.WriteLine(view.ToString());
}
this.startPosition = new Tuple <long, long>(startToken.lineNo, startToken.columnNo);
this.endPosition = new Tuple <long, long>(endToken.lineNo, endToken.columnNo + endToken.astext.Length);
}
private void parseStatement(TCustomSqlStatement stmt)
{
if (!stmtList.Contains(stmt.ToString()))
{
stmtList.Add(stmt.ToString());
}
else
{
return;
}
if (stmt is TCreateViewSqlStatement)
{
TCreateViewSqlStatement createView = ((TCreateViewSqlStatement)stmt);
parseCreateView(createView);
}
else if (stmt is TCreateTableSqlStatement && ((TCreateTableSqlStatement)stmt).SubQuery != null)
{
TCreateTableSqlStatement createTable = ((TCreateTableSqlStatement)stmt);
parseCreateTable(createTable);
}
else if (stmt is TInsertSqlStatement && ((TInsertSqlStatement)stmt).SubQuery != null)
{
TInsertSqlStatement insert = ((TInsertSqlStatement)stmt);
parseInsertStmt(insert);
}
if (stmt is TUseDatabase)
{
TUseDatabase use = (TUseDatabase)stmt;
database = use.DatabaseName.ToString();
}
}
public virtual void testWithCheckOption()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvdb2);
sqlparser.sqltext = "CREATE VIEW V2 AS SELECT COL1 FROM V1 WITH check option";
Assert.IsTrue(sqlparser.parse() == 0);
TCreateViewSqlStatement createView = (TCreateViewSqlStatement)sqlparser.sqlstatements.get(0);
TRestrictionClause restrictionClause = createView.RestrictionClause;
Assert.IsTrue(restrictionClause.Type == TRestrictionClause.with_check_option);
}
public void testCTEInCreateView()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "CREATE OR REPLACE VIEW FCT_PI_CLAIM_EVENTS_IMPACT_SUMMARY_INSIGHT AS \n" + "\n" + "WITH CLAIM_LODGEMENT AS (\n" + "\n" + " SELECT CLAIM_ID\n" + "\t , EVENT_TS AS LODGEMENT_TIMESTAMP\n" + " FROM (\t\n" + "\t \n" + " SELECT \n" + " CCR_CC_HISTORY.CLAIMID AS CLAIM_ID\n" + " , CCR_CC_HISTORY.EVENTTIMESTAMP AS EVENT_TS \n" + "\t , ROW_NUMBER() OVER (PARTITION BY CLAIMID \n" + "\t ORDER BY CCR_CC_HISTORY.EVENTTIMESTAMP DESC\n" + "\t\t\t\t\t\t\t , CCR_CC_HISTORY.ID DESC)\n" + "\t\t\t\t\t\t\t AS OPEN_EVENT_DESC_SEQ\t \n" + " FROM CCR_CC_HISTORY CCR_CC_HISTORY\n" + " \n" + " LEFT JOIN (\n" + " \tSELECT CCR_CC_TRANSACTION.CLAIMID AS CLAIM_ID\n" + " , MIN(CCR_CC_TRANSACTION.CREATETIME) AS ACTIVATION_DATETIME\n" + " FROM CCR_CC_TRANSACTION CCR_CC_TRANSACTION \n" + " GROUP BY CCR_CC_TRANSACTION.CLAIMID \t\t\t\t\t \n" + "\t ) CLAIM_ACTIVATION\n" + "\t\t \n" + " ON CLAIM_ACTIVATION.CLAIM_ID\t = CCR_CC_HISTORY.CLAIMID\n" + " \n" + " JOIN CCR_CCTL_HISTORYTYPE CCR_CCTL_HISTORYTYPE\n" + " ON CCR_CCTL_HISTORYTYPE.ID = CCR_CC_HISTORY.\"xxxx\"\n" + " AND CCR_CCTL_HISTORYTYPE.NAME IN ('xxxx', 'xxxx') \n" + " AND CCR_CC_HISTORY.EXPOSUREID IS NULL \n" + "\n" + " WHERE CCR_CC_HISTORY.EVENTTIMESTAMP <= COALESCE(ACTIVATION_DATETIME, CCR_CC_HISTORY.EVENTTIMESTAMP) \n" + "\t \n" + ") AS OPEN_REOPEN_DATES\n" + "\n" + " WHERE OPEN_EVENT_DESC_SEQ = 1\n" + "\n" + ")\n" + ", TRANSACTION_SUMMARY AS (\n" + "\n" + "SELECT *\n" + "FROM INT_PI_CLAIM_FINANCIALS_LIVE\n" + "\n" + ")\n" + " SELECT \n" + " CCR_CC_CLAIM.ID AS CLAIM_ID\n" + "\t\t, CCR_CC_CLAIM.CLAIMNUMBER AS CLAIM_NUMBER\n" + "\t\t, CCR_CC_POLICY.POLICYNUMBER AS POLICY_NUMBER\n" + "\t\t\n" + "\t\t \n" + "\t\t, CCR_CCTL_SC_BRAND.NAME AS BRAND\n" + "\t\t, CCR_CC_CATASTROPHE.NAME AS CATASTROPHE_NAME \n" + " , CCR_CC_CATASTROPHE.CATASTROPHENUMBER AS CATASTROPHE_CODE\n" + "\t , CCR_CCTL_SC_CLAIMTYPE.NAME AS CLAIM_TYPE\t\n" + "\t\t, DATE(CCR_CC_CLAIM.CLOSEDATE) AS CLOSED_DATE\t \n" + "\t\t, INT_PI_CLAIM_EVENT.EVENT_NAME AS EVENT_NAME\n" + "\t , CCR_CCTL_SC_CLAIMPRIORITY.NAME AS HOME_CLAIM_CATEGORY\n" + " , DATE(CLAIM_LODGEMENT.LODGEMENT_TIMESTAMP) AS LODGEMENT_DATE\n" + "\t\t, DATE(CLAIM_LODGEMENT.LODGEMENT_TIMESTAMP) AS LODGEMENT_TIME\t\t\n" + "\t\t, COALESCE(CCR_CCTL_SC_LOSSCAUSE.NAME, CCR_CCTL_LOSSCAUSE.NAME)\n" + "\t\t AS LOSS_CAUSE\t\t\n" + "\t\t, DATE(CCR_CC_CLAIM.LOSSDATE) AS LOSS_DATE\n" + "\n" + "\t , CCR_CC_ADDRESS_LOSS.POSTALCODE AS LOSS_POSTCODE\n" + "\t , CCR_CCX_SC_PAFPOSTCODES.LOCALITY AS LOSS_POSTCODE_LABEL\t \n" + "\t , UPPER(CCR_CC_ADDRESS_LOSS.CITY) AS LOSS_SUBURB\n" + "\t , CCR_CCTL_STATE_LOSS.NAME AS LOSS_STATE\n" + "\t , CASE WHEN CCR_CCTL_LOSSTYPE.NAME = 'xxxx' \n" + "\t\t THEN 'Home' \n" + "\t\t\t ELSE CCR_CCTL_LOSSTYPE.NAME \n" + "\t\t END AS LOSS_TYPE\n" + "\t , CAST(CASE WHEN CCR_CCTL_LOSSTYPE.TYPECODE = 'xxxx' \n" + "\t AND CCR_CCTL_SC_BRAND.TYPECODE = 'xxxx' \n" + "\t\t \t THEN 'xxxx'\n" + "\t WHEN CCR_CCTL_LOSSTYPE.TYPECODE = 'xxxx' \n" + "\t\t\t AND CCR_CCTL_VEHICLESTYLE.TYPECODE IN ('xxxx','xxxx','xxxx','xxxx') \n" + "\t\t\t THEN 'xxxx'\n" + "\t\t\t ELSE 'xxxx'\n" + "\t END AS CHAR(1)) AS SPECIALIST_CLAIM_FLAG\t\n" + "\t , CAST(CASE WHEN CCR_CC_INCIDENT.VEHICLEOPERABLE = 0 \n" + "\t THEN 'xxxx' \n" + "\t\t\t ELSE 'xxxx' \n" + "\t\t END AS CHAR(1)) AS VEHICLE_DRIVABLE_FLAG\n" + "\t \t\t \n" + "\n" + " \n" + "\t , CAST(1 AS INTEGER) AS CLAIM_VOLUME\n" + "\t , TRANSACTION_SUMMARY.NET_INCURRED_EX_GST \n" + "\t , TRANSACTION_SUMMARY.NET_INCURRED AS SYSTEM_COST\t\t\n" + "\t \n" + "\t \n" + "\t , CCR_CC_CLAIM_MAXTS.MAX_COMMIT_TIMESTAMP\n" + " \n" + " FROM CCR_CC_CLAIM CCR_CC_CLAIM\n" + "\t \n" + "\t JOIN TRANSACTION_SUMMARY TRANSACTION_SUMMARY\n" + "\t ON TRANSACTION_SUMMARY.CLAIM_ID = CCR_CC_CLAIM.ID\n" + "\t\t \n" + " JOIN CCR_CC_POLICY CCR_CC_POLICY\n" + " ON CCR_CC_CLAIM.POLICYID = CCR_CC_POLICY.ID\n" + " \n" + " JOIN CCR_CCTL_SC_BRAND CCR_CCTL_SC_BRAND\n" + " ON CCR_CCTL_SC_BRAND.ID = CCR_CC_POLICY.SC_BRAND \t \n" + "\t \n" + " JOIN CCR_CCTL_LOSSTYPE CCR_CCTL_LOSSTYPE\n" + " ON CCR_CC_CLAIM.LOSSTYPE = CCR_CCTL_LOSSTYPE.ID\t \n" + "\t \n" + " JOIN CCR_CC_ADDRESS CCR_CC_ADDRESS_LOSS\n" + " ON CCR_CC_ADDRESS_LOSS.ID = CCR_CC_CLAIM.LOSSLOCATIONID \n" + "\t \n" + " JOIN CCR_CCTL_STATE CCR_CCTL_STATE_LOSS\n" + " ON CCR_CC_ADDRESS_LOSS.STATE = CCR_CCTL_STATE_LOSS.ID\t \n" + "\t \n" + "\t \n" + " JOIN (\n" + "\t SELECT POSTCODE\n" + "\t\t , LOCALITY\n" + "\t\t\t , ROW_NUMBER() OVER (PARTITION BY POSTCODE \n" + "\t\t\t ORDER BY LOCALITY) \n" + "\t\t\t\t\t\t\t\t\t\t AS ALPHA_ORDER \n" + "\t FROM CCR_CCX_SC_PAFPOSTCODES\n" + "\t\t ) CCR_CCX_SC_PAFPOSTCODES\n" + "\t\t \n" + " ON CCR_CCX_SC_PAFPOSTCODES.POSTCODE = CCR_CC_ADDRESS_LOSS.POSTALCODE\n" + " AND CCR_CCX_SC_PAFPOSTCODES.ALPHA_ORDER = 1 \t \n" + "\t \n" + " JOIN (SELECT MAX(COMMIT_TIMESTAMP) AS MAX_COMMIT_TIMESTAMP FROM CCR_CC_CLAIM) CCR_CC_CLAIM_MAXTS\n" + "\t ON 1=1\n" + "\t \n" + "LEFT JOIN CCR_CCTL_LOSSCAUSE CCR_CCTL_SC_LOSSCAUSE\n" + " ON CCR_CCTL_SC_LOSSCAUSE.ID = CCR_CC_CLAIM.SC_LOSSCAUSE \n" + "\t \n" + "LEFT JOIN CCR_CCTL_LOSSCAUSE CCR_CCTL_LOSSCAUSE\n" + " ON CCR_CCTL_LOSSCAUSE.ID = CCR_CC_CLAIM.LOSSCAUSE \n" + " \n" + "LEFT JOIN CCR_CCTL_SC_YESNOONLY CCR_CCTL_SC_YESNOONLY\n" + " ON CCR_CCTL_SC_YESNOONLY.ID = CCR_CC_CLAIM.SC_INCIDENTREPORT\n" + "\t \n" + "LEFT JOIN CLAIM_LODGEMENT CLAIM_LODGEMENT\t\t \n" + " ON CLAIM_LODGEMENT.CLAIM_ID = CCR_CC_CLAIM.ID\n" + "\t\t\n" + "LEFT JOIN CCR_CC_CATASTROPHE CCR_CC_CATASTROPHE\n" + " ON CCR_CC_CATASTROPHE.ID = CCR_CC_CLAIM.CATASTROPHEID \t\n" + "\t \n" + "LEFT JOIN INT_PI_CLAIM_EVENT INT_PI_CLAIM_EVENT\n" + " ON INT_PI_CLAIM_EVENT.CLAIM_ID = CCR_CC_CLAIM.ID\n" + "\t \n" + "LEFT JOIN CCR_CC_INCIDENT CCR_CC_INCIDENT\n" + " ON CCR_CC_INCIDENT.CLAIMID = CCR_CC_CLAIM.ID\n" + "\n" + "LEFT JOIN CCR_CCTL_LOSSPARTYTYPE CCR_CCTL_LOSSPARTYTYPE_PR\n" + " ON CCR_CCTL_LOSSPARTYTYPE_PR.ID = CCR_CC_INCIDENT.SC_LOSSPARTY\n" + "\t \n" + "LEFT JOIN CCR_CCTL_LOSSPARTYTYPE CCR_CCTL_LOSSPARTYTYPE_AUTO\n" + " ON CCR_CCTL_LOSSPARTYTYPE_AUTO.ID = CCR_CC_INCIDENT.VEHICLELOSSPARTY\t \n" + "\t \n" + "LEFT JOIN CCR_CC_VEHICLE CCR_CC_VEHICLE\n" + " ON CCR_CC_VEHICLE.ID = CCR_CC_INCIDENT.VEHICLEID\n" + "\t \n" + "LEFT JOIN CCR_CCTL_VEHICLESTYLE CCR_CCTL_VEHICLESTYLE\n" + " ON CCR_CCTL_VEHICLESTYLE.ID = CCR_CC_VEHICLE.STYLE \t \n" + "\t \n" + "LEFT JOIN CCR_CCTL_SC_CLAIMPRIORITY CCR_CCTL_SC_CLAIMPRIORITY\n" + " ON CCR_CCTL_SC_CLAIMPRIORITY.ID = CCR_CC_CLAIM.SC_USERCLAIMPRIORITY\n" + "\n" + "LEFT JOIN CCR_CCTL_SC_CLAIMTYPE CCR_CCTL_SC_CLAIMTYPE\n" + " ON CCR_CCTL_SC_CLAIMTYPE.ID\t = CCR_CC_CLAIM.SC_CLAIMTYPE \t \t \n" + "\n" + " WHERE CCR_CC_CLAIM.RETIRED = 0\n" + " AND CCR_CC_POLICY.RETIRED = 0\n" + "\t \n" + "\t AND COALESCE(CCR_CCTL_SC_YESNOONLY.NAME,'xxxx') <> 'xxxx'\n" + "\t \n" + "\t AND ( CCR_CCTL_LOSSTYPE.TYPECODE = 'xxxx' \n" + "\t OR ( CCR_CCTL_LOSSPARTYTYPE_AUTO.TYPECODE = 'xxxx' AND CCR_CCTL_LOSSTYPE.TYPECODE = 'xxxx')\n" + "\t\t )\n" + "\t\t \t \n" + ";";
Assert.IsTrue(sqlparser.parse() == 0);
TCreateViewSqlStatement viewSqlStatement = (TCreateViewSqlStatement)sqlparser.sqlstatements.get(0);
Assert.IsTrue(viewSqlStatement.ViewName.ToString().Equals("FCT_PI_CLAIM_EVENTS_IMPACT_SUMMARY_INSIGHT", StringComparison.CurrentCultureIgnoreCase));
TSelectSqlStatement select = viewSqlStatement.Subquery;
Console.WriteLine(select.CteList.size());
}
public void testViewAlias()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "CREATE OR REPLACE FORCE VIEW \"DEV_602_MEDIUM_TC\".\"AP20_INVOICE_DISTRIBUTIONS\" (\"A$ACCRUAL_POSTED_FLAG\", \"A$DISTR$ACCOUNT\", \"A$DISTR$COMPANY\", \"A$DISTR$DEPARTMENT\", \"A$DISTR$PRODUCT\", \"A$DISTR$SUB_ACCOUNT\", \"A$EXPENDITURE_ORGANIZATION_NAM\", \"A$GL_DATE\", \"A$INVOICE_DATE\", \"A$INVOICE_NUMBER\", \"A$POSTED_FLAG\", \"A$PROJECT_NAME\", \"A$PROJECT_NUMBER\", \"A$VENDOR_NAME\", \"A$VENDOR_SITE_CODE\", \"A$ZZ__________________________\", \"APLIAB$OPERATIONS_ACCOUNTING_F\", \"ACCRUAL_POSTED_FLAG\", \"AMOUNT\", \"AMOUNT_BASE\", \"ASSETS_ADDITION_CODE\", \"ASSETS_TRACKING_FLAG\", \"BASE_AMOUNT\", \"BASE_CURRENCY_CODE\", \"BASE_INVOICE_PRICE_VARIANCE\", \"BASE_QUANTITY_VARIANCE\", \"DESCRIPTION\", \"DISTR$ACCOUNT\", \"DISTR$COMPANY\", \"DISTR$DEPARTMENT\", \"DISTR$OPERATIONS_ACCOUNTING_FL\", \"DISTR$PRODUCT\", \"DISTR$SUB_ACCOUNT\", \"DISTRIBUTION_CREATION_DATE\", \"DISTRIBUTION_LINE_NUMBER\", \"EARLIEST_SETTLEMENT_DATE\", \"EXCHANGE_DATE\", \"EXCHANGE_RATE\", \"EXCHANGE_RATE_TYPE\", \"EXCHANGE_RATE_VARIANCE\", \"EXPENDITURE_ITEM_DATE\", \"EXPENDITURE_ORGANIZATION_NAME\", \"EXPENDITURE_TYPE\", \"EXPENSE_JUSTIFICATION\", \"FINAL_MATCH_FLAG\", \"GL_DATE\", \"HOLD_MATCH_STATUS\", \"INV$ATTRIBUTE_CATEGORY\", \"INV$MISC_VENDOR_ADDRESS\", \"INV$MISC_VENDOR_CITY\", \"INV$MISC_VENDOR_NAME\", \"INV$MISC_VENDOR_STATE\", \"INV$MISC_VENDOR_ZIP\", \"INCOME_TAX_REGION\", \"INCOME_TAX_TYPE\", \"INVOICE_CURRENCY_CODE\", \"INVOICE_DATE\", \"INVOICE_NUMBER\", \"INVOICE_PAYMENT_STATUS\", \"INVOICE_PRICE_VARIANCE\", \"INVOICE_PRICE_VARIANCE_BASE\", \"INVOICE_SOURCE\", \"INVOICE_TYPE_LOOKUP_CODE\", \"JOURNAL_BATCH_DESCRIPTION\", \"JOURNAL_BATCH_NAME\", \"JOURNAL_BATCH_POSTED_DATE\", \"JOURNAL_BATCH_STATUS\", \"LINE_TYPE_LOOKUP_CODE\", \"PA_ADDITION_FLAG\", \"PA_QUANTITY\", \"PERIOD_NAME\", \"POSTED_AMOUNT\", \"POSTED_AMOUNT_BASE\", \"POSTED_BASE_AMOUNT\", \"POSTED_FLAG\", \"PREPAY_AMOUNT_REMAINING\", \"PREPAY_AMOUNT_REMAINING_BASE\", \"PRICE_VAR$OPERATIONS_ACCOUNTIN\", \"PROJECT_NAME\", \"PROJECT_NUMBER\", \"QUANTITY_INVOICED\", \"QUANTITY_VARIANCE\", \"REVERSAL_FLAG\", \"STAT_AMOUNT\", \"TASK_NAME\", \"TASK_NUMBER\", \"UNIT_PRICE\", \"UNIT_PRICE_BASE\", \"VAT_CODE\", \"VENDOR_EXCHANGE_DATE\", \"VENDOR_EXCHANGE_RATE\", \"VENDOR_EXCHANGE_RATE_TYPE\", \"VENDOR_NAME\", \"VENDOR_PREPAY_AMOUNT\", \"VENDOR_PREPAY_AMOUNT_BASE\", \"VENDOR_SITE_CODE\", \"Z$$_________________________\", \"Z$AP20_INVOICE_DISTRIBUTIONS\", \"Z$AP20_INVOICES\", \"Z$GL_CHART_OF_ACCOUNTS\") AS\n" + " SELECT\n" + "/*+ RULE */\n" + " IDSTR.ACCRUAL_POSTED_FLAG A$Accrual_Posted_Flag,\n" + " Distr.SEGMENT3 A$Distr$Account,\n" + " Distr.SEGMENT1 A$Distr$Company,\n" + " Distr.SEGMENT2 A$Distr$Department,\n" + " Distr.SEGMENT5 A$Distr$Product,\n" + " Distr.SEGMENT4 A$Distr$Sub_Account,\n" + " Expenditure_Organization_Name.NAME A$Expenditure_Organization_Nam,\n" + " IDSTR.ACCOUNTING_DATE A$Gl_Date,\n" + " INV.INVOICE_DATE A$Invoice_Date,\n" + " INV.INVOICE_NUM A$Invoice_Number,\n" + " IDSTR.POSTED_FLAG A$Posted_Flag,\n" + " PROJ.NAME A$Project_Name,\n" + " PROJ.SEGMENT1 A$Project_Number,\n" + " VEND.VENDOR_NAME A$Vendor_Name,\n" + " VCODE.VENDOR_SITE_CODE A$Vendor_Site_Code,\n" + " 'A$ZZ__________________________Copyright Noetix Corporation 1992-2009'\n" + "A$ZZ__________________________,\n" + " APLiab.SEGMENT1 ||'-'||APLiab.SEGMENT2 ||'-'||APLiab.SEGMENT3\n" + "||'-'||APLiab.SEGMENT4 ||'-'||APLiab.SEGMENT5 APLiab$Operations_Accounting_F,\n" + " IDSTR.ACCRUAL_POSTED_FLAG Accrual_Posted_Flag,\n" + " NVL(IDSTR.AMOUNT,0) Amount,\n" + " NVL(IDSTR.BASE_AMOUNT,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,NVL(IDSTR.AMOUNT,0),NULL)) Amount_Base,\n" + " NVL(IDSTR.ASSETS_ADDITION_FLAG,'N') Assets_Addition_Code,\n" + " IDSTR.ASSETS_TRACKING_FLAG Assets_Tracking_Flag,\n" + " NVL(IDSTR.BASE_AMOUNT,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,NVL(IDSTR.AMOUNT,0),NULL)) Base_Amount,\n" + " BOOK.CURRENCY_CODE Base_Currency_Code,\n" + " IDSTR.BASE_INVOICE_PRICE_VARIANCE Base_Invoice_Price_Variance,\n" + " IDSTR.BASE_QUANTITY_VARIANCE Base_Quantity_Variance,\n" + " IDSTR.DESCRIPTION Description,\n" + " Distr.SEGMENT3 Distr$Account,\n" + " Distr.SEGMENT1 Distr$Company,\n" + " Distr.SEGMENT2 Distr$Department,\n" + " Distr.SEGMENT1 ||'-'||Distr.SEGMENT2 ||'-'||Distr.SEGMENT3\n" + "||'-'||Distr.SEGMENT4 ||'-'||Distr.SEGMENT5 Distr$Operations_Accounting_Fl,\n" + " Distr.SEGMENT5 Distr$Product,\n" + " Distr.SEGMENT4 Distr$Sub_Account,\n" + " IDSTR.CREATION_DATE Distribution_Creation_Date,\n" + " IDSTR.DISTRIBUTION_LINE_NUMBER Distribution_Line_Number,\n" + " INV.EARLIEST_SETTLEMENT_DATE Earliest_Settlement_Date,\n" + " IDSTR.EXCHANGE_DATE Exchange_Date,\n" + " IDSTR.EXCHANGE_RATE Exchange_Rate,\n" + " IDSTR.EXCHANGE_RATE_TYPE Exchange_Rate_Type,\n" + " IDSTR.EXCHANGE_RATE_VARIANCE Exchange_Rate_Variance,\n" + " IDSTR.EXPENDITURE_ITEM_DATE Expenditure_Item_Date,\n" + " Expenditure_Organization_Name.NAME Expenditure_Organization_Name,\n" + " IDSTR.EXPENDITURE_TYPE Expenditure_Type,\n" + " IDSTR.JUSTIFICATION Expense_Justification,\n" + " NVL(IDSTR.FINAL_MATCH_FLAG,'N') Final_Match_Flag,\n" + " IDSTR.ACCOUNTING_DATE Gl_Date,\n" + " SUBSTR (DECODE (IDSTR.MATCH_STATUS_FLAG, 'N', 'Not Tested for Approval',\n" + "'T', 'Failed Approval Testing', 'A', 'Approved', NULL,\n" + "'Not Tested for Approval'\n" + ", 'Undefined Value: ' || IDSTR.MATCH_STATUS_FLAG), 1, 23) Hold_Match_Status,\n" + " INV.ATTRIBUTE_CATEGORY INV$ATTRIBUTE_CATEGORY,\n" + " decode(INV.ATTRIBUTE_CATEGORY,\n" + "'One-Time',INV.ATTRIBUTE4,null) INV$Misc_Vendor_Address,\n" + " decode(\n" + "INV.ATTRIBUTE_CATEGORY,'One-Time',INV.ATTRIBUTE5,null) INV$Misc_Vendor_City,\n" + " decode(\n" + "INV.ATTRIBUTE_CATEGORY,'One-Time',INV.ATTRIBUTE3,null) INV$Misc_Vendor_Name,\n" + " decode(\n" + "INV.ATTRIBUTE_CATEGORY,'One-Time',INV.ATTRIBUTE6,null) INV$Misc_Vendor_State,\n" + " decode(\n" + "INV.ATTRIBUTE_CATEGORY,'One-Time',INV.ATTRIBUTE7,null) INV$Misc_Vendor_Zip,\n" + " IDSTR.INCOME_TAX_REGION Income_Tax_Region,\n" + " IDSTR.TYPE_1099 Income_Tax_Type,\n" + " INV.INVOICE_CURRENCY_CODE Invoice_Currency_Code,\n" + " INV.INVOICE_DATE Invoice_Date,\n" + " INV.INVOICE_NUM Invoice_Number,\n" + " DECODE(INV.PAYMENT_STATUS_FLAG,'N'\n" + ",'No','Y','Yes','P','Partial',INV.PAYMENT_STATUS_FLAG) Invoice_Payment_Status,\n" + " IDSTR.INVOICE_PRICE_VARIANCE Invoice_Price_Variance,\n" + " NVL(IDSTR.BASE_INVOICE_PRICE_VARIANCE,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,\n" + "IDSTR.INVOICE_PRICE_VARIANCE,NULL)) Invoice_Price_Variance_Base,\n" + " INV.SOURCE Invoice_Source,\n" + " INV.INVOICE_TYPE_LOOKUP_CODE Invoice_Type_Lookup_Code,\n" + " GLBTC.DESCRIPTION Journal_Batch_Description,\n" + " GLBTC.NAME Journal_Batch_Name,\n" + " GLBTC.POSTED_DATE Journal_Batch_Posted_Date,\n" + " GLBTC.STATUS Journal_Batch_Status,\n" + " IDSTR.LINE_TYPE_LOOKUP_CODE Line_Type_Lookup_Code,\n" + " NVL(IDSTR.PA_ADDITION_FLAG,'Y') Pa_Addition_Flag,\n" + " NVL(IDSTR.PA_QUANTITY,0) Pa_Quantity,\n" + " IDSTR.PERIOD_NAME Period_Name,\n" + " NVL(IDSTR.POSTED_AMOUNT,0) Posted_Amount,\n" + " NVL(IDSTR.POSTED_BASE_AMOUNT,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,NVL(IDSTR.POSTED_AMOUNT,0),NULL)) Posted_Amount_Base,\n" + " NVL(IDSTR.POSTED_BASE_AMOUNT,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,NVL(IDSTR.POSTED_AMOUNT,0),NULL)) Posted_Base_Amount,\n" + " IDSTR.POSTED_FLAG Posted_Flag,\n" + " NVL (IDSTR.PREPAY_AMOUNT_REMAINING, DECODE (INV.INVOICE_TYPE_LOOKUP_CODE,\n" + "'PREPAYMENT', DECODE(INV.PAYMENT_STATUS_FLAG,'Y', DECODE(\n" + "IDSTR.LINE_TYPE_LOOKUP_CODE,\n" + "'ITEM', IDSTR.AMOUNT,0), 0), 0)) Prepay_Amount_Remaining,\n" + " (NVL (IDSTR.PREPAY_AMOUNT_REMAINING, DECODE(INV.INVOICE_TYPE_LOOKUP_CODE,\n" + "'PREPAYMENT', DECODE(INV.PAYMENT_STATUS_FLAG,'Y', DECODE(\n" + "IDSTR.LINE_TYPE_LOOKUP_CODE,'ITEM', IDSTR.AMOUNT,0), 0), 0)) * NVL (\n" + "IDSTR.EXCHANGE_RATE, DECODE (INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE, 1,NULL))) Prepay_Amount_Remaining_Base,\n" + " Price_Var.SEGMENT1 ||'-'||Price_Var.SEGMENT2 ||'-'||Price_Var.SEGMENT3 ||\n" + "'-'\n" + "||Price_Var.SEGMENT4 ||'-'||Price_Var.SEGMENT5 Price_Var$Operations_Accountin,\n" + " PROJ.NAME Project_Name,\n" + " PROJ.SEGMENT1 Project_Number,\n" + " NVL(IDSTR.QUANTITY_INVOICED,0) Quantity_Invoiced,\n" + " IDSTR.QUANTITY_VARIANCE Quantity_Variance,\n" + " NVL(IDSTR.REVERSAL_FLAG,'N') Reversal_Flag,\n" + " IDSTR.STAT_AMOUNT Stat_Amount,\n" + " TASK.TASK_NAME Task_Name,\n" + " TASK.TASK_NUMBER Task_Number,\n" + " IDSTR.UNIT_PRICE Unit_Price,\n" + " (IDSTR.UNIT_PRICE)*NVL(IDSTR.EXCHANGE_RATE,\n" + "DECODE(INV.INVOICE_CURRENCY_CODE,BOOK.CURRENCY_CODE,1,NULL)) Unit_Price_Base,\n" + " APTXC.NAME Vat_Code,\n" + " INV.EXCHANGE_DATE Vendor_Exchange_Date,\n" + " INV.EXCHANGE_RATE Vendor_Exchange_Rate,\n" + " INV.EXCHANGE_RATE_TYPE Vendor_Exchange_Rate_Type,\n" + " VEND.VENDOR_NAME Vendor_Name,\n" + " CASE WHEN IDSTR.LINE_TYPE_LOOKUP_CODE ='PREPAY' OR (\n" + "IDSTR.LINE_TYPE_LOOKUP_CODE = 'TAX' AND\n" + "IDSTR.PREPAY_TAX_PARENT_ID\n" + "IS NOT NULL) THEN IDSTR.AMOUNT ELSE NULL END Vendor_Prepay_Amount,\n" + " CASE WHEN IDSTR.LINE_TYPE_LOOKUP_CODE ='PREPAY' OR (\n" + "IDSTR.LINE_TYPE_LOOKUP_CODE = 'TAX' AND\n" + "IDSTR.PREPAY_TAX_PARENT_ID IS NOT NULL) THEN NVL(IDSTR.BASE_AMOUNT,\n" + "DECODE(INV.INVOICE_CURRENCY_CODE,BOOK.CURRENCY_CODE,\n" + "NVL(IDSTR.AMOUNT,0),NULL)) ELSE NULL END Vendor_Prepay_Amount_Base,\n" + " VCODE.VENDOR_SITE_CODE Vendor_Site_Code,\n" + " 'Z$$_________________________' Z$$_________________________,\n" + " IDSTR.rowid Z$AP20_Invoice_Distributions,\n" + " INV.rowid Z$AP20_Invoices,\n" + " Distr.rowid Z$GL_Chart_Of_Accounts\n" + " FROM\n" + " HR.HR_ALL_ORGANIZATION_UNITS_TL Expenditure_Organization_Name,\n" + " GL.GL_CODE_COMBINATIONS Price_Var,\n" + " GL.GL_CODE_COMBINATIONS Distr,\n" + " GL.GL_CODE_COMBINATIONS APLiab,\n" + " PO.PO_VENDORS VEND,\n" + " PO.PO_VENDOR_SITES_ALL VCODE,\n" + " PA.PA_TASKS TASK,\n" + " PA.PA_PROJECTS_ALL PROJ,\n" + " AP.AP_TAX_CODES_ALL APTXC,\n" + " GL.GL_SETS_OF_BOOKS BOOK,\n" + " GL.GL_JE_BATCHES GLBTC,\n" + " GL.GL_JE_HEADERS GLHDR,\n" + " GL.GL_JE_LINES GLLIN,\n" + " GL.GL_IMPORT_REFERENCES GLREF,\n" + " AP.AP_ACCOUNTING_EVENTS_ALL AE,\n" + " AP.AP_AE_HEADERS_ALL AEH,\n" + " AP.AP_AE_LINES_ALL AEL,\n" + " AP.AP_INVOICES_ALL INV,\n" + " DEV_586_SMALL_DE.AP20_OU_ACL_Map_Base XMAP,\n" + " AP.AP_INVOICE_DISTRIBUTIONS_ALL IDSTR\n" + "WHERE 'Copyright Noetix Corporation 1992-2009' is not null\n" + " AND IDSTR.INVOICE_ID = INV.INVOICE_ID\n" + " AND NVL (IDSTR.ORG_ID, -9999) = XMAP.ORG_ID\n" + " AND XMAP.APPLICATION_INSTANCE = '45'\n" + " /*** SET_OF_BOOKS_ID => 1 ***/\n" + " /*** CHART_OF_ACCOUNTS_ID => 101 ***/\n" + " /*** ORG_ID => 204 ***/\n" + " AND IDSTR.SET_OF_BOOKS_ID = XMAP.SET_OF_BOOKS_ID\n" + " AND IDSTR.ACCOUNTING_EVENT_ID = AE.ACCOUNTING_EVENT_ID (+)\n" + " AND AE.SOURCE_TABLE (+)= 'AP_INVOICES'\n" + " AND (AE.SOURCE_ID IS NULL OR\n" + " AE.SOURCE_ID = INV.INVOICE_ID)\n" + " AND AE.ACCOUNTING_EVENT_ID = AEH.ACCOUNTING_EVENT_ID (+)\n" + " AND IDSTR.INVOICE_DISTRIBUTION_ID = AEL.SOURCE_ID (+)\n" + " AND IDSTR.DIST_CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID (+)\n" + " AND AEL.SOURCE_TABLE (+) = 'AP_INVOICE_DISTRIBUTIONS'\n" + " AND ( ( AEL.SOURCE_ID IS NULL) OR\n" + " ( AEL.AE_LINE_ID = ( SELECT MIN(AEL1.AE_LINE_ID)\n" + " FROM AP.AP_AE_LINES_ALL AEL1\n" + " WHERE AEL1.AE_HEADER_ID = AEL.AE_HEADER_ID\n" + " AND AEL1.SOURCE_TABLE = 'AP_INVOICE_DISTRIBUTIONS'\n" + " AND AEL1.SOURCE_ID = AEL.SOURCE_ID\n" + " AND AEL1.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID)) )\n" + " AND ((AEL.AE_HEADER_ID IS NULL OR AEH.AE_HEADER_ID IS NULL) OR\n" + " AEL.AE_HEADER_ID = AEH.AE_HEADER_ID)\n" + " AND (AEH.AE_HEADER_ID IS NULL OR\n" + " AEH.SET_OF_BOOKS_ID = IDSTR.SET_OF_BOOKS_ID)\n" + " AND AEL.GL_SL_LINK_ID = GLREF.GL_SL_LINK_ID (+)\n" + " AND GLREF.GL_SL_LINK_TABLE (+) = 'APECL'\n" + " AND GLREF.JE_HEADER_ID = GLLIN.JE_HEADER_ID (+)\n" + " AND GLREF.JE_LINE_NUM = GLLIN.JE_LINE_NUM (+)\n" + " AND GLLIN.JE_HEADER_ID = GLHDR.JE_HEADER_ID (+)\n" + " AND GLHDR.JE_BATCH_ID = GLBTC.JE_BATCH_ID (+)\n" + " AND IDSTR.TAX_CODE_ID = APTXC.TAX_ID (+)\n" + " AND BOOK.SET_OF_BOOKS_ID = XMAP.SET_OF_BOOKS_ID\n" + " AND BOOK.SET_OF_BOOKS_ID +0 = IDSTR.SET_OF_BOOKS_ID\n" + " AND NVL (AE.ORG_ID(+), IDSTR.ORG_ID) = IDSTR.ORG_ID\n" + " AND NVL (AEH.ORG_ID, XMAP.ORG_ID) =XMAP.ORG_ID\n" + " AND NVL (AEL.ORG_ID, XMAP.ORG_ID) =XMAP.ORG_ID\n" + " AND NVL (APTXC.ORG_ID, XMAP.ORG_ID) =XMAP.ORG_ID\n" + " AND NVL (INV.ORG_ID, -9999) =XMAP.ORG_ID\n" + " AND PROJ.PROJECT_ID(+) = IDSTR.PROJECT_ID\n" + " AND TASK.TASK_ID(+) = IDSTR.TASK_ID\n" + " AND INV.VENDOR_SITE_ID = VCODE.VENDOR_SITE_ID(+)\n" + " AND NVL (VCODE.ORG_ID, XMAP.ORG_ID ) = XMAP.ORG_ID\n" + " AND VEND.VENDOR_ID +0 = INV.VENDOR_ID\n" + " AND IDSTR.ACCTS_PAY_CODE_COMBINATION_ID = APLiab.CODE_COMBINATION_ID(+)\n" + " AND IDSTR.DIST_CODE_COMBINATION_ID = Distr.CODE_COMBINATION_ID\n" + " AND IDSTR.PRICE_VAR_CODE_COMBINATION_ID = Price_Var.CODE_COMBINATION_ID(+)\n" + " AND IDSTR.EXPENDITURE_ORGANIZATION_ID\n" + "= Expenditure_Organization_Name.ORGANIZATION_ID(+)\n" + " AND Expenditure_Organization_Name.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE";
Assert.IsTrue(sqlparser.parse() == 0);
TCreateViewSqlStatement createView = (TCreateViewSqlStatement)sqlparser.sqlstatements.get(0);
TViewAliasClause va = createView.ViewAliasClause;
TObjectName o = va.ViewAliasItemList.getViewAliasItem(0).Alias;
Assert.IsTrue(o.ToString().Equals("\"A$ACCRUAL_POSTED_FLAG\"", StringComparison.CurrentCultureIgnoreCase));
}
private static void AnalyzeCreateViewStmt(TCreateViewSqlStatement stmt)
{
TCreateViewSqlStatement createView = stmt;
Console.WriteLine("View name: {0}", createView.ViewName.ToString());
TViewAliasClause aliasClause = createView.ViewAliasClause;
if (aliasClause != null)
{
for (int i = 0; i < aliasClause.ViewAliasItemList.Count; i++)
{
Console.WriteLine("View alias: {0}", aliasClause.ViewAliasItemList.getViewAliasItem(i).ToString());
}
}
Console.WriteLine("View subquery: {0}", createView.Subquery.ToString());
AnalyzeStmt(createView.Subquery);
}
private void parseCreateView(TCreateViewSqlStatement createView)
{
if (createView.ViewName != null)
{
string tableName = createView.ViewName.TableString;
string tableSchema = createView.ViewName.SchemaString;
string databaseName = createView.ViewName.DatabaseString;
TableMetaData viewMetaData = new TableMetaData(vendor, strict);
viewMetaData.Name = tableName;
viewMetaData.SchemaName = tableSchema;
if (isNotEmpty(databaseName))
{
viewMetaData.CatalogName = databaseName;
}
else
{
viewMetaData.CatalogName = database;
}
viewMetaData.View = true;
if (!tableColumns.ContainsKey(viewMetaData))
{
tableColumns[viewMetaData] = new List <ColumnMetaData>();
}
else
{
IList <TableMetaData> tables = new List <TableMetaData>(tableColumns.Keys);
viewMetaData = (TableMetaData)tables[tables.IndexOf(viewMetaData)];
viewMetaData.View = true;
}
if (createView.Subquery != null)
{
ColumnImpact impact = new ColumnImpact(createView.Subquery.ToString(), createView.dbvendor, tableColumns, strict);
impact.ignoreTopSelect(true);
impact.Debug = false;
impact.ShowUIInfo = true;
impact.TraceErrorMessage = false;
impact.impactSQL();
ColumnImpactModel columnImpactModel = impact.generateModel();
parseSubQueryColumnDefinition(createView, createView.Subquery, viewMetaData, columnImpactModel);
}
}
}
