Show zero if no record returned

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

General SQL Server Forums

New to SQL Server Programming

Show zero if no record returned

Author

Topic

dibblejon
Starting Member

36 Posts

Posted - 2012-06-28 : 03:36:33

Hi

How can I amend my query below to show a zero if nothing is returned?

SELECT ststats.company, ststats.depot, ststats.part, ststats.sale_period, ststats.sale_year, ststats.units, "CurrentPeriod".notes AS "Period", "CurrentPeriod".code_value::smallint AS "CurrentPeriod", "CurrentYear".notes AS "Year", "CurrentYear".code_value::smallint AS "CurrentYear"
   FROM ststats, "CurrentPeriod", "CurrentYear"
  WHERE ststats.company = "CurrentPeriod".company AND ststats.company = "CurrentYear".company AND ststats.sale_year = "CurrentYear".code_value::smallint AND ststats.sale_period = "CurrentPeriod".code_value::smallint;

dibblejon
Starting Member

36 Posts

Posted - 2012-06-28 : 03:37:51

Sorry I meant if the ststats.units field is blank for a stock record

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-28 : 04:48:22

That query is not a T-SQL query for Microsoft SQL Server.
Which RDBMS are you using?

N 56°04'39.26"
E 12°55'05.63"

dibblejon
Starting Member

36 Posts

Posted - 2012-06-28 : 06:53:12

Apologies

This is using postgres - functionality is pretty similar, if someone could show logic it would help

sshelper
Posting Yak Master

216 Posts

Posted - 2012-06-30 : 03:34:29

If I understood your question correctly, in SQL Server, you can use the CASE statement for this:

SELECT CASE WHEN ststats.units = '' THEN 0 ELSE CAST(ststats.units AS INT) END as units

SQL Server Helper
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources