CODEWITHSUNDEEP
sql-servercastingconcatenationcase
Chris
Chris

Reputation: 570

Getting CAST and CASE together

I've got a horrible problem query to write

SELECT TOP 25
uinv = (CASE 
    WHEN exported = 1 THEN 'Sent To Accounts' 
    WHEN queued = 1 THEN 'Finalised' 
    WHEN reviewed = 1 THEN 'Pro Forma' 
    WHEN started = 1 THEN 'New' 
END)
,
(
    CAST(acc_id AS VARCHAR) + ' / ' + 
    CAST(transactiontype AS VARCHAR) + ' / ' + 
    CAST(reference AS VARCHAR) + ' / '
) 
AS label 
FROM tablename;

That works fine, but I need to get the uinv field in the AS label. I've tried all sorts of permutations but I just cannot get it right...

Any help please?

Thanks, Chris

Upvotes: 0

Views: 103

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

If you just want uinv inside instead of as a separate column, all you need to do is move the case inside;

SELECT TOP 25
(
    CAST(acc_id AS VARCHAR) + ' / ' + 
    CAST(transactiontype AS VARCHAR) + ' / ' + 
    CAST(reference AS VARCHAR) + ' / ' + 
    CASE 
      WHEN exported = 1 THEN 'Sent To Accounts' 
      WHEN queued = 1 THEN 'Finalised' 
      WHEN reviewed = 1 THEN 'Pro Forma' 
      WHEN started = 1 THEN 'New' 
    END
) 
AS label 
FROM tablename;

If you want it inside the label in addition to being a separate column, you can use a CTE;

WITH cte AS (
  SELECT *,CASE 
      WHEN exported = 1 THEN 'Sent To Accounts' 
      WHEN queued = 1 THEN 'Finalised' 
      WHEN reviewed = 1 THEN 'Pro Forma' 
      WHEN started = 1 THEN 'New' 
    END uinv
  FROM tablename
)
SELECT uinv,  
    CAST(acc_id AS VARCHAR) + ' / ' + 
    CAST(transactiontype AS VARCHAR) + ' / ' + 
    CAST(reference AS VARCHAR) + ' / ' + 
    uinv
AS label 
FROM cte;

Upvotes: 1

Arion
Arion

Reputation: 31249

Do you mean like this? :

SELECT
    tbl.uinv,
    (
        tbl.uinv+
        CAST(acc_id AS VARCHAR) + ' / ' + 
        CAST(transactiontype AS VARCHAR) + ' / ' + 
        CAST(reference AS VARCHAR) + ' / '
    ) 
    AS label 
FROM
(
    SELECT TOP 25
    uinv = (CASE 
        WHEN exported = 1 THEN 'Sent To Accounts' 
        WHEN queued = 1 THEN 'Finalised' 
        WHEN reviewed = 1 THEN 'Pro Forma' 
        WHEN started = 1 THEN 'New' 
    END),
    tablename.acc_id,
    tablename.transactiontype,
    tablename.reference
    FROM tablename
) AS tbl

Upvotes: 1

Related Questions