?
Solved

Return a Different Value of a SQL Query if the Result is 0.

Posted on 2012-03-14
3
Medium Priority
?
191 Views
Last Modified: 2012-03-14
I'm wading into the world of writing SQL Queries, Functions, and Report Writing. I've got a query setup that returns all the Data I need with the exception of 2 variables that I need to return a different result.

The Query is pulling Employees Pay and Bill information. There are 2 employees that have a Pay Rate of $0 and I want to be able to substitute that $0 value with their Correct Value $22.50. If I can do that I can then create the functions to calculate the total Pay and Bill and Margin amounts.

The Employee ID Field is: [informix_styhstwe Query].empl_code
The Employee ID's of the Rates Needing Changed is: 334383 and 159154
The Pay Rate Field is:   [informix_styhstwe Query].reg_prate
The Rate Needs to be Changed from 0 to 22.50


So in summary - I need to replace the Pay Rates for Employee ID's 334383 and 159154 from 0 to 22.50.

I don't want to update the Database. Just change value of what is returned.


My Query is pasted below:

SELECT     [informix_styhstwe Query].week_end
                  , [informix_styhstwe Query].pay_month
                  , [informix_styhstwe Query].reference
                  , [informix_styhstwe Query].empl_code
                  , [informix_styhstwe Query].reg_phrs
                  , [informix_styhstwe Query].reg_prate
                  , [informix_styhstwe Query].ovt_phrs
                  , [informix_styhstwe Query].ovt_prate
                  , [informix_styhstwe Query].tot_phrs
                  , [informix_styhstwe Query].tot_bhrs
                  , [informix_styhstwe Query].tot_bamt
                  , [informix_styhstwe Query].tot_mtax_bamt
                  , [informix_styhstwe Query].tot_mded_bamt
                  , [informix_styhstwe Query].tot_mntx_bamt
                  , [informix_styhstwe Query].tot_pamt
                  , [informix_styhstwe Query].tot_mtax_pamt
                  , [informix_styhstwe Query].tot_mded_pamt
                  , [informix_styhstwe Query].tot_mntx_pamt
                  , [informix_styhstre Query].obl_total
                  , [informix_styhstwe Query].cust_code
                  , [informix_styhstwe Query].pay_year
FROM         [informix_styhstwe Query] INNER JOIN
                      [informix_styhstre Query] ON [informix_styhstwe Query].merge_no = [informix_styhstre Query].merge_no
WHERE     ([informix_styhstwe Query].cust_code = 1101) AND ([informix_styhstwe Query].pay_year = 2011) AND ([informix_styhstwe Query].reference = N'12690173')
ORDER BY [informix_styhstwe Query].reg_prate, [informix_styhstwe Query].week_end, [informix_styhstwe Query].empl_code
0
Comment
Question by:thomas-sherrouse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37720960
Replace:

, [informix_styhstwe Query].reg_prate

with:

, CASE WHEN [informix_styhstwe Query].empl_code IN ('334383', '159154') THEN 22.50 ELSE
[informix_styhstwe Query].reg_prate END
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37720989
Technically it's better to create a lookup table and join to that, just in case you have to add extra overrides later, but the above will work for a "quick-and-dirty" approach.

Btw, it's considered best practice to use "aliases" to reference tables in queries.

SQL allows you to specify an "alias" name after the table name.  This makes it easier to code and change queries.  For example:

SELECT     we.week_end
                  , we.pay_month
                  , we.reference
                  , ...
                  , we.cust_code
                  , we.pay_year
FROM         [informix_styhstwe Query] AS we INNER JOIN
                      [informix_styhstre Query] AS re ON we.merge_no = re.merge_no
WHERE     (we.cust_code = 1101) AND (we.pay_year = 2011) AND (we.reference = N'12690173')
ORDER BY we.reg_prate, we.week_end, we.empl_code


The "AS" is optional, so you will often see it written this way:

FROM         [informix_styhstwe Query] we INNER JOIN
0
 

Author Closing Comment

by:thomas-sherrouse
ID: 37721069
Awesome Thanks! - I added a Column Name:

, CASE WHEN [informix_styhstwe Query].empl_code IN ('334383', '159154') THEN 22.50 ELSE
[informix_styhstwe Query].reg_prate END AS Reg_PayRate

 Thanks for the input on the other Best Practices. I'm still working back and forth between writing the Queries and Creating them in the wizard and then editing the out of the Wizard. I'm learning. I'll be sure to be back when I run into another question!

Thanks!!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

741 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