If statements in a SQL View or Query

Posted on 2007-10-10
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
    LVL 19

    Expert Comment

    use the CASE statement
    LVL 142

    Accepted Solution

    Select Amount ,TransactionType , case when Transactiontype = 'Collected' then amount else null end As Collected
    from Transactions
    LVL 19

    Assisted Solution

    Select Amount ,TransactionType ,
     case isnull(Transactiontype,'')
        when 'Collected' then amount
         else 'whatever'
     end As Collected
    from Transactions

    Expert Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now