Need help with SQL

[TStriker]

I have a rather simple query:

SELECT ven.area_cd, ven.phone_no, ven.updt_dt_time
FROM vend_commcn ven
WHERE ( (ven.commcn_type_cd = 'VOICE')
AND (ven.vend_no = (SELECT vend_no
FROM purch_ord
WHERE user_ord_no = '78092'))
)

It returns more that one row sometimes. I would like it to return only one row based on the latest ven.updt_dt_time


Any help would be appreciated.

-Striker

 

[TStriker]

Nevermind, this seems to work:

SELECT
*
FROM
(
SELECT AREA_CD, PHONE_NO
FROM VEND_COMMCN
WHERE
COMMCN_TYPE_CD = 'VOICE' and
VEND_NO = (SELECT VEND_NO FROM PURCH_ORD WHERE USER_ORD_NO = '78092') ORDER BY updt_dt_time DESC
)
WHERE ROWNUM = 1;


-Striker

 

[Thargy]

Er, I think you actually have a rather serious problem there.

When you stipulate rownum=1 you're artificially limiting the results to 1 row, thereby masking the underlying problem of the query returning more rows than it ought.

this is often caused by dud data or unexpected join conditions. What you appear to have done is found what you want (maybe) plus some dross, and then by saying just give me 1 row, be claiming correct behaviour.

All I can do is wish you good luck.

Regards

T

 

[TStriker]

The query is intended to place a vendor's main phone number onto a purchase order.

The vendor can have many numbers; VOICE, FAX, MOBILE, etcetera and each vendor can have more than one of each type. This means that the query will return zero or more VOICE numbers. I only have room for one number on the PO form so we have decided that the most recently created VOICE number is the most likely one to be accurate.

You are correct that there is a design flaw here. A significant design flaw. There should be a better way of designating which one is the main number but there isn't.

This system wasn't designed by me, it is a shrink-wrapped business application bought off-the-shelf. I have no input on the design nor can anything be done about it. To use a tired old phrase; It-is-what-it-is.

Everyone involved understands that this is far from perfect but we have done our best with what we have.

-Striker

 

[Thargy]

Ah, the good ol' "vendor knows best" problem.

Commiserations.

Regards

T