Solved

sql syntax

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 24 54
SQL group by query to return records with highest value 6 33
SQL Recursion schedule 13 35
Can a Trigger trigger a Trigger? 4 22
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…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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