Description:
Consider two DML statements:
update report_column rc set rc.report_table_id = (select coalesce(id, 0) from report_table rt where left(rc.column_name, length(rt.alias)) = rt.alias);
and
update report_column rc set rc.report_table_id = coalesce((select id from report_table rt where left(rc.column_name, length(rt.alias)) = rt.alias), 0);
First one produces "Column 'report_table_id' cannot be null", last one executes normally. This statements are logically the same, so maybe first one should perform as well as the last...
How to repeat:
Please create tables:
CREATE TABLE `report_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`description` text,
`alias` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
CREATE TABLE `report_column` (
`REPORT_COLUMN_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`REPORT_ID` int(11) NOT NULL,
`COLUMN_NAME` varchar(3000) NOT NULL,
`DISPLAY_NAME` varchar(50) NOT NULL,
`ALIAS` varchar(255) DEFAULT NULL,
`report_table_id` bigint(20) NOT NULL,
PRIMARY KEY (`REPORT_COLUMN_ID`),
KEY `REPORT_ID` (`REPORT_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=249 DEFAULT CHARSET=latin1
insert data:
INSERT INTO `report_table` VALUES (1,'expense','SAP Invoices','e'),(2,'trans','Citibank transactions','t'),(3,'vendor','Vendors','v'),(4,'user_attr','User attributes','ua'),(5,'user','Users','u'),(6,'org_group','Business units','og');
INSERT INTO `report_column` VALUES (1,1,'\'Citibank\' as \'Source\'','Source',NULL,0),(2,1,'t.DATE as \'Trans Date\'','Trans Date',NULL,2),(3,1,'t.TRANS_POST_DATE as \'Trans Post Date\'','Trans Post Date',NULL,2),(4,1,'e.EXPENSE_DATE as \'Expense Date\'','Expense Date',NULL,1),(5,1,'concat(uab.ATTR_VALUE,\' \',ua.ATTR_VALUE) as \'User Name\'','User Name',NULL,0),(6,1,'vt.NAME as \'Trans Vendor Name\'','Trans Vendor Name',NULL,3),(7,1,'ve.NAME as \'Invoice Vendor Name\'','Invoice Vendor Name',NULL,3),(8,1,'t.PURCHASE_ORDER_ID as \'P O Number\'','P O Number',NULL,2),(9,1,'e.INVOICE_NUMBER as \'Invoice Number\'','Invoice Number',NULL,1),(10,1,'t.TDI as \'TDI\'','TDI',NULL,2),(11,1,'p.CARD_NUMBER as \'Account\'','Account',NULL,0),(12,1,'format(t.TAX_AMOUNT ,2) as \'Tax Amount\'','Tax Amount',NULL,0),(13,1,'format(t.TOTAL_AMOUNT,2) as \'Total Amount\'','total Amount',NULL,0),(14,1,'format(e.TOTAL_SUMMARY ,2) as \'Invoice Amount\'','Invoice Amount',NULL,0),(15,1,'e.SAP_DOC_NUMBER as \'SAP Doc Number\'','SAP Doc Number',NULL,1),(16,1,'og1.NUMBER as \'Buisness Unit 1\'','Buisness Unit 1',NULL,6),(17,1,'ve.NUMBER as \'SAP Vendor Number\'','SAP Vendor Number',NULL,3),(18,2,'\'American Express\' as \'Source\'','Source',NULL,0),(19,2,'t.DATE as \'Trans Date\'','Trans Date',NULL,2),(20,2,'t.TRANS_POST_DATE as \'Trans Post Date\'','Trans Post Date',NULL,2),(21,2,'e.EXPENSE_DATE as \'Expense Date\'','Expense Date',NULL,1),(22,2,'concat(uab.ATTR_VALUE,\' \',ua.ATTR_VALUE) as \'User Name\'','User Name',NULL,0),(23,2,'vt.NAME as \'Trans Vendor Name\'','Trans Vendor Name',NULL,3),(24,2,'ve.NAME as \'Invoice Vendor Name\'','Invoice Vendor Name',NULL,3),(25,2,'t.PURCHASE_ORDER_ID as \'P O Number\'','P O Number',NULL,2),(26,2,'e.INVOICE_NUMBER as \'Invoice Number\'','Invoice Number',NULL,1),(27,2,'t.TDI as \'TDI\'','TDI',NULL,2),(28,2,'p.CARD_NUMBER as \'Account\'','Account',NULL,0),(29,2,'format(t.TOTAL_AMOUNT,2) as \'Total Amount\'','total Amount',NULL,0),(30,2,'format(t.TAX_AMOUNT ,2) as \'Tax Amount\'','Tax Amount',NULL,0),(31,2,'format(e.TOTAL_SUMMARY ,2) as \'Invoice Amount\'','Invoice Amount',NULL,0),(32,2,'e.SAP_DOC_NUMBER as \'SAP Doc Number\'','SAP Doc Number',NULL,1),(33,2,'og1.NUMBER as \'Buisness Unit 1\'','Buisness Unit 1',NULL,6),(34,2,'ve.NUMBER as \'SAP Vendor Number\'','SAP Vendor Number',NULL,3),(35,3,'\'Citibank\' as \'Source\'','Source',NULL,0),(36,3,'t.DATE as \'Trans Date\'','Trans Date',NULL,2),(37,3,'t.TRANS_POST_DATE as \'Trans Post Date\'','Trans Post Date',NULL,2),(38,3,'concat(uab.ATTR_VALUE,\' \',ua.ATTR_VALUE) as \'User Name\'','User Name',NULL,0),(39,3,'vt.NAME as \'Trans Vendor Name\'','Trans Vendor Name',NULL,3),(40,3,'ve.NAME as \'Invoice Vendor Name\'','Invoice Vendor Name',NULL,3),(41,3,'t.PURCHASE_ORDER_ID as \'P O Number\'','P O Number',NULL,2),(42,3,'e.INVOICE_NUMBER as \'Invoice Number\'','Invoice Number',NULL,1),(43,3,'t.TDI as \'TDI\'','TDI',NULL,2),(44,3,'p.CARD_NUMBER as \'Account\'','Account',NULL,0),(45,3,'format(t.TOTAL_AMOUNT,2) as \'Total Amount\'','total Amount',NULL,0),(46,3,'format(t.TAX_AMOUNT ,2) as \'Tax Amount\'','Tax Amount',NULL,0),(47,3,'e.SAP_DOC_NUMBER as \'SAP Doc Number\'','SAP Doc Number',NULL,1),(48,3,'e.EXPENSE_DATE as \'Expense Date\'','Expense Date',NULL,1),(49,3,'format(e.TOTAL_SUMMARY ,2) as \'Invoice Amount\'','Invoice Amount',NULL,0),(50,3,'og1.NUMBER as \'Buisness Unit 1\'','Buisness Unit 1',NULL,6),(51,3,'ve.NUMBER as \'SAP Vendor Number\'','SAP Vendor Number',NULL,3),(52,4,'\'American Express\' as \'Source\'','Source',NULL,0),(53,4,'t.DATE as \'Trans Date\'','Trans Date',NULL,2),(54,4,'t.TRANS_POST_DATE as \'Trans Post Date\'','Trans Post Date',NULL,2),(55,4,'concat(uab.ATTR_VALUE,\' \',ua.ATTR_VALUE) as \'User Name\'','User Name',NULL,0),(56,4,'vt.NAME as \'Trans Vendor Name\'','Trans Vendor Name',NULL,3),(57,4,'ve.NAME as \'Invoice Vendor Name\'','Invoice Vendor Name',NULL,3),(58,4,'t.PURCHASE_ORDER_ID as \'P O Number\'','P O Number',NULL,2),(59,4,'e.INVOICE_NUMBER as \'Invoice Number\'','Invoice Number',NULL,1),(60,4,'t.TDI as \'TDI\'','TDI',NULL,2),(61,4,'p.CARD_NUMBER as \'Account\'','Account',NULL,0),(62,4,'format(t.TOTAL_AMOUNT,2) as \'Total Amount\'','total Amount',NULL,0),(63,4,'format(t.TAX_AMOUNT ,2) as \'Tax Amount\'','Tax Amount',NULL,0),(64,4,'e.SAP_DOC_NUMBER as \'SAP Doc Number\'','SAP Doc Number',NULL,1),(65,4,'e.EXPENSE_DATE as \'Expense Date\'','Expense Date',NULL,1),(66,4,'format(e.TOTAL_SUMMARY ,2) as \'Invoice Amount\'','Invoice Amount',NULL,0),(67,4,'og1.NUMBER as \'Buisness Unit 1\'','Buisness Unit 1',NULL,6),(68,4,'ve.NUMBER as \'SAP Vendor Number\'','SAP Vendor Number',NULL,3),(69,5,'concat(uab.ATTR_VALUE,\' \',ua.ATTR_VALUE) as \'User Name\'','User Name',NULL,0),(70,5,'t.DATE as \'Trans Date\'','Date',NULL,2),(71,5,'t.MERCHANT_NAME as \'Vendor Name\'','Vendor Name',NULL,2),(72,5,'format(t.TOTAL_AMOUNT,2) as \'Total Amount\'','Amount',NULL,0),(73,5,'og.NAME as \'Business Unit\'','Business Unit',NULL,6),(74,5,'t.PURCHASE_ORDER_ID as \'P O Number\'','P O Number',NULL,2),(75,5,'t.TDI as \'Invoice Number/TDI\'','Invoice Number/TDI',NULL,2),(76,5,'p.CARD_NUMBER as \'Account\'','Account',NULL,0),(77,7,'tc.STRING_VALUE as \'Status\'','Status',NULL,2),(78,7,'t.DATE as \'Trans Date\'','Date',NULL,2),(79,7,'v.NAME as \'Vendor Name\'','Vendor Name',NULL,3),(80,7,'format(t.TOTAL_AMOUNT,2) as \'Amount\'','Amount',NULL,0),(81,7,'t.TRANS_NUMBER as \'Transaction /Identification Number\'','Transaction/Identification Number',NULL,2),(82,7,'t.PURCHASE_ORDER_ID as \'PO Number\'','PO Number',NULL,2),(83,7,'t.TDI as \'Invoice Number/TDI\'','Invoice Number/TDI',NULL,2),(84,7,'p.CARD_NUMBER as \'Account\'','Account',NULL,0),(85,7,'og.NAME as \'Business Unit\'','Business Unit',NULL,6),(86,8,'vc.STRING_VALUE as \'Status\'','Status',NULL,3),(87,8,'vad.ATTR_VALUE as \'Date\'','Date',NULL,3),(88,8,'og.NAME as \'Business Unit\'','Business Unit',NULL,6),(89,8,'v.NUMBER as \'SAP Vendor Number\'','SAP Vendor Number',NULL,3),(90,8,'v.NAME as \'Vendor Name\'','Vendor Name',NULL,3),(91,8,'va.ATTR_VALUE as \'Tax ID\'','Tax Id',NULL,3),(92,8,'vcw.STRING_VALUE as \'Minority\'','Minority',NULL,3),(93,9,'ec.STRING_VALUE as \'Status\'','Status',NULL,1),(94,9,'\'SAP\' as \'Source\'','Source',NULL,0),(95,9,'e1.SAP_DOC_NUMBER as \'SAP Doc Number1\'','SAP Doc Number1',NULL,1),(96,9,'e1.INVOICE_NUMBER as \'Invoice Number \'','Invoice Number',NULL,1),(97,9,'format(e1.TOTAL_SUMMARY,2) as \'Amount1\'','Amount1',NULL,0),(98,9,'format(e2.TOTAL_SUMMARY,2) as \'Amount2\'','Amount2',NULL,0),(99,9,'n.DESCRIPTION as \'Notes\'','Notes',NULL,0),(100,9,'og1.NUMBER as \'Buisness Unit 1\'','Buisness Unit 1',NULL,6),(101,9,'v1.NUMBER as \'SAP Vendor Number1\'','SAP Vendor Number1',NULL,3),(105,10,'ec.STRING_VALUE as \'Status\'','Status',NULL,1);
then try to execute:
update report_column rc set rc.report_table_id = (select coalesce(id, 0) from report_table rt where left(rc.column_name, length(rt.alias)) = rt.alias);
and
update report_column rc set rc.report_table_id = coalesce((select id from report_table rt where left(rc.column_name, length(rt.alias)) = rt.alias), 0);
