Обсуждение: problem while converting sybase quries to postgres
SELECT t1.SR_TRAN_HEAD_PK AS ReceiptHeaderPK,
t1.ADJ_TRAN_ID AS AdjustmentTransactionID,
t1.TOTAL_QTY_INVOICED AS TotalQtyInvoiced,
t1.TOTAL_QTY_MATCHED AS TotalQtyMatched,
t1.VENDOR_ID AS VendorID,
t1.COMMENTS AS Comments,
t1.SR_NUMBER AS ShipmentReceiptNumber,
t1.SHIPMENT_RECEIPT_DATE AS ReceiptDate,
t1.POSTING_DATE AS PostingDate,
t2.PO_HEADER_PK AS POHeaderPK,
t1.VENDOR_DOCUMENT_NUMBER AS VendorDocumentNumber,
t1.TRAN_STATUS_ID AS TransactionStatusID,
t1.SR_TOTAL AS ReceiptTotal,
t1.SR_BATCH_NUMBER AS ReceiptBatchNumber,
t1.VENDOR_PK AS VendorPK,
t1.TOTAL_QTY_RECEIVED AS TotalQtyReceived,
t1.TOTAL_QTY_REJECTED AS TotalQtyRejected,
t2.PO_NUMBER AS PONumber,
t2.BUYER_PK AS BuyerPK,
t2.BILLING_TERMS_PK AS BillingTermsPK,
t2.PO_GROUP_PK AS POGroupPK,
t2.PURCHASE_ORDER_TYPE_PK AS POTypePK,
t2.PO_DELIVERY_METHOD_PK AS PODeliveryMethodPK,
t2.CUSTOMER_PK AS CustomerPK,
t2.PO_HEADER_STATUS_PK AS POHeaderStatusPK,
t2.BUYER_ID AS BuyerID,
t2.PO_GROUP AS POGroup,
t2.PO_TYPE AS POType,
t2.PO_DATE AS PODate,
t2.PO_HEADER_STATUS AS POStatus,
t2.IS_PO_ON_HOLD AS POonHold,
t2.TRADE_DISCOUNT AS TradeDiscount,
t2.SHIPPING_CHARGES AS ShippingCharges,
t2.OTHER_CHARGES AS OtherCharges,
t2.PO_TAX AS POTax,
t2.PO_SUB_TOTAL AS POSubTotal,
t2.PO_DISCOUNT AS PODiscount,
t2.PO_TOTAL AS POTotal,
t2.PO_TOTAL_QTY AS TotalQty,
t2.RELEASED_DATE AS ReleasedDate,
t3.AMT_APPORTIONED AS AmtApportioned,
t3.MODIFIED_RECEIVED_QTY AS ModifiedReceivedQty,
t3.QTY_INVOICED AS QtyInvoiced,
t3.QTY_MATCHED AS QtyMatched,
t3.STATUS_ID AS StatusID,
t3.INV_COST AS InvoiceCost,
t3.AMT_INVOICE_APPORTIONED AS AmtInvoiceApportioned,
t3.SR_TRAN_DETAIL_PK AS ReceiptDetailPK,
t3.WAREHOUSE_PK AS WarehousePK,
t3.UNIT_COST AS UnitCost,
t3.QTY_RECEIVED AS QtyReceived,
t3.EXT_COST AS ExtendedCost,
t3.QTY_REJECTED AS QtyRejected,
t3.REASON_FOR_REJECTION AS ReasonforRejection,
t3.BILL_OF_LADING AS BillofLading,
t3.ITEM_ID AS ItemID,
t3.WAREHOUSE_ID AS WarehouseID,
t3.ITEM_PK AS ItemPK,
t3.UOM_PK AS UomPK,
t3.UOM_ID AS UomID,
t3.QTY_FOR_BASE_UOM AS BaseUOM,
t3.UNIT_COST_FOR_BASE_UOM AS UnitCostforBaseUOM,
t3.ITEM_NAME AS ItemName,
t3.ITEM_ALTERNATE_NAME AS ItemAlternateName,
t3.PO_DETAIL_PK AS PODetailPK
FROM PE_POP_SR_POSTED_TRAN_HEAD t1,
PE_POP_PO_HEADER t2,
PE_POP_SR_POSTED_TRAN_DET t3
Where t1.SR_TRAN_HEAD_PK = t3.SR_TRAN_HEAD_PK
and (t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *= t2.PO_HEADER_PK)
imam wrote: > The following is a sybase query and i am trying to convet it into > postgres but in postgres the join will be specified in from clause > and we cannot specify any logical operator > > i would be grateful if you could tell me the solution for it You don't actually say what your query is trying to do. > SELECT t1.SR_TRAN_HEAD_PK AS ReceiptHeaderPK, > t1.ADJ_TRAN_ID AS AdjustmentTransactionID, ...other columns... > FROM PE_POP_SR_POSTED_TRAN_HEAD t1, > PE_POP_PO_HEADER t2, > PE_POP_SR_POSTED_TRAN_DET t3 > Where t1.SR_TRAN_HEAD_PK = t3.SR_TRAN_HEAD_PK > and (t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *= > t2.PO_HEADER_PK) If this is a left outer join, you could do something like: SELECT ... FROM pe_pop_sr_posted_tran_head t1 LEFT JOIN pe_pop_po_header t2 ON t1.po_header_pk = t2.po_header_pk LEFT JOIN pe_pop_sr_posted_tran_det t3 ON t2.po_header_pk = t3.po_header_pk AND t1.sr_tran_head_pk = t3.sr_tran_head_pk But you'll want to test it because I'm not clear what your query is doing. -- Richard Huxton Archonet Ltd
Please cc: the list when you reply to me - that way others can help too. imam wrote: >>If this is a left outer join, you could do something like: >>SELECT >> ... >>FROM >> pe_pop_sr_posted_tran_head t1 >>LEFT JOIN >> pe_pop_po_header t2 >>ON >> t1.po_header_pk = t2.po_header_pk >>LEFT JOIN >> pe_pop_sr_posted_tran_det t3 >>ON >> t2.po_header_pk = t3.po_header_pk >> AND t1.sr_tran_head_pk = t3.sr_tran_head_pk >> >>But you'll want to test it because I'm not clear what your query is doing. >>-- >> Richard Huxton >> Archonet Ltd > > > Thanking for you reply the problem is that i have a "or" condition and left > outer join between two table.A po_header_table is comman in both the join as > given below > t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *= >>>t2.PO_HEADER_PK) Ah! Hadn't spotted that. Would this be what you're after? SELECT ... FROM ( SELECT t1.po_header_pk AS t1_phpk t3.po_header_pk AS t3_phpk ... FROM t1, t3 WHERE t1.sr_tran_head_pk = t3.sr_tran_head_pk ) AS first LEFT JOIN t2 ON ( first.t1_phpk = t2.po_header_pk OR first.t3_phpk = t2.po_header_pk ) Not sure what happens if t1_phpk and t3_phpk both match different rows in t2 or whether that's what you want. -- Richard Huxton Archonet Ltd