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
Solved

sql syntax

Posted on 2014-10-23
4
126 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
  • 3
4 Comments
 
LVL 65

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 65

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 500 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 65

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

861 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