Solved

sql syntax

Posted on 2014-10-23
4
124 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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