phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vivek KT <vivek...@zycus.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 Wed, 02 Dec 2015 15:38:30 GMT
Hi Maryann,

I’ve filed a JIRA for the same. Below is the link.

https://issues.apache.org/jira/browse/PHOENIX-2480

Regards,
Vivek K T

From: Maryann Xue [mailto:maryann.xue@gmail.com]
Sent: 01 December 2015 21:03
To: user@phoenix.apache.org
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

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<mailto: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.

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