converting T-sql to postgresql

Question

i have a T-sql code that i run on SQL server successfully.

SELECT
(SELECT
    Address
FROM
    customer_address
WHERE
    ProspectID = tm.ProspectID
    AND TYPE = 'RESIDENCE') AS Residence_Address
 FROM
    landing.kp_los_trx_master tm
INNER JOIN landing.kp_los_trx_status ts ON
    tm.ProspectID = ts.ProspectID
LEFT JOIN landing.kp_los_confins_branch cb ON
    tm.BranchID = cb.BranchID
LEFT JOIN landing.kp_los_customer_personal cp ON
    tm.ProspectID = cp.ProspectID
LEFT JOIN landing.kp_los_trx_items ti ON
    tm.ProspectID = ti.ProspectID
LEFT JOIN landing.kp_los_trx_apk ta ON
    tm.ProspectID = ta.ProspectID
LEFT JOIN landing.kp_los_customer_employment cem ON
    tm.ProspectID = cem.ProspectID
LEFT JOIN landing.kp_los_customer_emcon ce ON
    tm.ProspectID = ce.ProspectID

But, when i want to convert its code to postgresql with the code i created shown below;

SELECT
(SELECT
        a.address 
    FROM
        landing.kp_los_customer_address a 
        inner join landing.kp_los_trx_master b on
        a.ProspectID = b.ProspectID
        where a."Type" = 'RESIDENCE') AS Residence_Address
FROM
    landing.kp_los_trx_master tm
INNER JOIN landing.kp_los_trx_status ts ON
    tm.ProspectID = ts.ProspectID
LEFT JOIN landing.kp_los_confins_branch cb ON
    tm.BranchID = cb.BranchID
LEFT JOIN landing.kp_los_customer_personal cp ON
    tm.ProspectID = cp.ProspectID
LEFT JOIN landing.kp_los_trx_items ti ON
    tm.ProspectID = ti.ProspectID
LEFT JOIN landing.kp_los_trx_apk ta ON
    tm.ProspectID = ta.ProspectID
LEFT JOIN landing.kp_los_customer_employment cem ON
    tm.ProspectID = cem.ProspectID
LEFT JOIN landing.kp_los_customer_emcon ce ON
    tm.ProspectID = ce.ProspectID

the result is getting error that:

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

i dont know how to solve of its code, anyone have any idea to help me? thank in advance

You've removed the correlated join in your sub query. Also why all those LEFT JOINs when the only object you define in the FROM (in the T-SQL) that you use in the query is kp_los_trx_master. In the PostgreSQL you don't reference a single one of the objects in the FROM. — Thom A
– Thom A ♦, Commented Aug 5, 2021 at 10:54

Gordon Linoff · Accepted Answer · 2021-08-05 10:49:08Z

1

Your original code should run in Postgres -- or in fact in any database.

However, when using correlated subqueries you should always qualify all column references to be sure they are referring to exactly what you intend:

SELECT (SELECT ca.Address
        FROM customer_address ca
        WHERE ca.ProspectID = tm.ProspectID AND
              ca.TYPE = 'RESIDENCE'
       ) AS Residence_Address

Qualifying column references is generally a good idea, but even more strongly recommended with subqueries.

Note: I would also suggest that you avoid double quotes around column and table names, so you can avoid double quotes in queries.

answered Aug 5, 2021 at 10:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting T-sql to postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related