SQL Query

The following query is supposed to give me the sub account number (arcf.arcf_alt_cust_n) by matching the arcf.arcf_cust_n to arcf.arcf_parent_cust_n where the length is greater than 1. I keep getting syntax errors. Here's what I wrote:

CASE
    WHEN LEN( ARCF.ARCF_PARENT_CUST_N ) > 1
    THEN (CASE
            WHEN LEN((SELECT ARCF.ARCF_ALT_CUST_N FROM ARCF  WHERE ARCF.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)) > 1 THEN ( SELECT dbo.TRIM(ARCF.ARCF_ALT_CUST_N) FROM ARCF  WHERE ARCF.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
            ELSE ( SELECT dbo.TRIM(ARCF.ARCF_CUST_N) FROM ARCF  WHERE ARCF.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )
          END)  
    ELSE (CASE
            WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1 THEN dbo.TRIM(ARCF.ARCF_ALT_CUST_N)
            ELSE dbo.TRIM(ARCF.ARCF_CUST_N)
          END)
END AS SUB_ACCOUNT,
LVL 1
isamesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
It is most likely that (SELECT ARCF.ARCF_ALT_CUST_N FROM ARCF  WHERE ARCF.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N) returns more than one row. The ARCF_ALT_CUST_N needs to be selected as part of the main query. Can you provide the whole query, so that we can help you to include it there
0
Scott PletcherSenior DBACommented:
You need to use a different alias for the table in the subquery, rather than using ARCF for both:

CASE
     WHEN LEN( ARCF.ARCF_PARENT_CUST_N ) > 1
     THEN (SELECT CASE WHEN LEN(ARCF2.ARCF_ALT_CUST_N) > 1
                       THEN dbo.TRIM(ARCF2.ARCF_ALT_CUST_N)
                       ELSE dbo.TRIM(ARCF2.ARCF_CUST_N) END
               FROM ARCF ARCF2
               WHERE
                   ARCF2.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)
     ELSE
         CASE WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1
              THEN dbo.TRIM(ARCF.ARCF_ALT_CUST_N)
              ELSE dbo.TRIM(ARCF.ARCF_CUST_N) END
END AS SUB_ACCOUNT,
0
isamesAuthor Commented:
I used the following query:

Select
CASE
     WHEN LEN( ARCF.ARCF_PARENT_CUST_N ) > 1
     THEN (SELECT CASE WHEN LEN(ARCF2.ARCF_ALT_CUST_N) > 1
                       THEN RTRIM(Ltrim(ARCF2.ARCF_ALT_CUST_N))
                       ELSE RTRIM(LTRIM(ARCF2.ARCF_CUST_N)) END
               FROM ARCF ARCF2
               WHERE
                   ARCF2.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N)
     ELSE
         CASE WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1
              THEN RTRIM(LTRIM(ARCF.ARCF_ALT_CUST_N))
              ELSE RTRIM(LTRIM(ARCF.ARCF_CUST_N)) END
END


This worked in the SQL query.

When I copied the query into my SEI intelligence software as a calculated column, it says the query is good. But when I add the column into the view I'm creating, I get the following error:


Caller: FacadeLoadBlock_LoadBlocksForViewRecursiveCompleted in TreeListViewModel

DALService

Error occured in RequestDAL.LoadNextRecordBlockAsc (2000,,)
+++: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.+++>>   at Nectari.DAL.RequestDAL.LoadNextRecordBlock(Int32 loadsize, Boolean forceStopLoadingData)
   at Nectari.Services.NectariService.LoadNextRecordBlock(String statementToken, Int32 nbElement, Boolean forceStopLoadingData, INectariTraceEvent& eventEntity)

Errors
    0) Error occured in RequestDAL.LoadNextRecordBlockAsc (2000,,)
    0) +++: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.+++>>   at Nectari.DAL.RequestDAL.LoadNextRecordBlock(Int32 loadsize, Boolean forceStopLoadingData)
   at Nectari.Services.NectariService.LoadNextRecordBlock(String statementToken, Int32 nbElement, Boolean forceStopLoadingData, INectariTraceEvent& eventEntity)


Any ideas?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
That's logical, but it's a data issue.

The same ARCF_CUST_N value appears multiple times in ARCF.  

A "TOP (1)" will fix the issue, but that gives you essentially randomly any of the rows that match.  It's much better to specify an ORDER BY with the TOP (1) to give you a specific match, if you can work out what the ORDER BY should be: maybe the MAX(LEN(ARCF_CUST_N)) for the lowest matching level???


CASE
      WHEN LEN( ARCF.ARCF_PARENT_CUST_N ) > 1
      THEN (SELECT TOP (1) CASE WHEN LEN(ARCF2.ARCF_ALT_CUST_N) > 1
                        THEN RTRIM(Ltrim(ARCF2.ARCF_ALT_CUST_N))
                        ELSE RTRIM(LTRIM(ARCF2.ARCF_CUST_N)) END
                FROM ARCF ARCF2
                WHERE
                    ARCF2.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N
                --ORDER BY ??
                )
      ELSE
          CASE WHEN LEN(ARCF.ARCF_ALT_CUST_N) > 1
               THEN RTRIM(LTRIM(ARCF.ARCF_ALT_CUST_N))
               ELSE RTRIM(LTRIM(ARCF.ARCF_CUST_N)) END
 END
0
isamesAuthor Commented:
I've requested that this question be deleted for the following reason:

I figured out what was wrong
0
Scott PletcherSenior DBACommented:
As I noted, I believe that because of the way SQL resolves subquery references, this subquery:

THEN ( SELECT dbo.TRIM(ARCF.ARCF_ALT_CUST_N) FROM ARCF  WHERE ARCF.ARCF_CUST_N = ARCF.ARCF_PARENT_CUST_N )

would have found rows where the cust_n and parent_cust_n were the same for that row.  Even if something else was wrong, that should have an issue as well.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.