substring function can't figure out

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

substring function can't figure out

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-05 : 15:10:39

I have a SQL query in Visual Studio (SSRS). I have a GL Account field that is formatted such as 100-400-123-1234. I wanted to use the substring function to pull out the second set of numbers which I can assign a location:

CASE WHEN substring(GlAccount,5,3)= '400' THEN 'Gainesville'
CASE WHEN substring(GlAccount,5,3)= '401' THEN 'Aledo'

I tried this and it comes back with syntax errors. Can anyone tell me how to approach this as I have a list of about 35 locations that I need to do like this. Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 15:31:31

[code]DECLARE @Account TABLE (GLAccount VARCHAR(16))

INSERT @Account
SELECT '100-400-123-1234'
UNION ALL SELECT '100-401-123-1234'
UNION ALL SELECT '100-402-123-1234'

SELECT
GLAccount,
CASE
WHEN SUBSTRING(GlAccount,5,3) = '400' THEN 'Gainesville'
WHEN SUBSTRING(GlAccount,5,3) = '401' THEN 'Aledo'
ELSE 'Unknown'
END AS Location
FROM
@Account

-- Or
SELECT
GLAccount,
CASE SUBSTRING(GlAccount,5,3)
WHEN '400' THEN 'Gainesville'
WHEN '401' THEN 'Aledo'
ELSE 'Unknown'
END AS Location
FROM
@Account
[/code]

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-05 : 15:57:21

I guess I should put the GL Account code format such as:
XXX-XXX-XXX-XXXX where the second set of numbers is where I'm pulling the location code from. The other numbers can be any combination.

Sorry, new to this. In Crystal my syntax was simply:

if mid(GLAccount,5,3)= '400' then 'Gainesville'
else if mid(GLAccount,5,3)= '401' then 'Aledo'
etc, etc, etc,
else ' '

dineshasanka
Yak Posting Veteran

72 Posts

Posted - 2008-03-05 : 16:52:28

use Switch command

SWITCH(MID(GLACCOUNT,5,3)="400","Gainesville",MID(GLACCOUNT,5,3)="401","Aledo")

---------------------
http://dineshasanka.spaces.live.com/

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-06 : 10:39:35

It states that MID is not a recognized built in function. I'm trying to figure out why this doesn't work. It says that there is an error near 'AS'.

case when substring(a.Account,5,3)='400' then 'Gainesville' else case when substring(a.Account,5,3)='401'then'Aledo'
else case when substring(a.Account,5,3)='402'then'Kilgore'
ELSE ' 'END AS Location

This is the only thing that currently is kicking my butt. Could someone help me in this syntax.

Here is the entire query if anyone needs to look at it. Thanks.

SELECT CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 0 THEN id.SOExtChargeAmount ELSE 0 END AS CurrentMonth,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 1 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessOne,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 2 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessTwo,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 3 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessThree,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 4 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessFour,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 5 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessFive,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 6 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessSix,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 7 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessSeven,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 8 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEight,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 9 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessNine,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 10 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessTen,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven,
CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, GetDate()) = 0 THEN id.SOExtChargeAmount ELSE 0 END AS CurrentYearTotal,
CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, GetDate()) = 1 THEN id.SOExtChargeAmount ELSE 0 END AS LastYearTotal,
CASE WHEN DATEADD([year], - 1, GetDate()) > ih.SOTransDate AND DATEDIFF([YEAR], ih.SOTransDate, GetDate())
= 1 THEN id.SOExtChargeAmount ELSE 0 END AS LastYearYTD,
case when substring(a.Account,5,3)='400' then 'Gainesville' else case when substring(a.Account,5,3)='401'then'Aledo'
else case when substring(a.Account,5,3)='402'then'Kilgore'
ELSE ' 'END AS Location,
ih.SOTransDate, ih.InvoiceNumber, cm.CustomerName, id.DetailSeqNumber, id.LineType, a.Account, id.SOExtChargeAmount
FROM dbo.MAS_CCS_AR1_CustomerMaster cm, dbo.MAS_CCS_ARN_InvHistoryHeader ih, dbo.MAS_CCS_ARO_InvHistoryDetail id,
dbo.MAS_CCS_GL_Account a
WHERE cm.CustomerNumber = ih.CustomerNumber AND ih.InvoiceNumber = id.InvoiceNumber AND id.SOGLSalesAcct = a.AccountKey

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-06 : 10:45:04

are you trying to nest them? or do you just mean...
case
when substring(a.Account,5,3)='400' then 'Gainesville'
when substring(a.Account,5,3)='401'then'Aledo'
when substring(a.Account,5,3)='402'then'Kilgore'
ELSE ' 'END AS Location,

Em

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-06 : 11:43:18

This works in SQL Management Studio, but not in Visual Studio when building report. Any suggestions? Thanks for your help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-06 : 11:51:05

Try removing the brackets maybe? for example change [YEAR] to YEAR. I think adding the brackets might be messing things up..?

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-06 : 12:01:21

Visual Studio adds the brackets automatically once run. It's an error having to do with the syntax on substring(a.Account,5,3)='400' then 'Aledo' else ' ' end as Location,

If I have just this one string instead of trying multiple when statements it works fine, but I can't seem to have it work otherwise.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-06 : 12:17:51

Try looking at Elancaster's response.

The actually error, it appears, is that you do not have enough END's for that CASE statement. But, do you really want them nested as you have coded it or do you just want a simple case statement as Elancsater as shown?

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-07 : 12:11:19

OK. Thanks. I don't know why but I had created my report already and when I would take the code and paste it in it wouldn't run in Visual Studio, but if I started from scratch building a report it would run. Could it be something on the report side that I have that is giving me an error? Anyhow, I'm starting from scratch.

One more question if you don't mind. On this report I have it grouped by Location and I have the Customers sorted by most revenue descending. How would I limit this to a Top N report where I would only see the top N=20? And where the last row in the group would be a combination of all the <20 in revenue? In Crystal there is a Top N report function to where the 20th row would be 'Others' and was a sum of all the customers that weren't in the top 19. Thanks for any help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources