how to check on six fields if the same value exist

Question

how to check on six fields if the same value exist

midan1

SSCertifiable

Points: 5100
More actions
March 14, 2007 at 5:25 am

#116250
need halp
how to check on six fields if the same value exist
but not zero value
i have a " form" that the user can select "product number" from six list box
from the same table
like
1. Listbox_1 = fld1
2. Listbox_2 = fld2
3. Listbox_3 = fld3
4. Listbox_4 = fld4
5. Listbox_5 = fld5
6. Listbox_6 = fld6
--------------
how can i check after insert
on " fld1+fld2+fld3+fld4+fld5+fld6"
if the same value exist
but not zero
TNX

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Camilo Torres SSCrazy Points: 2034 More actions · Answer 1

Your inquire is very general. However, make sure to populate the list box with unique values. If populating it with a data set, make sure the SELECT in your query returns unique Product Numbers.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 2

Hi Midan1,

I think , it can solve ur problem.. it returns to u if all columns have same value including Nulls also..........

select * from Tab_exists

where isnull(col1,'')+isnull(col2,'')+isnull(col3,'')+isnull(col4,'')+

isnull(col5,'')+isnull(col6,'')=isnull(Replicate(coalesce(col1,col2,col3,col4,col5,col6),6),'')

Regards

Amit Gupta

/* Smart Thinking Not Always Right but Right Thinking May be Smarter */

midan1 SSCertifiable Points: 5100 More actions · Answer 3

sorry i dont explain ok

i dont wont a duplicated value to be exist !

so that the user canot select the same value from the six listbox

so the user can select only unique value

check it after the user insert into the table

tnx

midan1 SSCertifiable Points: 5100 More actions · Answer 4

midan1

SSCertifiable

Points: 5100

March 14, 2007 at 8:44 pm

#695133

hi

need halp

Vladan SSC-Insane Points: 22072 More actions · Answer 5

I'm not sure I understand correctly... Please tell me if this is it:

There are 6 listboxes, all of them contain the same values (the same list of products). After the user sets some values, you want to check that none of them is repeating - in other words, that user has selected different product in each of the boxes. You are not checking NULLs - any number of the listboxes can remain "unused", i.e. without value.

Is this what you were asking?

If yes, here we go... seems you have 6 columns for parameter values - but it should only be one column. You did not post any info about table names etc., so I wrote a pseudo-SQL, just to give you the idea. <listbox#> is the value of respective listbox, as it was displayed to user for entry.

/*this is table into which you will enter values of the listboxes*/

CREATE TABLE #param (paramID INT, value INT)

INSERT INTO #param (paramID, value)

SELECT 1, <listbox1>

UNION

SELECT 2, <listbox2>

UNION

SELECT 3, <listbox3>

UNION

SELECT 4, <listbox4>

UNION

SELECT 5, <listbox5>

UNION

SELECT 6, <listbox6>

IF EXISTS (SELECT value, count(*)

FROM #param

WHERE value IS NOT NULL /*or maybe WHERE value <> 0, or WHERE ISNULL(value,0)<>0 - depends on your data*/

GROUP BY value

HAVING count(*) > 1)

BEGIN

print 'Repeating value - check entry'

/*...and go back to where the user can modify values*/

END

ELSE

BEGIN

SELECT somecolumns

FROM sometable

JOIN #param ON #param.value = sometable.productID

END

PS: Actually, it should not be a problem if the user enters the same product several times... you wouldn't have to check anything at all if you build the select like this:

SELECT somecolumns

FROM sometable

JOIN (SELECT DISTINCT value FROM #param) dp ON dp.value = sometable.productID