Thread: problem while converting sybase quries to postgres

problem while converting sybase quries to postgres

From
"imam"
Date:
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

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)

 

 

Re: problem while converting sybase quries to postgres

From
Richard Huxton
Date:
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


Re: problem while converting sybase quries to postgres

From
Richard Huxton
Date:
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