Check Missing data when id is not null or blank

Question

I am trying to check if data is present for ID but not for its corresponding value then entire row discard from output. If both value and id are blank then its ok, and those row retain in the output

DECLARE @TAB TABLE
(
ID VARCHAR (50),
SKU VARCHAR (50),
Resistor_ID1 VARCHAR (50),
Resistor_Value VARCHAR (50),
Capacitor_ID VARCHAR (50),
Capacitor_Value VARCHAR (50),
Inductor_ID  VARCHAR (50),
Inductor_Value VARCHAR (50)
)

INSERT @TAB

SELECT '1', 'BPN1256', '1033', ''     , 'RMA56', 'Ceramic', 'PVAN59', 'Ferrite' UNION ALL
SELECT '1', 'SAN9286', ''    , ''     , 'TMA56', 'FILM'   , ''      , ''        UNION ALL
SELECT '1', 'RJA1896', '3033', '35OHM', 'UMA56', 'Ceramic', 'PVAN59', 'Ferrite' UNION ALL
SELECT '1', 'DNN5256', '4033', '45OHM', 'QMA56', ''       , 'PVAN59', 'Ferrite' UNION ALL
SELECT '1', 'LXA6556', '5033', '65OHM', 'ZMA56', 'FILM'   , 'PVAN59', ''

Expected Output

1  SAN9286               TMA56  FILM  
1  RJA1896  3033  35OHM  UMA56  Ceramic  PVAN59  Ferrite

Please share your expertise. Thanks

Adjust you example, do you need that many columns? Describe better why just those rrows are returned. — jarlh, Jan 27 '16 at 08:30
@jarlh: These Rows are returned beacause either ID and its value are filled or both are blank i am using sql2008 — Rajee Kasthuri, Jan 27 '16 at 08:40

score 2 · Answer 1 · answered Jan 27 '16 at 08:52

  DECLARE @TAB TABLE
    (
    ID VARCHAR (50),
    SKU VARCHAR (50),
    Resistor_ID1 VARCHAR (50),
    Resistor_Value VARCHAR (50),
    Capacitor_ID VARCHAR (50),
    Capacitor_Value VARCHAR (50),
    Inductor_ID  VARCHAR (50),
    Inductor_Value VARCHAR (50)
    )

    INSERT @TAB

    SELECT '1', 'BPN1256',  '1033', '','RMA56', 'Ceramic',  'PVAN59',   'Ferrite' UNION ALL
    SELECT '1', 'SAN9286',  '', '', 'TMA56',    'FILM',     '',           '' UNION ALL
    SELECT '1', 'RJA1896',  '3033', '35OHM',    'UMA56',    'Ceramic',  'PVAN59',   'Ferrite' UNION ALL
    SELECT '1', 'DNN5256',  '4033', '45OHM',    'QMA56',    '',         'PVAN59',   'Ferrite' UNION ALL
    SELECT '1', 'LXA6556',  '5033', '65OHM',    'ZMA56',    'FILM', 'PVAN59',   ''

    SELECT * FROM @TAB t WHERE ((t.Resistor_ID1<>'' AND t.Resistor_Value<>'') OR (t.Resistor_ID1='' AND t.Resistor_Value='')) 
    AND ((t.Capacitor_ID<>'' AND t.Capacitor_Value<>'') OR (t.Capacitor_ID='' AND t.Capacitor_Value='')) 
    AND ((t.Inductor_ID<>'' AND t.Inductor_Value<>'') OR (t.Inductor_ID='' AND t.Inductor_Value=''))

But you should take care on using empty string or null. There are significant differences. In your example you have used empty string to show absence of data and the select above is applicable for empty string.

score 1 · Accepted Answer · edited May 23 '17 at 11:59

Well, why not just do it directly? You need rows when both ID and value are blank, or when both ID and value are not blank. Put this into where clause, repeat for 3 id-value pairs, and you're done.

SELECT * FROM @TAB
WHERE (Resistor_ID1 != '' and Resistor_Value != '' or Resistor_ID1 = '' and Resistor_Value = '')
and (Capacitor_ID != '' and Capacitor_Value != '' or Capacitor_ID = '' and Capacitor_Value = '')
and (Inductor_ID != '' and Inductor_Value != '' or Inductor_ID = '' and Inductor_Value = '')

Important note: you didn't specify your DBMS, so you might need to alter that query syntax. For example, in Oracle a blank string('') is treated as null value and should be checked with value is null instead of value = ''. In Mysql, null and empty string are different things, and should be checked differently.

UPD: it should work fine using = and != for MS SQL server, if you actually inserted blank strings and not nulls. If you're not sure, you might want to take a look at How do I check if a Sql server string is null or empty

@jarlh: id value is identity column, I am sorry, typo error – Rajee Kasthuri Jan 27 '16 at 08:55 — Rajee Kasthuri, Jan 27 '16 at 08:55

Check Missing data when id is not null or blank

2 Answers2