CODEWITHSUNDEEP
sqlpostgresqlt-sql
Handri Mauludin Maulana
Handri Mauludin Maulana

Reputation: 21

converting T-sql to postgresql

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

Upvotes: 0

Views: 547

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

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.

Upvotes: 1

Related Questions