If statements in a SQL View or Query

Posted on 2007-10-10
Medium Priority
Last Modified: 2013-11-16
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.
Question by:Stanton_Roux
  • 2
LVL 19

Expert Comment

ID: 20047408
use the CASE statement
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 20047409
Select Amount ,TransactionType , case when Transactiontype = 'Collected' then amount else null end As Collected
from Transactions
LVL 19

Assisted Solution

frankytee earned 1000 total points
ID: 20047413
Select Amount ,TransactionType ,
 case isnull(Transactiontype,'')
    when 'Collected' then amount
     else 'whatever'
 end As Collected
from Transactions

Expert Comment

ID: 23239856
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


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question