Use case & when statement

Question

0.00/5 (No votes)

See more:

I've got a temp table as Table1 with the following fields:

Account ID, CustomerID, State
4564640 252 TX
4561210 257 WI
4564620 253 TX
4564720 259 NM

Here is what I am trying to achieve, I need to look up each customer with its Account ID from a different Server according to its state from Table2 to get the proper "CustomerID" and using that ID, check in Table3 if that Customer is actually valid or not.

What I have tried:

SELECT CAST(
            CASE
                WHEN LocationState = 'TX'
                        THEN

Posted 13-Mar-17 5:38am

SmartDeveloping

Updated 13-Mar-17 10:03am

Add a Solution

Comments

CHill60 13-Mar-17 11:40am

What do tables 2 and 3 look like?

SmartDeveloping 13-Mar-17 11:42am

Table 2 is a contact table:
Account ID, FirstName etc. etc.. I need to get in this table to pick up correct CustomerID

Table 3:
CustomerID, ActiveCustomer... Table where I can see all the transactions of the customer.

CHill60 13-Mar-17 12:49pm

I'm trying to post a solution but there is a problem with either my system or this site at the moment

SmartDeveloping 13-Mar-17 13:09pm

Yeah there seems to be.

Bryian Tan 13-Mar-17 13:40pm

anyway, not able to post solution. See if this will work.

"Just thinking out loud, how about calling a function? But, also check if performance will be an issue."

Example:
Hide Copy Code

CASE 
	WHEN LocationState = 'TX' THEN
		(
			SELECT returnCol FROM  [dbo].[YourTXFuncCustomerAccountCheck](Account ID, CustomerID, State)

WHEN LocationState = 'MD' THEN
		(
			SELECT returnCol FROM  [dbo].[YourMDFuncCustomerAccountCheck](Account ID, CustomerID, State)		
		)

SmartDeveloping 13-Mar-17 14:25pm

Where would I let the query know I am picking up LocationState from Temp table.

Bryian Tan 13-Mar-17 14:30pm

Table1, I guess. This is what you posted,
SELECT CAST(
CASE
WHEN LocationState = 'TX'
THEN
...
I guess here you will have FROM Table1???

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-03-13T10:00:00

You don't need to use CASE ... WHEN ... THEN ... END statement. You need to use JOIN's. See: Visual Representation of SQL Joins[^]

SQL

SELECT t1.<FirstField1>, t2.<AnotherField>
FROM [dbo1].Table1 AS t1 <LEFT|RIGHT> JOIN [dbo2].Table2 AS t2 ON t1.KeyField = t2.OtherKeyField

We can't provide more details due to incomplete information about data.

CHill60 · Answer 2 · 2017-03-13T10:03:00

I'm going to post this now anyway - it's the one I was trying to post earlier. Apologies - it contains a lot of what the others have said
e technique you are looking for is JOIN - see this article Visual Representation of SQL Joins[^]

There is no need to use CASE and I have no idea why you are trying to CAST something.

From your description of your tables you are making mistakes with your database schema. the ActiveCustomer flag is a feature of a Contact or Customer not the Transactions for the customer. You need to read up on Database Normalization[^]

It's also very confusing because your Table1 already has CustomerID on it so I have no idea why you need to go somewhere to get the "correct" CustomerID.

I used some sample tables that look like this:

SQL

create table table1
(
	[Account ID] varchar(7), 
	CustomerID int, 
	[State] varchar(3)
)
insert into table1 values
('4564640', 252, 'TX'),
('4561210', 257, 'WI'),
('4564620', 253, 'TX'),
('4564720', 259, 'NM')

create table contact
(
	[Account ID] varchar(7),
	[CustomerID] int,
	ActiveCustomer bit,
	FirstName varchar(50)
	-- other contact fields
)
insert into contact values
('4564640', 252, 1, 'John'), --active
('4564620', 253, 0, 'Fred')  -- not active

create table [transactions]
(
	[CustomerID] int,
	transdate date
	-- other transaction fields
)

insert into transactions values
(252, getdate()),
(252, getdate()),
(253, getdate())

All you need to do is do a join across the table to get the information you need:

lect c.CustomerID, t1.[Account ID], FirstName, [State], CASE WHEN ActiveCustomer = 1 THEN 'Active' ELSE 'Inactive' END AS [status]
from transactions t
inner join contact c on t.CustomerID = c.CustomerID
inner join table1 t1 on t1.[Account ID] = c.[Account ID]

You can include a WHERE clause to omit inactive customers if you want to:

SQL

WHERE c.ActiveCustomer = 1

If one or more of the tables is on a different server then you just need to ensure that you use the fully qualified table names
e.g.

SQL

MyOtherServer.DatabaseName.schemaname.tablename

You may need to set the servers up as Linked Servers (Database Engine)[^]

Use case & when statement

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Use case & when statement

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us