phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maryann Xue <maryann....@gmail.com>
Subject Re: SQL Query with multiple projection selections over multiple tables having LEFT OUTER JOINS returns completely null for random columns even when data is present
Date Tue, 01 Dec 2015 15:32:31 GMT
Hi Vivek,

Thank you for reporting the issue! Could you please file a JIRA? I'll look
at it.


Thanks,
Maryann

On Mon, Nov 30, 2015 at 7:09 AM, Vivek KT <vivek.kt@zycus.com> wrote:

> Hi,
>
>
>
> I’m facing a weird problem with the phoenix library
> (phoenix-4.4.0-HBase-0.98-client).
>
>
>
> I’m using Hadoop 2.2.0 and HBase 0.98.9.
>
>
>
> I’ve the following tables created in Hbase :
>
>
>
> 1.       TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDER_C
> (Primary Key : PURCHASEORDERID_C)
>
>
>
> *COLUMN_NAME*
>
> *TYPE_NAME*
>
> RELEASEDON_C
>
> TIMESTAMP
>
> REFERENCEPURCHASEORDERID_C
>
> VARCHAR
>
> APPROVEDAMOUNTRC_C
>
> DOUBLE
>
> RETROSPECTIVELETUSERDECIDE_C
>
> BOOLEAN
>
> INVOICEDAMOUNTRC_C
>
> DOUBLE
>
> NOTES_C
>
> VARCHAR
>
> CARDNUMBER_C
>
> VARCHAR
>
> REOPENEDPO_C
>
> BOOLEAN
>
> PAYMENTMETHOD_C
>
> VARCHAR
>
> FORECASTEDSPENDRC_C
>
> DOUBLE
>
> DELIVERYTO_C
>
> VARCHAR
>
> MODIFIEDBY_C
>
> VARCHAR
>
> DELIVERTOTYPE_C
>
> VARCHAR
>
> TOTALAMOUNT_C
>
> DOUBLE
>
> APPROVALTIMEHRS_C
>
> DOUBLE
>
> PURCHASEORDERNUMBER_C
>
> VARCHAR
>
> PURCHASEORDERID_C
>
> VARCHAR
>
> DELIVERON_C
>
> TIMESTAMP
>
> TAXAMOUNTRC_C
>
> DOUBLE
>
> REQUESTER_C
>
> VARCHAR
>
> PROJECTSETTINGSTATUS_C
>
> VARCHAR
>
> RETROSPECTIVESENDPOSUPPLIER_C
>
> BOOLEAN
>
> CLOSETIMEDAYS_C
>
> DOUBLE
>
> CLOSETIMEHRS_C
>
> DOUBLE
>
> CREATEDBY_C
>
> VARCHAR
>
> DEVIATIONPRCENT_C
>
> DOUBLE
>
> VALIDITYTO_C
>
> TIMESTAMP
>
> FREIGHTTAXAMOUNT_C
>
> DOUBLE
>
> LOCATIONCODE_C
>
> VARCHAR
>
> PROJECT_C
>
> VARCHAR
>
> PAIDAMOUNTRC_C
>
> DOUBLE
>
> REJECTEDAMOUNTRC_C
>
> DOUBLE
>
> NONINVOICEAMOUNT_C
>
> DOUBLE
>
> AMOUNTTOBEAPPROVEDRC_C
>
> DOUBLE
>
> ASSETCODESETTING_C
>
> BOOLEAN
>
> APPROVALTIMEDAYS_C
>
> DOUBLE
>
> STATUSCOMMENTS_C
>
> VARCHAR
>
> SUPPLIERERPID_C
>
> VARCHAR
>
> CONTRACTNUMBER_C
>
> VARCHAR
>
> DISCOUNTTYPE_C
>
> VARCHAR
>
> DELIVERTOLEVEL_C
>
> INTEGER
>
> ALLOWINVOICE_C
>
> BOOLEAN
>
> PAYMENTTERMID_C
>
> VARCHAR
>
> ADVANCEAMOUNTRC_C
>
> DOUBLE
>
> SUPPCURRENCY_C
>
> VARCHAR
>
> CHARGEDAMOUNT_C
>
> DOUBLE
>
> RELEASEDONYEAR_C
>
> INTEGER
>
> BASECURRENCY_C
>
> VARCHAR
>
> CONFIRMEDON_C
>
> TIMESTAMP
>
> BASETOTAL_C
>
> DOUBLE
>
> SPLITCOSTINGTYPE_C
>
> VARCHAR
>
> DEVIATION_C
>
> DOUBLE
>
> SHIPTOADDRESSSTR_C
>
> VARCHAR
>
> SUPPLOCATIONSTR_C
>
> VARCHAR
>
> DISCOUNTVALUE_C
>
> DOUBLE
>
> RESUBMITIONCOUNT_C
>
> INTEGER
>
> RECEIVEDAMOUNT_C
>
> DOUBLE
>
> ORDERAUTHORIZATIONTYPE_C
>
> VARCHAR
>
> AMOUNTTOBEAPPROVED_C
>
> DOUBLE
>
> TOTALAMOUNTRC_C
>
> DOUBLE
>
> SUPPPOCONTACTEMAIL_C
>
> VARCHAR
>
> NAME_C
>
> VARCHAR
>
> ATTACHMENTIDS_C
>
> VARCHAR
>
> CARDTYPE_C
>
> VARCHAR
>
> BUSINESSUNITCODE_C
>
> VARCHAR
>
> APPRINVOICEAMNTRC_C
>
> DOUBLE
>
> SPLITCOSTINGLEVEL_C
>
> VARCHAR
>
> PROCESSEFORMID_C
>
> VARCHAR
>
> UTILIZEBUDGET_C
>
> BOOLEAN
>
> AUTOUPDATE_C
>
> BOOLEAN
>
> ITEMS_C
>
> VARCHAR
>
> QUOTATIONNO_C
>
> VARCHAR
>
> AMENDCOUNT_C
>
> INTEGER
>
> CONTRACTID_C
>
> VARCHAR
>
> TOTALORDERVALUE_C
>
> DOUBLE
>
> ESTIMATEDDELIVERON_C
>
> TIMESTAMP
>
> SUPPPOCONTACT_C
>
> VARCHAR
>
> AMENDSTATUS_C
>
> VARCHAR
>
> CHARGEDTAXAMOUNT_C
>
> DOUBLE
>
> DEVIATIONRC_C
>
> DOUBLE
>
> SOURCETYPE_C
>
> VARCHAR
>
> ERPID_C
>
> VARCHAR
>
> GROSSTOTALAMOUNT_C
>
> DOUBLE
>
> HIDEBPOVALUESETTINGUSERDECIDE_C
>
> BOOLEAN
>
> RELEASENUMBER_C
>
> INTEGER
>
> DYNAMICFORMID_C
>
> VARCHAR
>
> FORECASTEDSPEND_C
>
> DOUBLE
>
> DISCOUNTAMOUNTRC_C
>
> DOUBLE
>
> TAXES_C
>
> VARCHAR
>
> VALIDITYFROM_C
>
> TIMESTAMP
>
> DYNAMICINSTANCEID_C
>
> VARCHAR
>
> SHIPTOCODE_C
>
> VARCHAR
>
> EXTERNALID_C
>
> VARCHAR
>
> INVOICEUNTILDATE_C
>
> TIMESTAMP
>
> PARENTPURCHASEORDERID_C
>
> VARCHAR
>
> DISCOUNTLEVEL_C
>
> VARCHAR
>
> ADVANCEAMOUNT_C
>
> DOUBLE
>
> CONSUMEDORDERVALUE_C
>
> DOUBLE
>
> APPRINVOICEAMNT_C
>
> DOUBLE
>
> DISCOUNT_C
>
> DOUBLE
>
> GROSSTOTALAMOUNTRC_C
>
> DOUBLE
>
> INVOICESTATUS_C
>
> VARCHAR
>
> SUPPLIERID_C
>
> VARCHAR
>
> PAYMENTTERMNAME_C
>
> VARCHAR
>
> PAIDAMOUNT_C
>
> DOUBLE
>
> ARCHIVE_C
>
> BOOLEAN
>
> PAYMENTINAPPROVALRC_C
>
> DOUBLE
>
> PTGLACCOUNTSETTING_C
>
> BOOLEAN
>
> SUPPLIERPOCONTACTTYPE_C
>
> VARCHAR
>
> PAYMENTINAPPROVAL_C
>
> DOUBLE
>
> REFERENCEVALUE_C
>
> VARCHAR
>
> VERSION_C
>
> INTEGER
>
> REJECTEDAMOUNT_C
>
> DOUBLE
>
> SUPPLIERNAME_C
>
> VARCHAR
>
> APPLYNOTAXES_C
>
> BOOLEAN
>
> UNRECEIVEDAMOUNTRC_C
>
> DOUBLE
>
> SHIPTOCODETYPE_C
>
> VARCHAR
>
> INVOICEDAMOUNT_C
>
> DOUBLE
>
> APPROVEDON_C
>
> TIMESTAMP
>
> COMPANYCODE_C
>
> VARCHAR
>
> NONINVOICEAMOUNTRC_C
>
> DOUBLE
>
> STATUS_C
>
> VARCHAR
>
> SUBMITTEDAMOUNT_C
>
> DOUBLE
>
> ASSIGNPROJECT_C
>
> BOOLEAN
>
> REQUIRERECEIPT_C
>
> BOOLEAN
>
> MODIFIEDON_C
>
> TIMESTAMP
>
> SUBMITTEDON_C
>
> TIMESTAMP
>
> RELEASETIMEDAYS_C
>
> DOUBLE
>
> SENDPOTOSUPPLIER_C
>
> BOOLEAN
>
> APPROVEDAMOUNT_C
>
> DOUBLE
>
> UNRECEIVEDAMOUNT_C
>
> DOUBLE
>
> REFERENCETYPE_C
>
> VARCHAR
>
> TENANTID_C
>
> VARCHAR
>
> PAYMENTSTATUS_C
>
> VARCHAR
>
> TERMSANDCONDITIONS_C
>
> VARCHAR
>
> ERROR_C
>
> BOOLEAN
>
> PURCHASETYPESETTING_C
>
> BOOLEAN
>
> BASEEXCHANGERATE_C
>
> DOUBLE
>
> PURCHASETYPE_C
>
> VARCHAR
>
> RECEIPTSTATUS_C
>
> VARCHAR
>
> RELEASETIMEHRS_C
>
> DOUBLE
>
> SUPPADDRESSID_C
>
> VARCHAR
>
> DELIVERYTERMCODE_C
>
> VARCHAR
>
> FREIGHTCHARGES_C
>
> DOUBLE
>
> CREATEDON_C
>
> TIMESTAMP
>
> SUPPLIERPREFERENCETYPE_C
>
> VARCHAR
>
> TAXAMOUNT_C
>
> DOUBLE
>
> INVOICETOCODE_C
>
> VARCHAR
>
> REJECTEDITEMCOUNT_C
>
> INTEGER
>
> RELEASEDONQUARTER_C
>
> INTEGER
>
> BILLTOCODE_C
>
> VARCHAR
>
> TYPE_C
>
> VARCHAR
>
> FREIGHTAXAMOUNT_C
>
> DOUBLE
>
> MASI_C
>
> BOOLEAN
>
> RETROSPECTIVEPURCHASE_C
>
> VARCHAR
>
> SUPPLIERADDRESSERPID_C
>
> VARCHAR
>
> CLOSEDON_C
>
> TIMESTAMP
>
> RECEIVEDAMOUNTRC_C
>
> DOUBLE
>
> CONFIRMSTATUS_C
>
> VARCHAR
>
> CHECKOUTBUYER_C
>
> VARCHAR
>
> CONTRACTTYPE_C
>
> VARCHAR
>
> EFORM_4028813A5119CDDA01511A77132B00D1EC3AFE21DC12C7EBB2AC1544DBE11B38SDG_C
>
> VARCHAR
>
> EFORM_4028813A4DDC6F61014DDDA4C7570467C79A654813F9FF1FBD6AC8528B12B31BFSD_C
>
> DOUBLE
>
>
> EFORM_4028813A4ED37ABC014ED392EE9B00904FD274B2907033A056895A1E329856FDUNDEFINED_C
>
> VARCHAR
>
>
> EFORM_4028813A4ED37ABC014ED38C8EFF0020CE58F86D580B32841D61552D8B0D0993UNDEFINED_C
>
> VARCHAR
>
> EFORM_4028813A4DDC6F61014DDDA4C757046700BAFA0E270DD182FC0FB819EF518E26IIY_C
>
> TIMESTAMP
>
> PHOENIX_ID
>
> INTEGER
>
> EFORM_4028813A513D7B2501513DA5712F06D9E7DA7A55F1F94058B2CB84E67BCE3EC7_C
>
> VARCHAR
>
> EFORM_4028813A513D7B2501513DA784E2072853394EEFF28F49FDBA7284024266ACCB_C
>
> VARCHAR
>
> EFORM_4028813A513D7B2501513D9EC3F906AD091BEC221E0A4261B8E6BD6BD10F4574FSD_C
>
> DOUBLE
>
>
> EFORM_4028813A513D7A9B01513D82BBCA04EFC6FF9FB5032B75850C505AFD9AEDCA33AAMIR_KHAN_C
>
> VARCHAR
>
> EFORM_4028813A513D7B2501513D9EC3F906ADCB1FA6AFCAF34E278571F89DDD49DAD4IIY_C
>
> TIMESTAMP
>
> TOTALITEMS_C
>
> INTEGER
>
>
>
>
>
> 2.       TABLE_6DF34229033E446CA448D76289376730_EPROC_COMPANY_C  (Primary
> Key : CODE_C)
>
>
>
> *COLUMN_NAME*
>
> *TYPE_NAME*
>
> TENANTID_C
>
> VARCHAR
>
> CURRENCY_C
>
> VARCHAR
>
> CODE_C
>
> VARCHAR
>
> LEGALNAME_C
>
> VARCHAR
>
> ERPID_C
>
> VARCHAR
>
> CREATEDBY_C
>
> VARCHAR
>
> CREATEDON_C
>
> TIMESTAMP
>
> NAME_C
>
> VARCHAR
>
> ARCHIVE_C
>
> BOOLEAN
>
> ACTIVE_C
>
> BOOLEAN
>
> LOCATIONCODE_C
>
> VARCHAR
>
> PHOENIX_ID
>
> INTEGER
>
>
>
>
>
> 3.       TABLE_6DF34229033E446CA448D76289376730_EPROC_BUSINESSUNIT_C
> (Primary Key : CODE_C)
>
>
>
> *COLUMN_NAME*
>
> *TYPE_NAME*
>
> TENANTID_C
>
> VARCHAR
>
> LOCATIONCODE_C
>
> VARCHAR
>
> CODE_C
>
> VARCHAR
>
> CREATEDBY_C
>
> VARCHAR
>
> NAME_C
>
> VARCHAR
>
> CREATEDON_C
>
> TIMESTAMP
>
> COMPANYCODE_C
>
> VARCHAR
>
> ARCHIVE_C
>
> BOOLEAN
>
> ERPID_C
>
> VARCHAR
>
> ACTIVE_C
>
> BOOLEAN
>
> PHOENIX_ID
>
> INTEGER
>
>
>
>
>
> 4.       TABLE_6DF34229033E446CA448D76289376730_EPROC_LOCATION_C
> (Primary Key : CODE_C)
>
>
>
> *COLUMN_NAME*
>
> *TYPE_NAME*
>
> TENANTID_C
>
> VARCHAR
>
> SHIPTOCODE_C
>
> VARCHAR
>
> BILLTOCODE_C
>
> VARCHAR
>
> REGIONCODE_C
>
> VARCHAR
>
> APCONTACT_C
>
> VARCHAR
>
> ERPID_C
>
> VARCHAR
>
> CODE_C
>
> VARCHAR
>
> CREATEDBY_C
>
> VARCHAR
>
> CREATEDON_C
>
> TIMESTAMP
>
> NAME_C
>
> VARCHAR
>
> ARCHIVE_C
>
> BOOLEAN
>
> ACTIVE_C
>
> BOOLEAN
>
> INVOICETOCODE_C
>
> VARCHAR
>
> PHOENIX_ID
>
> INTEGER
>
>
>
> 5.       TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDERITEM_C
> (Composite Key : { ENTITYID_C , LINEITEMID_C , PRIMARY_KEY_C })
>
>
>
> *COLUMN_NAME*
>
> *TYPE_NAME*
>
> OPENRATE_C
>
> DOUBLE
>
> INTERNALCOMMENT_C
>
> VARCHAR
>
> PRICE_C
>
> DOUBLE
>
> ALLOWEDTOTALPRICE_C
>
> DOUBLE
>
> ASSETCODETYPE_C
>
> VARCHAR
>
> IMAGEURL_C
>
> VARCHAR
>
> SUPPLIERPARTID_C
>
> VARCHAR
>
> RECEIPTTYPE_C
>
> VARCHAR
>
> SUPPLIERID_C
>
> VARCHAR
>
> SUPPLIERPRODUCTURL_C
>
> VARCHAR
>
> LINEITEMAMOUNT_C
>
> DOUBLE
>
> CHARGEDQUANTITY_C
>
> DOUBLE
>
> MANUFACTURERPRODUCTURL_C
>
> VARCHAR
>
> REQUESTER_C
>
> VARCHAR
>
> REJECTEDAMOUNT_C
>
> DOUBLE
>
> ISREPLACINGREJECTEDITEM_C
>
> BOOLEAN
>
> DELIVERYON_C
>
> TIMESTAMP
>
> MANUFACTURERPARTID_C
>
> VARCHAR
>
> DELIVERYUPTO_C
>
> TIMESTAMP
>
> ITEMTOTALPRICE_C
>
> DOUBLE
>
> ACCOUNTINGS_C
>
> VARCHAR
>
> CATEGORYCODE_C
>
> VARCHAR
>
> DURATIONDELIVERY_C
>
> DOUBLE
>
> LINENO_C
>
> VARCHAR
>
> ORDEREDQUANTITY_C
>
> DOUBLE
>
> DISCOUNTTYPE_C
>
> VARCHAR
>
> MAXUNITPRICE_C
>
> DOUBLE
>
> DESCRIPTION_C
>
> VARCHAR
>
> APPROVALSTATUS_C
>
> VARCHAR
>
> CONTRACTTYPE_C
>
> VARCHAR
>
> SUPPADDRESSSTR_C
>
> VARCHAR
>
> GREENITEM_C
>
> BOOLEAN
>
> CATEGORYNAME_C
>
> VARCHAR
>
> APPROVEDQUANTITY_C
>
> DOUBLE
>
> ITEMTAXES_C
>
> VARCHAR
>
> CONTRACTNO_C
>
> VARCHAR
>
> CONTRACTID_C
>
> VARCHAR
>
> QUANTITY_C
>
> DOUBLE
>
> APPLYNOTAXES_C
>
> BOOLEAN
>
> PARENTCATEGORYCODE_C
>
> VARCHAR
>
> APPROVEDRCVQUANTITY_C
>
> DOUBLE
>
> SUPPLIERNAME_C
>
> VARCHAR
>
> ADJUSTEDCREDITEDQUANTITY_C
>
> DOUBLE
>
> REFERENCEREQUISITIONID_C
>
> VARCHAR
>
> SUPPLIERADDRESSID_C
>
> VARCHAR
>
> DYNAMICINSTANCEID_C
>
> VARCHAR
>
> UNRECEIVEDAMOUNT_C
>
> DOUBLE
>
> ITEMTAXPRICE_C
>
> DOUBLE
>
> SUPPLIERCOMMENT_C
>
> VARCHAR
>
> RECEIVEDAMOUNT_C
>
> DOUBLE
>
> ORDEREDAMOUNT_C
>
> DOUBLE
>
> DELIVERTO_C
>
> VARCHAR
>
> OPENQUANTITY_C
>
> DOUBLE
>
> DYNAMICFORMID_C
>
> VARCHAR
>
> MANUFACTURERNAME_C
>
> VARCHAR
>
> APPROVEDAMOUNT_C
>
> DOUBLE
>
> ITEMTYPE_C
>
> VARCHAR
>
> SOURCETYPE_C
>
> VARCHAR
>
> MARKETPRICE_C
>
> DOUBLE
>
> ITEMID_C
>
> VARCHAR
>
> REFERENCEPURCHASEORDERID_C
>
> VARCHAR
>
> ORDERTIMEDAYS_C
>
> DOUBLE
>
> ORIGIN_C
>
> VARCHAR
>
> NONINVOICEDQUANTITY_C
>
> DOUBLE
>
> LINEITEMID_C
>
> VARCHAR
>
> SUPPLIERADDRESS_C
>
> VARCHAR
>
> UOM_C
>
> VARCHAR
>
> NAME_C
>
> VARCHAR
>
> CHARGEDLINEAMOUNT_C
>
> DOUBLE
>
> REJECTEDQUANTITY_C
>
> DOUBLE
>
> ORDERTIMEHRS_C
>
> DOUBLE
>
> UNRECEIVEDQUANTITY_C
>
> DOUBLE
>
> SCOPEID_C
>
> VARCHAR
>
> INVOICEDQUANTITY_C
>
> DOUBLE
>
> COSTINGS_C
>
> VARCHAR
>
> APPROVALREJECTQTY_C
>
> DOUBLE
>
> ATTACHMENTS_C
>
> VARCHAR
>
> DISCOUNTVALUE_C
>
> DOUBLE
>
> DELIVERTOTYPE_C
>
> INTEGER
>
> CHARGEDLINETAXAMOUNT_C
>
> DOUBLE
>
> PREFERREDITEM_C
>
> BOOLEAN
>
> NONINVOICEDAMOUNT_C
>
> DOUBLE
>
> SOURCINGSTATUS_C
>
> VARCHAR
>
> SPLITCOSTINGTYPE_C
>
> VARCHAR
>
> ASSETCODE_C
>
> VARCHAR
>
> REQUISITIONNO_C
>
> VARCHAR
>
> RECEIVEDQUANTITY_C
>
> DOUBLE
>
> SPENDTYPE_C
>
> VARCHAR
>
> CURRENCY_C
>
> VARCHAR
>
> ENTITYID_C
>
> VARCHAR
>
> INVOICEDAMOUNT_C
>
> DOUBLE
>
>
> EFORM_402881D64B56CFC3014B588187AF0508034958E73AB9854E43CB1A9F7E2563D6A200_C
>
> DOUBLE
>
>
> EFORM_402881D64C59ED63014C5AE39A4D48BDBF9939592EF4F49FA33CCF149F80C1F8RTETETT_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EDESCRIBE_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6ETAKE_1_C
>
> VARCHAR
>
>
> EFORM_4028813A4ED32EA9014ED37758CF012CB1C5494EE819B4BCBBA7F4D6626517A3UNDEFINED_C
>
> VARCHAR
>
> EFORM_4028813A4D6701B9014D6771C9C241A42D15F4E499283AE5C34B4A5C72C85A6BTY_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DDATE_OF_MANUF_C
>
> TIMESTAMP
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EUNDEFINED_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EQUANTITY_C
>
> VARCHAR
>
>
> EFORM_4028813A4FC04E55014FC1A2A3E000178DE25E869A414012B7DD2C3326CA6868TEST_C
>
> VARCHAR
>
>
> EFORM_402881D64B56CFC3014B588187AF0508034958E73AB9854E43CB1A9F7E2563D6UNDEFINED_C
>
> DOUBLE
>
>
> EFORM_4028813A4ED37ABC014ED38DA6400028508B79EFBC209872FBD9030991D2BB33UNDEFINED_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DISSUE_WITH_PRODUCT_C
>
> VARCHAR
>
>
> EFORM_402882624521E31D014521EEBDAE000A0B4C794DF3BF436DAA76BEF724D60B39UNDEFINED_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EDATE_OF_MANUF_C
>
> TIMESTAMP
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6ECOLOR_C
>
> VARCHAR
>
>
> EFORM_4028813A4ECF91E5014ECF995424000BABC7B22B4AE0AF1123199B98F1D35D64UNDEFINED_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EEXPIRY_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DTAKE_1_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DCOLOR_C
>
> VARCHAR
>
>
> EFORM_4028813A4FD1B066014FD1B28B7900013DE1D0B86A38478CA6E40FF29923516DTEST_C
>
> VARCHAR
>
>
> EFORM_402882624521E31D014521EE7D7800081DB15C93E0FAAE895FA3A26DE0F7EBE0UNDEFINED_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DDESCRIBE_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DHOW_MANY_C
>
> DOUBLE
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DEXPIRY_C
>
> VARCHAR
>
> EFORM_402884B54FE0DE51014FE0EA30B70004AED789945E58443F82C406A1BD43B08EADG_C
>
> VARCHAR
>
>
> EFORM_4028813A4FD1B066014FD1B28B7900013DE1D0B86A38478CA6E40FF29923516DHI_FIELD_UPLOAD_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EISSUE_WITH_PRODUCT_C
>
> VARCHAR
>
>
> EFORM_4028813A4ED37ABC014ED39351030094FEAC8AB3DD730B764781CDB9235BA175UNDEFINED_C
>
> VARCHAR
>
>
> EFORM_4028813A4D2151AC014D22B7091D0080858ACAE23FAF12B3E207D9914E14228FUNDEFINED_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EHOW_MANY_C
>
> DOUBLE
>
> PRIMARY_KEY_C
>
> VARCHAR
>
> PHOENIX_ID
>
> INTEGER
>
> EFORM_402882624521E31D014521EEBDAE000A0B4C794DF3BF436DAA76BEF724D60B39_C
>
> VARCHAR
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6E_C
>
> VARCHAR
>
> EFORM_402881D64B56CFC3014B588187AF0508034958E73AB9854E43CB1A9F7E2563D6_C
>
> DOUBLE
>
> EFORM_402882624521E31D014521EE7D7800081DB15C93E0FAAE895FA3A26DE0F7EBE0_C
>
> VARCHAR
>
> COSTINGS_JSON_C
>
> VARCHAR
>
> ATTACHMENTS_JSON_C
>
> VARCHAR
>
> ACCOUNTINGS_JSON_C
>
> VARCHAR
>
> ITEMTAXES_JSON_C
>
> VARCHAR
>
>
> EFORM_402881D64B56CFC3014B588187AF0508034958E73AB9854E43CB1A9F7E2563D6A200_JSON_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DDATE_OF_MANUF_JSON_C
>
> VARCHAR
>
>
> EFORM_402881D64B56CFC3014B588187AF0508034958E73AB9854E43CB1A9F7E2563D6_JSON_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EDATE_OF_MANUF_JSON_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EEXPIRY_JSON_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DHOW_MANY_JSON_C
>
> VARCHAR
>
>
> EFORM_4028813A4F25F818014F2621A6AD022309E07A3C5B7D4B76BE3F8C1491678C3DEXPIRY_JSON_C
>
> VARCHAR
>
>
> EFORM_402881D64C4A9FED014C4AB7A5EB0171064DD218D80B1086F9041C43BB4C4A6EHOW_MANY_JSON_C
>
> VARCHAR
>
>
>
> 6.       TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDERITEMCOSTING_C
> (Composite key : {ENTITYID_C, ITEMID_C, PRIMARY_KEY_C})
>
>
>
>
>
> *COLUMN_NAME*
>
> *TYPE_NAME*
>
> TENANTID_C
>
> VARCHAR
>
> COSTCENTERCODE_C
>
> VARCHAR
>
> CHARGEDISTRIBUTION_C
>
> VARCHAR
>
> PROJECTCODE_C
>
> VARCHAR
>
> BUDGETID_C
>
> VARCHAR
>
> BUSINESSUNITCODE_C
>
> VARCHAR
>
> SPLITVALUE_C
>
> DOUBLE
>
> ITEMID_C
>
> VARCHAR
>
> BUDGETLINEID_C
>
> VARCHAR
>
> ENTITYID_C
>
> VARCHAR
>
> VALUE_C
>
> DOUBLE
>
> PRIMARY_KEY_C
>
> VARCHAR
>
> PHOENIX_ID
>
> INTEGER
>
>
>
>
>
> 7.       TABLE_6DF34229033E446CA448D76289376730_EPROC_COSTCENTER_C
> (Primary Key : COSTCENTERCODE_C)
>
>
>
> *COLUMN_NAME*
>
> *TYPE_NAME*
>
> TENANTID_C
>
> VARCHAR
>
> COSTCENTERCODE_C
>
> VARCHAR
>
> DESCRIPTION_C
>
> VARCHAR
>
> CODE_C
>
> VARCHAR
>
> BUSINESSUNITCODES_C
>
> VARCHAR
>
> CREATEDBY_C
>
> VARCHAR
>
> CREATEDON_C
>
> TIMESTAMP
>
> ERPID_C
>
> VARCHAR
>
> NAME_C
>
> VARCHAR
>
> ARCHIVE_C
>
> BOOLEAN
>
> COMPANYCODE_C
>
> VARCHAR
>
> ACTIVE_C
>
> BOOLEAN
>
> COSTCENTEROWNERID_C
>
> VARCHAR
>
> PHOENIX_ID
>
> INTEGER
>
>
>
>
>
> Following is the query that is being fired eventually.
>
>
>
> *SELECT*
>
> *     DISTINCT *
>
> *     COALESCE( a1.name_C, 'N.A.')  ,*
>
> *     COALESCE( a2.name_C, 'N.A.')  ,*
>
> *     COALESCE( a3.name_C, 'N.A.')  ,*
>
> *     COALESCE( a4.purchaseOrderNumber_C, 'N.A.') ,*
>
> *     COALESCE( a4.releasedOn_C,TO_DATE('01/01/1970','MM/dd/yyyy')) ,*
>
> *     COALESCE( a4.name_C, 'N.A.') --,*
>
> *     COALESCE( a5.lineNo_C, 'N.A.') ,*
>
> *     COALESCE( a5.name_C, 'N.A.') ,*
>
> *     COALESCE(  a7.name_C,'N.A.') *
>
> *     FROM*
>
> *     (*
>
> *      TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDER_C  a4
> LEFT OUTER*
>
> *      JOIN TABLE_6DF34229033E446CA448D76289376730_EPROC_COMPANY_C  a1 ON
> a4.companyCode_C =  a1.code_C LEFT OUTER*
>
> *      JOIN TABLE_6DF34229033E446CA448D76289376730_EPROC_BUSINESSUNIT_C
> a2 ON  a4.businessUnitCode_C =  a2.code_C LEFT OUTER*
>
> *      JOIN TABLE_6DF34229033E446CA448D76289376730_EPROC_LOCATION_C  a3
> ON  a4.locationCode_C =  a3.code_C LEFT OUTER*
>
> *      JOIN
> TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDERITEM_C  a5 ON
> a5.entityId_C =  a4.purchaseOrderId_C LEFT OUTER*
>
> *      JOIN
> TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDERITEMCOSTING_C  a6
> ON  a6.itemId_C =  a5.lineItemId_C*
>
> *      AND
> TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDERITEMCOSTING_C.entityId_C
> = a5.entityId_C LEFT OUTER*
>
> *      JOIN TABLE_6DF34229033E446CA448D76289376730_EPROC_COSTCENTER_C   a7
> ON  a6.costCenterCode_C =   a7.code_C*
>
> *      )*
>
>
>
> When the query is executed, I get all the records in the first three
> columns(COALESCE( a1.name_C, 'N.A.'), COALESCE( a2.name_C, 'N.A.'),
> COALESCE( a3.name_C, 'N.A.')) as null even though data is present.
>
>
>
> The query works if I select only up to the fifth projection, for e.g. the
> below query returns me proper data in the first three columns:
>
>
>
> SELECT
>
>      DISTINCT
>
>      COALESCE( a1.name_C, 'N.A.')  ,
>
>      COALESCE( a2.name_C, 'N.A.')  ,
>
>      COALESCE( a3.name_C, 'N.A.')  ,
>
>      COALESCE( a4.purchaseOrderNumber_C, 'N.A.') ,
>
>      COALESCE( a4.releasedOn_C,TO_DATE('01/01/1970','MM/dd/yyyy')) ,
>
> -- Rest of the columns commented out
>
>      FROM
>
>      (
>
>       TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDER_C  a4
> LEFT OUTER
>
>       JOIN TABLE_6DF34229033E446CA448D76289376730_EPROC_COMPANY_C  a1 ON
> a4.companyCode_C =  a1.code_C LEFT OUTER
>
>       JOIN TABLE_6DF34229033E446CA448D76289376730_EPROC_BUSINESSUNIT_C  a2
> ON  a4.businessUnitCode_C =  a2.code_C LEFT OUTER
>
>       JOIN TABLE_6DF34229033E446CA448D76289376730_EPROC_LOCATION_C  a3 ON
> a4.locationCode_C =  a3.code_C LEFT OUTER
>
>       JOIN
> TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDERITEM_C  a5 ON
> a5.entityId_C =  a4.purchaseOrderId_C LEFT OUTER
>
>       JOIN
> TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDERITEMCOSTING_C  a6
> ON  a6.itemId_C =  a5.lineItemId_C
>
>       AND
> TABLE_6DF34229033E446CA448D76289376730_EPROC_PURCHASEORDERITEMCOSTING_C.entityId_C
> = a5.entityId_C LEFT OUTER
>
>       JOIN TABLE_6DF34229033E446CA448D76289376730_EPROC_COSTCENTER_C   a7
> ON  a6.costCenterCode_C =   a7.code_C
>
>       )
>
>
>
>
>
> But the moment I select an additional 6th projection, the records of the
> first three columns are returned as null.
>
>
>
> The issue is non-existent when I do a SELECT * (All the columns are
> returned with proper data).
>
>
>
> Can anyone help me on this issue??
>
>
>
>
>
> Regards,
>
> Vivek K T
>
>
>
>
>
>
>
>
>
>
>
>
> This email communication (including any attachments) contains confidential
> information and is intended only for the named recipients. If you are not
> the intended recipient, please delete this email communication (including
> any attachments) and hard copies immediately, Any unauthorized use or
> dissemination of this email communication (including any attachments) in
> any manner, is strictly prohibited. This email communication (including any
> attachments), may not be free of viruses, you should carry out your own
> virus checks before opening any attachment to this e-mail. The sender of
> this e-mail and the company shall not be liable for any damage that you may
> sustain as a result of viruses, incompleteness of this message,
> interception of this message, which may arise as a result of e-mail
> transmission.
>

Mime
View raw message