?
Solved

sql syntax

Posted on 2014-10-23
4
Medium Priority
?
133 Views
Last Modified: 2014-10-24
I am trying to work out the query column dynamically through the subquery but does not seems to able to get it to work. The column name is regularPrice

SELECT  C.promoCode,C.cartKey, c.userid,
      C.cartId,
      C.productType,
      C.productID,
      C.description,
      C.qty,
      C.price,
      C.taxable,
      CP.price as priceItem,
      C.taxable,
      C.weight,
      C.productKey,
      C.packageKey,
      CP.productKey AS lineProductKey,
      CP.qty AS lineQty,
      CP.packageKey as linePackageKey,
      CP.title AS lineTitle,
      CP.sessionKey,
      CP.sessionName,
      CP.sessionStart,
      CP.sessionEnd,
    PT.isClassIn,
    PT.isInCarIn,
      CP.sessionMapKey,
         S.locationKEy,
    CASE WHEN CP.sessionKey IS NOT NULL
          C.price + (SELECT
              abs(SUM(SF.sessionFeeValueNb))
        FROM lkup_fee F WITH (NOLOCK)
              INNER JOIN session_fee SF WITH (NOLOCK) ON F.feeKey = SF.feeKey
                  AND SF.sessionKey = CP.sessionKey
             WHERE F.feeActiveIn = 1
       ) as regularPrice
       
     ELSE
           regularPrice = C.price
     END
FROM carts C WITH (NOLOCK)
      LEFT OUTER JOIN carts_product CP WITH (NOLOCK) ON C.cartKey = CP.cartKey
    LEFT OUTER JOIN product P WITH (NOLOCK) ON CP.productKey = P.productKey
    LEFT OUTER JOIN lkup_productType PT WITH (NOLOCK) ON P.productTypeKey = PT.productTypeKey
   LEFT outer join session S WITH (NOLOCK) on s.sessionKey = CP.sessionKey
0
Comment
Question by:erikTsomik
[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
  • 3
4 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40400565
>    CASE WHEN CP.sessionKey IS NOT NULL
For starters, you're missing a THEN that should be at the end of this line
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40400569
I think this is what you're after..
   CASE 
      WHEN CP.sessionKey IS NOT NULL THEN C.price + (
         SELECT abs(SUM(SF.sessionFeeValueNb))
         FROM lkup_fee F WITH (NOLOCK) 
            INNER JOIN session_fee SF WITH (NOLOCK) ON F.feeKey = SF.feeKey AND SF.sessionKey = CP.sessionKey
         WHERE F.feeActiveIn = 1)
      ELSE regularPrice = C.price END as regularPrice

Open in new window

btw if it helps I have an article out there called SQL Server CASE Solutions that is an code-heavy montage of common CASE block solutions.
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 2000 total points
ID: 40400571
Some corrections in the case structure:

SELECT  C.promoCode,
C.cartKey, c.userid,
      C.cartId,
      C.productType,
      C.productID,
      C.description,
      C.qty,
      C.price,
      C.taxable,
      CP.price as priceItem,
      C.taxable,
      C.weight,
      C.productKey,
      C.packageKey,
      CP.productKey AS lineProductKey,
      CP.qty AS lineQty,
      CP.packageKey as linePackageKey,
      CP.title AS lineTitle,
      CP.sessionKey,
      CP.sessionName,
      CP.sessionStart,
      CP.sessionEnd,
    PT.isClassIn,
    PT.isInCarIn,
      CP.sessionMapKey,
         S.locationKEy,
    CASE WHEN CP.sessionKey IS NOT NULL
          C.price + (SELECT 
              abs(SUM(SF.sessionFeeValueNb))
        FROM lkup_fee F WITH (NOLOCK) 
              INNER JOIN session_fee SF WITH (NOLOCK) ON F.feeKey = SF.feeKey
                  AND SF.sessionKey = CP.sessionKey
             WHERE F.feeActiveIn = 1
       ) 
       
     ELSE 
           C.price
     END as regularPrice
FROM carts C WITH (NOLOCK)
      LEFT OUTER JOIN carts_product CP WITH (NOLOCK) ON C.cartKey = CP.cartKey
    LEFT OUTER JOIN product P WITH (NOLOCK) ON CP.productKey = P.productKey
    LEFT OUTER JOIN lkup_productType PT WITH (NOLOCK) ON P.productTypeKey = PT.productTypeKey
   LEFT outer join session S WITH (NOLOCK) on s.sessionKey = CP.sessionKey

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40402205
erikTsomik - Did you try my suggestion?
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 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