CODEWITHSUNDEEP
sql
scratmiller
scratmiller

Reputation: 141

Case in SQL Server?

I am learning SQL Server by exercising some problems.

In one of that problems I have 4 database tables: ORDERS, SHIPMENTS, WHFICHES and ATTACHMENTS

One of them is hold attachment references to other tables that is named ATTACHMENTS. It has 3 columns:

alt text

By checking value of RECTYPE, I want to join table to other tables through RECREF.

Here my SQL with switch case:

SELECT LREF,RECREF,RECTYPE,
CASE RECTYPE 
WHEN 100 THEN (SELECT O.ORDERNO FROM AV_ATTACHMENTS A LEFT OUTER JOIN AV_ORDERS O ON A.RECREF = O.LREF WHERE A.RECREF = O.LREF)   
WHEN 200 THEN (SELECT S.FSNNO FROM AV_ATTACHMENTS A LEFT OUTER JOIN AV_SHIPMENTS S ON A.RECREF = S.LREF WHERE A.RECREF = S.LREF)
ELSE (SELECT F.WHFICHENO FROM AV_ATTACHMENTS A LEFT OUTER JOIN AV_WHFICHES F ON A.RECREF = F.WHFICHENO WHERE A.RECREF = F.LREF)
END AS FICHE
 FROM AV_ATTACHMENTS

Also, in other tables I have a unique primary key LREF and another value will be used as FICHENO.

I get this warning:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , , >= or when the subquery is used as an expression.

What should I do exactly, because I can't find any solution. Should I change my SQL statement? Any suggestions?

My kind regards and thanks in advance.

Upvotes: 1

Views: 328

Answers (5)

subhash
subhash

Reputation: 286

Tyr below:

SELECT LREF,RECREF,RECTYPE, 
       CASE RECTYPE
          WHEN 100 THEN (
                         SELECT TOP 1 O.ORDERNO 
                           FROM AV_ATTACHMENTS A 
                                LEFT OUTER JOIN AV_ORDERS O 
                                   ON A.RECREF = O.LREF 
                          WHERE A.RECREF = O.LREF
                        )
          WHEN 200 THEN (
                         SELECT TOP 1 S.FSNNO 
                           FROM AV_ATTACHMENTS A 
                                LEFT OUTER JOIN AV_SHIPMENTS S 
                                   ON A.RECREF = S.LREF 
                          WHERE A.RECREF = S.LREF
                        ) 
          ELSE (
                SELECT TOP 1 F.WHFICHENO 
                  FROM AV_ATTACHMENTS A 
                       LEFT OUTER JOIN AV_WHFICHES F 
                          ON A.RECREF = F.WHFICHENO 
                 WHERE A.RECREF = F.LREF
               ) 
       END AS FICHE 
  FROM AV_ATTACHMENTS;

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Given the error that you're receiving you'll need to change the query. Something like the following may be closer to what you want:

SELECT LREF, RECREF, RECTYPE
  FROM AV_ATTACHMENTS V
LEFT OUTER JOIN (SELECT A.LREF, O.ORDERNO AS FICHE
                   FROM AV_ATTACHMENTS A
                 LEFT OUTER JOIN AV_ORDERS O
                   ON A.RECREF = O.LREF
                 WHERE A.RECREF = O.LREF) T1
  ON (V.RECTYPE = 100 AND
      T1.LREF = V.LREF)
LEFT OUTER JOIN (SELECT A.LREF, S.FSNNO A FICHE
                   FROM AV_ATTACHMENTS A
                 LEFT OUTER JOIN AV_SHIPMENTS S
                   ON A.RECREF = S.LREF
                 WHERE A.RECREF = S.LREF) T2
  ON (V.RECTYPE = 200 AND
      T2.LREF = V.LREF)
LEFT OUTER JOIN (SELECT A.LREF, F.WHFICHENO AS FICHE
                   FROM AV_ATTACHMENTS A
                 LEFT OUTER JOIN AV_WHFICHES F
                   ON A.RECREF = F.WHFICHENO
                 WHERE A.RECREF = F.LREF) T3
  ON (V.RECTYPE NOT IN (100, 200) AND
      T3.LREF = V.LREF);

Share and enjoy.

Upvotes: 2

van
van

Reputation: 76992

Give a try to the following, but you have to make sure that those three fields have compatible type:

SELECT  A.LREF, 
        A.RECREF, 
        A.RECTYPE,
        CASE A.RECTYPE 
            WHEN 100 THEN O.ORDERNO
            WHEN 200 THEN S.FSNNO
            WHEN 300 THEN F.WHFICHENO
            ELSE NULL
        END AS FICHE
FROM        AV_ATTACHMENTS A
LEFT JOIN   AV_ORDERS O 
        ON  A.RECREF = O.LREF
        AND A.RECTYPE = 100
LEFT JOIN   AV_SHIPMENTS S 
        ON  A.RECREF = S.LREF
        AND A.RECTYPE = 200
LEFT JOIN   AV_WHFICHES F 
        ON  A.RECREF = F.WHFICHENO
        AND A.RECTYPE = 300

Upvotes: 3

SmartestVEGA
SmartestVEGA

Reputation: 8889

You can insert it into two temp tables using a where condition

and join those temp tables ..Voila!!

Upvotes: 0

Joachim VR
Joachim VR

Reputation: 2340

Your subqueries are placed in the select statement, so it's not possible to have multiple result field in a single select field. A way to do this would be to use SELECT TOP 1 in your subqueries.

Upvotes: 0

Related Questions