Query

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  November 6, 2017 at 8:41 am

  #341196

  Hi
     I am displaying Distinct Location Code from a table . It has some Blank values. In a report i want Value of Blank Location Code should also get displayed. I am working on Matrix report.

  Below Sql is not working for Blank Location Code.

  Select '' as Code from [Test $G_L Entry]
  Union
  Select Distinct[Location Code] as Code from [Test $G_L Entry]
  Thanks

• Thom A

  SSC Guru

  Points: 99133

  More actions

  November 6, 2017 at 8:54 am

  #1966811

  I realise that there is a language barrier here, but your statement doesn't really make sense. Cold you try rewording your statement into a question please, and try to be clearer about your goals and problems?

  Thanks.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  November 6, 2017 at 9:38 am

  #1966823

  The "from [Test $G_L Entry]" is is not necessary here:
  Select '' as Code from [Test $G_L Entry]

  You could do this:
  Select '' as Code
union
Select distinct [Location Code] as Code from [Test $G_L Entry];

  or this:
  Select '' as Code
union all
Select Distinct[Location Code] as Code from [Test $G_L Entry];

  Note that UNION (not UNION ALL) forces a unique result set. DISTINCT does too. That means that you don't need both UNION and DISTINCT.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001