After add more fields to select statement all records Increased why and how to prevent that?

1

I work on SQL server 2012 I face issue more records increased after add more columns to select statment as

TextUnit and MaxValue why I don't know that

I need it to display as two records .

Before add textUnit and MaxValue it display two records

After add textUnit and MaxValue it display three records .

SELECT DISTINCT
  [InputID],PART_ID,[Vendor ID],Manufacturer,[Digi-Key Part No.] ,[Mfr Part No.],[Description],Category,Family ,Obsolete ,[Non-Stock] 
  ,	[Part_Status],partNumber,CompanyName,DKFeatureName,[Variant Number],IsUnit,
  SUBSTRING((
   
	 SELECT  isnull(StarFormat,'') + Value+ isnull(endFormat,'')
    FROM #FinalTable 
    WHERE ([InputID] = Results.[InputID] and DkFeatureId = Results.DkFeatureId) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,5000) AS Value,MaxValue,TextUnit
FROM #FinalTable Results
Where StatusId=3

before add textUnit and MaxValue it display as below :slight_smile:

InputID PART_ID Vendor ID Manufacturer Digi-Key Part No. Mfr Part No. Description Category Family Obsolete Non-Stock Part_Status partNumber CompanyName DKFeatureName Variant Number IsUnit Value
1 12178894 2326 RDI Inc 2326-40A5758BIP1J11M-ND 40A5758BIP1J11M SPEAKER Audio Products Speakers Active Stocking Exact 40A5758BIP1J11M RDI, Inc. Frequency Range V-Article_1582340895328689 0 0Hz ~ 4.5kHz
1 12178894 2326 RDI Inc 2326-40A5758BIP1J11M-ND 40A5758BIP1J11M SPEAKER Audio Products Speakers Active Stocking Exact 40A5758BIP1J11M RDI, Inc. Operating Temperature V-Article_1582340895328689 0 -25°C ~ 60°C

after add textUnit and MaxValue it display as below :

it increased one row after add two columns TextUnit and MaxValue why and how to keep them two rows only

InputID PART_ID Vendor ID Manufacturer Digi-Key Part No. Mfr Part No. Description Category Family Obsolete Non-Stock Part_Status partNumber CompanyName DKFeatureName Variant Number IsUnit Value MaxValue TextUnit
1 12178894 2326 RDI Inc 2326-40A5758BIP1J11M-ND 40A5758BIP1J11M SPEAKER Audio Products Speakers Active Stocking Exact 40A5758BIP1J11M RDI, Inc. Frequency Range V-Article_1582340895328689 0 0Hz ~ 4.5kHz NULL Hz
1 12178894 2326 RDI Inc 2326-40A5758BIP1J11M-ND 40A5758BIP1J11M SPEAKER Audio Products Speakers Active Stocking Exact 40A5758BIP1J11M RDI, Inc. Frequency Range V-Article_1582340895328689 0 0Hz ~ 4.5kHz NULL kHz
1 12178894 2326 RDI Inc 2326-40A5758BIP1J11M-ND 40A5758BIP1J11M SPEAKER Audio Products Speakers Active Stocking Exact 40A5758BIP1J11M RDI, Inc. Operating Temperature V-Article_1582340895328689 0 -25°C ~ 60°C NULL NULL
2

Because you have inconsistent data.
Why is there a difference between first and second row TexUnit value? Both have Vakue column of 4.5kHz but TextUnit for 1st one is Hz but second one is Khz. Where and How is TextUnit being populated?

And your table design is worrisome.
There seems to be a lot of redundancy
DKFeatureName is Frequency Range whereas this can be derived from the Value column. And Value column can never be used for any range calculations it probably should be broken down into 2 float columns of FromRange and ToRange snd a separate column for UnitText instead of parsing the Value column through some function to separate out the range numbers and the measurement unit

3

hi ahmedbarbary

One possible reason could be !!!

image
image1169×632 16.3 KB