• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7419
  • Last Modified:

If statements in a SQL View or Query

Hi There
I have a SQL Query

Select Amount,TransactionType from Transactions

I want to put a condition statement in the Query where if the TransactionType=Collected put the Amount field in a new column called collected and in the amount column.

So the SQL would be something like this

Select Amount ,TransactionType ,(If Transactiontype= 'Collected' then use amount) As Collected
from Transactions.

I want to be able to do this in a view which is built up of a few tables.
I kept the SQL Statement simple as to get an Idea as how this is done.
  • 2
2 Solutions
use the CASE statement
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Select Amount ,TransactionType , case when Transactiontype = 'Collected' then amount else null end As Collected
from Transactions
Select Amount ,TransactionType ,
 case isnull(Transactiontype,'')
    when 'Collected' then amount
     else 'whatever'
 end As Collected
from Transactions
Hello all

first of all merry christmas to everyone. I am new with this website and SQL ad hopefully I can find  somebody to help me.  I am trying to create a" SQL" view and one of the coloum should populate a value in a coloumn according to the paramaters

If l.lft_start_date>Now(),"N",If l.lft_end_date Between Now() And Now()-7,"D",If l.leaflet_internet_date Between Now() AndNow()-7,"U","V"

Is there any way to ad if statements in the VIEW?

My Sql Statement is :

SELECT     TOP 100 PERCENT l.leaflet_id, l.lft_services AS leaflet_services, b.brc_route_desc AS leaflet_route_desc, b.brc_start_date AS leaflet_start_date,
                      b.brc_end_date AS leaflet_end_date, b.internet AS leaflet_internet_date, ISNULL(b.change_note, 'Please see timetable for details.') AS change_note,
                      l.lft_start_date, l.lft_end_date, pt.product_type_id, l.lft_obsolete, b.brc_obsolete
FROM         dbo.barcode b INNER JOIN
                      dbo.leaflet l ON b.leaflet_id = l.leaflet_id INNER JOIN
                      dbo.part p ON l.leaflet_id = p.leaflet_id INNER JOIN
                      dbo.service s ON p.service_id = s.service_id INNER JOIN
                      dbo.product_type pt ON b.product_type_id = pt.product_type_id
GROUP BY l.leaflet_id, l.lft_services, b.brc_route_desc, b.brc_start_date, b.brc_end_date, b.internet, ISNULL(b.change_note, 'Please see timetable for details.'),
                      l.lft_start_date, l.lft_end_date, pt.product_type_id, l.lft_obsolete, b.brc_obsolete
HAVING      (pt.product_type_id = 3) AND (l.lft_obsolete = 0) AND (b.brc_obsolete = 0) AND (l.lft_services IS NOT NULL) AND (NOT (l.lft_services LIKE '[*Hospital*]'))
                      AND (NOT (l.lft_services LIKE '[*Area*]'))
ORDER BY l.leaflet_id

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now