September 3, 2013 at 9:52 am
Hi,
I am trying to do a lookup in SQL and have to create a SQL code for the following condition under the where clause-
-----------------------------------------------
If the Continent_cd (tableA) value is in ("EUR","MED") for the matching POL_Location_cd (tableB) value in the Location_cd (tableA) column then it should be classified as OUT
If the Continent_cd (tableA) value is in ("EUR","MED") for the matching PDL_Location_cd (tableB) value in the Location_cd (tableA) column then it should be classified as IN
else it should be NON
------------------------------------------------
The DDL and sample data are as follows-
----------------------------------
CREATE TABLE [dbo].[tableA](
[LOCATION_CD] [varchar](5) NULL,
[CONTINENT_CD] [varchar](5) NULL
)
CREATE TABLE [dbo].[tableB](
[POR_LOCATION_CD] [varchar](5) NULL,
[PDL_LOCATION_CD] [varchar](5) NULL,
)
insert into [tableA]
values('UYMVD','ESM')
insert into [tableA]
values('BEANR','EUR')
insert into [tableA]
values('DEHAM','EUR')
insert into [tableA]
values('USSSI','MED')
insert into [tableB]
values('UYMVD','PHMNS')
insert into [tableB]
values('BEANR','SGSIN')
insert into [tableB]
values('DEHAM','PTLIS')
insert into [tableB]
values('MXVER','USSSI')
----------------------------------------------
Does anybody know how this can be accomplished ? Any suggestions on using lookup in SQL would also be helpful.
Thanks.
September 3, 2013 at 10:41 am
You seem to have forgotten the ddl. Even if the tables were provided it really isn't very clear what you are trying to do here. Can you try to explain more clearly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2013 at 1:49 pm
Something like below. It's possible you only need one left join, depending on the specific data in your tables:
SELECT
CASE WHEN b_out.Continent_cd IS NOT NULL THEN 'OUT'
WHEN b_in.Continent_cd IS NOT NULL THEN 'IN'
ELSE 'NON' END
FROM dbo.tableA a
LEFT OUTER JOIN dbo.tableB b_out ON
a.Continent_cd IN ('EUR', 'MED') AND
b_out.POL_Location_cd = a.Location_cd
LEFT OUTER JOIN dbo.tableB b_in ON
a.Continent_cd IN ('EUR', 'MED') AND
b_in.PDL_Location_cd = a.Location_cd
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2013 at 2:51 am
Sean Lange (9/3/2013)
You seem to have forgotten the ddl. Even if the tables were provided it really isn't very clear what you are trying to do here. Can you try to explain more clearly?
Sorry Sean, I have added the DDLs now. Thanks.
September 4, 2013 at 7:13 am
pwalter83 (9/4/2013)
Sean Lange (9/3/2013)
You seem to have forgotten the ddl. Even if the tables were provided it really isn't very clear what you are trying to do here. Can you try to explain more clearly?Sorry Sean, I have added the DDLs now. Thanks.
No problem. It seems that Scott was able to figure out something. Does what he posted get you what you need? If not, a little explanation would go a long way. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 3:04 am
ScottPletcher (9/3/2013)
Something like below. It's possible you only need one left join, depending on the specific data in your tables:
SELECT
CASE WHEN b_out.Continent_cd IS NOT NULL THEN 'OUT'
WHEN b_in.Continent_cd IS NOT NULL THEN 'IN'
ELSE 'NON' END
FROM dbo.tableA a
LEFT OUTER JOIN dbo.tableB b_out ON
a.Continent_cd IN ('EUR', 'MED') AND
b_out.POL_Location_cd = a.Location_cd
LEFT OUTER JOIN dbo.tableB b_in ON
a.Continent_cd IN ('EUR', 'MED') AND
b_in.PDL_Location_cd = a.Location_cd
Thanks a lot, Scott, your code worked !
September 6, 2013 at 3:05 am
Sean Lange (9/4/2013)
pwalter83 (9/4/2013)
Sean Lange (9/3/2013)
You seem to have forgotten the ddl. Even if the tables were provided it really isn't very clear what you are trying to do here. Can you try to explain more clearly?Sorry Sean, I have added the DDLs now. Thanks.
No problem. It seems that Scott was able to figure out something. Does what he posted get you what you need? If not, a little explanation would go a long way. 🙂
I will try my best to be more detailed the next time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply