Hi
Could you please look at whats wrong with following sql.
Thx in advance,
SELECT BTI.*, BTI_PREDECESSOR.objid AS predecessor_objid, BTI_PREDECESSOR.item_id AS predecessor_item_id, BTIT_PREDECESSOR.bt_item_type_key AS predecessor_type_key, CAT.catalog_key, S.objid AS state_objid, S.state_key, S.is_init_state, S.is_final_state, mlS.name AS state, BTIT.bt_item_type_key, BTP.bt_processor_key, mlBTP.name AS bt_processor_name , CU.objid AS cust_user_objid , CU.title AS cust_user_title , CU.firstname AS cust_user_firstname , CU.lastname AS cust_user_lastname , CU.salutation2pv AS cust_user_salutation2pv , PV_CU.name_option AS cust_user_salutation , A_CU.email AS cust_user_email , '' AS use_option_field, '' AS use_readerlist , BTI_QUOTATION.quotation_type2pv , BTI_QUOTATION.is_mandatory AS quotation_is_mandatory , BTI_QUOTATION.is_multiple AS quotation_is_multiple , BTI_QUOTATION.expiration_datetime AS quotation_expiration_datetime , BTI_QUOTATION.hint_internal AS quotation_hint_internal , BTI_QUOTATION.hint_external AS quotation_hint_external , BTI_QUOTATION.filter_value AS quotation_filter_value , BTI_QUOTATION.email_cc AS quotation_email_cc , BTI_QUOTATION.notification1_datetime AS notification1_datetime , BTI_QUOTATION.notification2_datetime AS notification2_datetime , BTI_RFQ.filter_value AS request_for_quotation_filter_value FROM tBusinessTransactionItem BTI LEFT OUTER JOIN tBusinessTransactionItem_Quotation BTI_QUOTATION ON BTI_QUOTATION.this2business_transaction_item = BTI.objid LEFT OUTER JOIN tBusinessTransactionItem_RequestForQuotation BTI_RFQ ON BTI_RFQ.this2business_transaction_item = BTI.objid LEFT OUTER JOIN tBusinessTransactionItem BTI_PREDECESSOR ON BTI_PREDECESSOR.objid = BTI.predecessor2bt_item, tBusinessTransactionItemType BTIT_PREDECESSOR , tBusinessTransactionItemType BTIT, tBusinessTransactionProcessor BTP, mltBusinessTransactionProcessor mlBTP, tLanguagePriority LP_BTP, tState S, mltState mlS, tLanguagePriority LP_S, tCatalog CAT , tBusinessTransactionItem2BusinessTransaction BTI2BT , tBusinessTransactionItem2SessionCart BTI2SC , tSessionCart SC , tCustUser CU_MASTER , tCustUser CU , tPopValue PV_CU , tAddress A_CU , tAddress2CustUser A2CU WHERE BTI.objid <> -1 AND BTI_PREDECESSOR.this2bt_item_type = BTIT_PREDECESSOR.objid AND BTI.this2bt_item_type = BTIT.objid AND BTI.this2bt_processor = BTP.objid AND mlBTP.this2master = BTP.objid AND mlBTP.this2language = LP_BTP.item2language AND LP_BTP.master2language = 0 AND LP_BTP.this2shop = 0 AND LP_BTP.priority = (SELECT MIN(LP_BTP2.priority) FROM tLanguagePriority LP_BTP2, mltBusinessTransactionProcessor mlBTP2 WHERE LP_BTP2.master2language = 0 AND LP_BTP2.this2shop = 0 AND LP_BTP2.item2language = mlBTP2.this2language AND mlBTP2.this2master = BTP.objid ) AND BTI.this2catalog = CAT.objid AND S.objid = BTI.bt_item2state AND mlS.this2master = S.objid AND mlS.this2language = LP_S.item2language AND LP_S.master2language = 0 AND LP_S.this2shop = 0 AND LP_S.priority = (SELECT MIN(LP_S2.priority) FROM tLanguagePriority LP_S2, mltState mlS2 WHERE LP_S2.master2language = 0 AND LP_S2.this2shop = 0 AND LP_S2.item2language = mlS2.this2language AND mlS2.this2master = S.objid ) AND BTI.objid = BTI2BT.this2business_transaction_item AND CU_MASTER.objid = 1101 AND CU.this2customer = CU_MASTER.this2customer AND SC.this2custuser = CU.objid AND BTI.objid = BTI2SC.this2business_transaction_item AND BTI.bt_item2state = 6664 AND BTI2SC.is_master_cart_item = 1 AND BTI2SC.this2session_cart = SC.objid AND EXISTS (SELECT NULL FROM tBusinessTransaction BT, tBusinessTransactionType BTT WHERE BT.objid = BTI2BT.this2business_transaction AND BTT.objid = BT.this2bt_type AND BTT.business_transaction_type_key = 'order:master_cart') AND PV_CU.objid = CU.salutation2pv AND A2CU.this2custuser = CU.objid AND A2CU.is_billing_default = 1 AND A2CU.this2address = A_CU.objid ORDER BY BTI.dbobj_create_datetime DESC
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi
Could you please look at whats wrong with following sql.
Thx in advance,
SELECT BTI.*, BTI_PREDECESSOR.objid AS predecessor_objid, BTI_PREDECESSOR.item_id AS predecessor_item_id, BTIT_PREDECESSOR.bt_item_type_key AS predecessor_type_key, CAT.catalog_key, S.objid AS state_objid, S.state_key, S.is_init_state, S.is_final_state, mlS.name AS state, BTIT.bt_item_type_key, BTP.bt_processor_key, mlBTP.name AS bt_processor_name , CU.objid AS cust_user_objid , CU.title AS cust_user_title , CU.firstname AS cust_user_firstname , CU.lastname AS cust_user_lastname , CU.salutation2pv AS cust_user_salutation2pv , PV_CU.name_option AS cust_user_salutation , A_CU.email AS cust_user_email , '' AS use_option_field, '' AS use_readerlist , BTI_QUOTATION.quotation_type2pv , BTI_QUOTATION.is_mandatory AS quotation_is_mandatory , BTI_QUOTATION.is_multiple AS quotation_is_multiple , BTI_QUOTATION.expiration_datetime AS quotation_expiration_datetime , BTI_QUOTATION.hint_internal AS quotation_hint_internal , BTI_QUOTATION.hint_external AS quotation_hint_external , BTI_QUOTATION.filter_value AS quotation_filter_value , BTI_QUOTATION.email_cc AS quotation_email_cc , BTI_QUOTATION.notification1_datetime AS notification1_datetime , BTI_QUOTATION.notification2_datetime AS notification2_datetime , BTI_RFQ.filter_value AS request_for_quotation_filter_value FROM tBusinessTransactionItem BTI LEFT OUTER JOIN tBusinessTransactionItem_Quotation BTI_QUOTATION ON BTI_QUOTATION.this2business_transaction_item = BTI.objid LEFT OUTER JOIN tBusinessTransactionItem_RequestForQuotation BTI_RFQ ON BTI_RFQ.this2business_transaction_item = BTI.objid LEFT OUTER JOIN tBusinessTransactionItem BTI_PREDECESSOR ON BTI_PREDECESSOR.objid = BTI.predecessor2bt_item, tBusinessTransactionItemType BTIT_PREDECESSOR , tBusinessTransactionItemType BTIT, tBusinessTransactionProcessor BTP, mltBusinessTransactionProcessor mlBTP, tLanguagePriority LP_BTP, tState S, mltState mlS, tLanguagePriority LP_S, tCatalog CAT , tBusinessTransactionItem2BusinessTransaction BTI2BT , tBusinessTransactionItem2SessionCart BTI2SC , tSessionCart SC , tCustUser CU_MASTER , tCustUser CU , tPopValue PV_CU , tAddress A_CU , tAddress2CustUser A2CU WHERE BTI.objid <> -1 AND BTI_PREDECESSOR.this2bt_item_type = BTIT_PREDECESSOR.objid AND BTI.this2bt_item_type = BTIT.objid AND BTI.this2bt_processor = BTP.objid AND mlBTP.this2master = BTP.objid AND mlBTP.this2language = LP_BTP.item2language AND LP_BTP.master2language = 0 AND LP_BTP.this2shop = 0 AND LP_BTP.priority = (SELECT MIN(LP_BTP2.priority) FROM tLanguagePriority LP_BTP2, mltBusinessTransactionProcessor mlBTP2 WHERE LP_BTP2.master2language = 0 AND LP_BTP2.this2shop = 0 AND LP_BTP2.item2language = mlBTP2.this2language AND mlBTP2.this2master = BTP.objid ) AND BTI.this2catalog = CAT.objid AND S.objid = BTI.bt_item2state AND mlS.this2master = S.objid AND mlS.this2language = LP_S.item2language AND LP_S.master2language = 0 AND LP_S.this2shop = 0 AND LP_S.priority = (SELECT MIN(LP_S2.priority) FROM tLanguagePriority LP_S2, mltState mlS2 WHERE LP_S2.master2language = 0 AND LP_S2.this2shop = 0 AND LP_S2.item2language = mlS2.this2language AND mlS2.this2master = S.objid ) AND BTI.objid = BTI2BT.this2business_transaction_item AND CU_MASTER.objid = 1101 AND CU.this2customer = CU_MASTER.this2customer AND SC.this2custuser = CU.objid AND BTI.objid = BTI2SC.this2business_transaction_item AND BTI.bt_item2state = 6664 AND BTI2SC.is_master_cart_item = 1 AND BTI2SC.this2session_cart = SC.objid AND EXISTS (SELECT NULL FROM tBusinessTransaction BT, tBusinessTransactionType BTT WHERE BT.objid = BTI2BT.this2business_transaction AND BTT.objid = BT.this2bt_type AND BTT.business_transaction_type_key = 'order:master_cart') AND PV_CU.objid = CU.salutation2pv AND A2CU.this2custuser = CU.objid AND A2CU.is_billing_default = 1 AND A2CU.this2address = A_CU.objid ORDER BY BTI.dbobj_create_datetime DESC
sorry for double post.
I don't understand this:
LEFT OUTER JOIN
tBusinessTransactionItem BTI_PREDECESSOR ON BTI_PREDECESSOR.objid
= BTI.predecessor2bt_item, tBusinessTransactionItemType BTIT_PREDECESSOR
I've always used
"LEFT OUTER JOIN tab1 on tab1.col1 = tab2.col2"
but this is like:
"LEFT OUTER JOIN tab1 on tab1.col1 = tab2.col2, col3"
which I don't understand (and I guess this is the part jsqparser doesn't understand either).
ups sorry I guess tBusinessTransactionItemType is a table, not a column... I will look into that.
Please have a look at 0.5.0
This release will break some working code, since there is only 1 "FromItem" per PlainSelect. In a select like:
select * from tab1, tab2, tab3
FromItem would be tab1, all the others are joins.
Hi Leonardo,
Thank you for your reply.
I was actually deliberately looking for this fix.
Now everything works fine.
But now I am getting another issue. I will post in a new post to make things cleaner.
Greetings,