Capturing the first vendor code (not ALL) associated with active user

[gautammalkani]

Hi All

I have several tables that helps me try to capture the unique number of vendors that are active from the users table as shown below in the SQL script. However, ther is an exception where users may be associated with multiple vendors. I feel that my script captures all associated vendor codes associated with my users. Is there a way of capturing only the FIRST (not ALL) vendor code associated with the user. Thanks for your help.

Gautam

SQL SCRIPT:

select count(distinct(d.vendor_id))
from SSPE.PG_FORM_SUBMIT a, SSPE.SP_USER_AUX b, sspe.pg_supplier_vendor d
where a.SOURCENAME = 'wvInventoryForecast' and
a.PROFILEID = b.ID and
b.SUPPLIER_ID = d.supplier_id and
a.CLOCKTIME between to_date('15-Mar-06','DD-MON-YY') and to_date('04-Apr-06','DD-MON-YY')

 

[rac2]

Instead of query the table pg_supplier_vendor, query a subquery which has only the first vendor_id for each user.

The subquery would be something like this

SELECT b.ID, MIN(d.vendor_id) AS FirstVendor

FROM SSPE.PG_FORM_SUBMIT a, SSPE.SP_USER_AUX b, sspe.pg_supplier_vendor d

WHERE a.PROFILEID = b.ID
  AND b.SUPPLIER_ID = d.supplier_id
GROUP BY b.id

The idea is to create a view which has one vendor_id per person and to use that in place of the table which has many vendor_id per person. Either create a VIEW with this query; or put it inside parentheses as a subquery and use it in place of the table in the FROM clause.

SELECT a.col1, a.col5, b.col2
FROM TableA a , (SELECT blah AS ID, blah AS col2 more blah) b
WHERE a.id = b.ID

Meanwhile, may I suggest that you use the JOIN style to write queries joining several tables. One advantage of this, is that it removes the join conditions from the WHERE clause. For example,

SELECT count(distinct(d.vendor_id))

FROM SSPE.PG_FORM_SUBMIT a
JOIN SSPE.SP_USER_AUX b ON a.PROFILEID = b.ID
JOIN sspe.pg_supplier_vendor d ON b.SUPPLIER_ID = d.supplier_id

WHERE a.SOURCENAME = 'wvInventoryForecast'
  AND a.CLOCKTIME BETWEEN to_date('15-Mar-06','DD-MON-YY') AND to_date('04-Apr-06','DD-MON-YY')

With this style, the conditions in the WHERE clause concern only the search. So it is easier to read and understand.

Well, while I am at it, here is more unwanted advice. What we are writing here are single statements in SQL, not scripts. A script would be several statemenst.