Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nhess80
Yak Posting Veteran
83 Posts |
 Posted - 2009-04-14 : 15:15:27
|
| I get an error when I try to run this query. It seems to have a problem with the nested query in the CASE statement. I must have coded this wrong because I have seen other examples of people doing this but I cannot figure out what the problem is.Thanks for any help... CASE WHEN Duration > 6 THEN CASE WHEN (SELECT NPANXX FROM ANPI_PREM_012 WHERE (LEFT(RIGHT(Number,10),6) IN (NPANXX)) THEN CASE WHEN Duration / 6 = FLOOR(Duration / 6) THEN (Duration / 60) * 0.013 WHEN Duration / 6 <> FLOOR(Duration / 6) THEN (FLOOR(Duration / 6) * 6 + 6) / (60) * 0.013 END ELSE CASE WHEN Duration / 6 = FLOOR(Duration / 6) THEN (Duration / 60) * 0.012 WHEN Duration / 6 <> FLOOR(Duration / 6) THEN (FLOOR(Duration / 6) * 6 + 6) / (60) * 0.012 END END WHEN Duration < 6 THEN CASE WHEN (SELECT NPANXX FROM ANPI_PREM_012 WHERE (LEFT(RIGHT(Number,10),6) IN (NPANXX)) THEN 0.1 * 0.013 ELSE 0.1 * 0.012 END ELSE 0.1 * 0.012 END AS Cost |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-04-14 : 16:08:28
|
| (SELECT NPANXX FROM ANPI_PREM_012 WHERE (LEFT(RIGHT(Number,10),6) IN (NPANXX)) the above does not equate to any condition like when x = y to be used in the when statement.Also what does (LEFT(RIGHT(Number,10),6) IN (NPANXX) mean? can you explain what you are trying to do. |
 |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2009-04-14 : 16:25:43
|
The LEFT(RIGHT(Number,10),6) is there to retrieve 6 digits out of a 10 or 11 digit phone number. Sometime the field "Number" will contain 12175551212 and sometimes it will contain 2175551212. The LEFT(RIGHT(Number,10),6) is used to retrieve 217555 or whichever number is in the field. The reason I do this is because the NPANXX field contains 6 digit numbers. I’m trying to match these up. So if LEFT(RIGHT(Number,10),6) is in NPANXX then I assign a rate to it and if it is not then I assign another rate to it.quote:
Originally posted by vijayisonly
(SELECT NPANXX FROM ANPI_PREM_012 WHERE (LEFT(RIGHT(Number,10),6) IN (NPANXX)) the above does not equate to any condition like when x = y to be used in the when statement.Also what does (LEFT(RIGHT(Number,10),6) IN (NPANXX) mean? can you explain what you are trying to do.
|
|
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2009-04-14 : 17:49:29
|
| I was able to figure it out. I just did the coding wrong. Figured out the correct way to do it. |
|
|
|
|
|
|
|
|
Using subqueries in a CASE statement