Help with adding in case of NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with adding in case of NULL

Author

Topic

Johnyalm
Starting Member

49 Posts

Posted - 2009-10-24 : 04:01:21

Hi,

I have problems with this pqrt of a selection where I add the results from several selections, and sometimes I get NULL as a result, and then the sumup is NULL as well.

Any ideas how to fix this?

( SELECT 1 WHERE ( SELECT tblDIMRESULTS.scaledscore FROM tblDIMRESULTS INNER JOIN tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo INNER JOIN tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions WHERE id_tests = (SELECT TOP 1 id_tests FROM tblTESTS WHERE (id_actors = tblACTORS.id_actors) AND (testname = 'bigfive249') ORDER BY regdatetime DESC) AND tblDIMENSIONS.dimension = 'Sårbarhet') - (SELECT tblPROFILES_NORMGROUPS_VALUES.score FROM tblPROFILES_NORMGROUPS_VALUES INNER JOIN tblPROFILES_NORMGROUPS ON tblPROFILES_NORMGROUPS_VALUES.id_profiles_normgroups = tblPROFILES_NORMGROUPS.id_profiles_normgroups WHERE (tblPROFILES_NORMGROUPS_VALUES.dimension = 'Sårbarhet') AND (tblPROFILES_NORMGROUPS.id_profiles_normgroups = 5))< 0 )
+ ( SELECT 1 WHERE ( SELECT tblDIMRESULTS.scaledscore FROM tblDIMRESULTS INNER JOIN tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo INNER JOIN tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions WHERE id_tests = (SELECT TOP 1 id_tests FROM tblTESTS WHERE (id_actors = tblACTORS.id_actors) AND (testname = 'bigfive249') ORDER BY regdatetime DESC) AND tblDIMENSIONS.dimension = 'Behov av spänning') - (SELECT tblPROFILES_NORMGROUPS_VALUES.score FROM tblPROFILES_NORMGROUPS_VALUES INNER JOIN tblPROFILES_NORMGROUPS ON tblPROFILES_NORMGROUPS_VALUES.id_profiles_normgroups = tblPROFILES_NORMGROUPS.id_profiles_normgroups WHERE (tblPROFILES_NORMGROUPS_VALUES.dimension = 'Behov av spänning') AND (tblPROFILES_NORMGROUPS.id_profiles_normgroups = 5))< 0 )
+ ( SELECT 1 WHERE ( SELECT tblDIMRESULTS.scaledscore FROM tblDIMRESULTS INNER JOIN tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo INNER JOIN tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions WHERE id_tests = (SELECT TOP 1 id_tests FROM tblTESTS WHERE (id_actors = tblACTORS.id_actors) AND (testname = 'bigfive249') ORDER BY regdatetime DESC) AND tblDIMENSIONS.dimension = 'Självsäkerhet') - (SELECT tblPROFILES_NORMGROUPS_VALUES.score FROM tblPROFILES_NORMGROUPS_VALUES INNER JOIN tblPROFILES_NORMGROUPS ON tblPROFILES_NORMGROUPS_VALUES.id_profiles_normgroups = tblPROFILES_NORMGROUPS.id_profiles_normgroups WHERE (tblPROFILES_NORMGROUPS_VALUES.dimension = 'Självsäkerhet') AND (tblPROFILES_NORMGROUPS.id_profiles_normgroups = 5))< 0 )
+ ( SELECT 1 WHERE ( SELECT tblDIMRESULTS.scaledscore FROM tblDIMRESULTS INNER JOIN tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo INNER JOIN tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions WHERE id_tests = (SELECT TOP 1 id_tests FROM tblTESTS WHERE (id_actors = tblACTORS.id_actors) AND (testname = 'bigfive249') ORDER BY regdatetime DESC) AND tblDIMENSIONS.dimension = 'EXTROVERSION/UTÅTVÄNDHET') - (SELECT tblPROFILES_NORMGROUPS_VALUES.score FROM tblPROFILES_NORMGROUPS_VALUES INNER JOIN tblPROFILES_NORMGROUPS ON tblPROFILES_NORMGROUPS_VALUES.id_profiles_normgroups = tblPROFILES_NORMGROUPS.id_profiles_normgroups WHERE (tblPROFILES_NORMGROUPS_VALUES.dimension = 'EXTROVERSION/UTÅTVÄNDHET') AND (tblPROFILES_NORMGROUPS.id_profiles_normgroups = 5))< 0 )
+ ( SELECT 1 WHERE ( SELECT tblDIMRESULTS.scaledscore FROM tblDIMRESULTS INNER JOIN tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo INNER JOIN tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions WHERE id_tests = (SELECT TOP 1 id_tests FROM tblTESTS WHERE (id_actors = tblACTORS.id_actors) AND (testname = 'bigfive249') ORDER BY regdatetime DESC) AND tblDIMENSIONS.dimension = 'Försiktighet') - (SELECT tblPROFILES_NORMGROUPS_VALUES.score FROM tblPROFILES_NORMGROUPS_VALUES INNER JOIN tblPROFILES_NORMGROUPS ON tblPROFILES_NORMGROUPS_VALUES.id_profiles_normgroups = tblPROFILES_NORMGROUPS.id_profiles_normgroups WHERE (tblPROFILES_NORMGROUPS_VALUES.dimension = 'Försiktighet') AND (tblPROFILES_NORMGROUPS.id_profiles_normgroups = 5)) > 0 )
+ ( SELECT 1 WHERE ( SELECT tblDIMRESULTS.scaledscore FROM tblDIMRESULTS INNER JOIN tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo INNER JOIN tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions WHERE id_tests = (SELECT TOP 1 id_tests FROM tblTESTS WHERE (id_actors = tblACTORS.id_actors) AND (testname = 'bigfive249') ORDER BY regdatetime DESC) AND tblDIMENSIONS.dimension = 'Anspråkslöshet') - (SELECT tblPROFILES_NORMGROUPS_VALUES.score FROM tblPROFILES_NORMGROUPS_VALUES INNER JOIN tblPROFILES_NORMGROUPS ON tblPROFILES_NORMGROUPS_VALUES.id_profiles_normgroups = tblPROFILES_NORMGROUPS.id_profiles_normgroups WHERE (tblPROFILES_NORMGROUPS_VALUES.dimension = 'Anspråkslöshet') AND (tblPROFILES_NORMGROUPS.id_profiles_normgroups = 5)) > 0 )
+ ( SELECT 1 WHERE ( SELECT tblDIMRESULTS.scaledscore FROM tblDIMRESULTS INNER JOIN tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo INNER JOIN tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions WHERE id_tests = (SELECT TOP 1 id_tests FROM tblTESTS WHERE (id_actors = tblACTORS.id_actors) AND (testname = 'bigfive249') ORDER BY regdatetime DESC) AND tblDIMENSIONS.dimension = 'Samarbete') - (SELECT tblPROFILES_NORMGROUPS_VALUES.score FROM tblPROFILES_NORMGROUPS_VALUES INNER JOIN tblPROFILES_NORMGROUPS ON tblPROFILES_NORMGROUPS_VALUES.id_profiles_normgroups = tblPROFILES_NORMGROUPS.id_profiles_normgroups WHERE (tblPROFILES_NORMGROUPS_VALUES.dimension = 'Samarbete') AND (tblPROFILES_NORMGROUPS.id_profiles_normgroups = 5)) > 0 )
+ ( SELECT 1 WHERE ( SELECT tblDIMRESULTS.scaledscore FROM tblDIMRESULTS INNER JOIN tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo INNER JOIN tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions WHERE id_tests = (SELECT TOP 1 id_tests FROM tblTESTS WHERE (id_actors = tblACTORS.id_actors) AND (testname = 'bigfive249') ORDER BY regdatetime DESC) AND tblDIMENSIONS.dimension = 'Moralisk principfasthet') - (SELECT tblPROFILES_NORMGROUPS_VALUES.score FROM tblPROFILES_NORMGROUPS_VALUES INNER JOIN tblPROFILES_NORMGROUPS ON tblPROFILES_NORMGROUPS_VALUES.id_profiles_normgroups = tblPROFILES_NORMGROUPS.id_profiles_normgroups WHERE (tblPROFILES_NORMGROUPS_VALUES.dimension = 'Moralisk principfasthet') AND (tblPROFILES_NORMGROUPS.id_profiles_normgroups = 5)) > 0 )AS rightsideantal

www.mirrorgate.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-24 : 04:51:38

do you really expect somebody to go through this mess and help you with the solution? can you please post the info in below format to help us help u?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources