Query Help

Question

Post reply

Query Help

DBA12345

Right there with Babe

Points: 742
More actions
September 11, 2013 at 4:06 pm

#279693

Hi I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql
App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB
OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag
App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null
App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE
Let me know fi you need addiitional information.
Thanks
Aswin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

DBA12345 (9/11/2013)
Let me know fi you need addiitional information.

DDL and sample data in a consumable form would be nice.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Please provide the DDL scripts for the table creation, ddl script to insert some dummy data and desired output...

Please follow the link in my signature and you will find answers to your question quickly 😛

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

DBA12345 (9/11/2013)
Hi I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql
App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB
OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag
App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null
App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE
Let me know fi you need addiitional information.
Thanks
Aswin

I am not sure which is correct, your explanation or your desired output. The stated rules, the sample data and the output you said you wanted don't match up.

If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.

In your output you have App1 as NULL but your rule says it should be "True".

If the Application has more the one AIX and Adder names are diferent then the flag would be null.

App2 has 2 rows for AIX but the Adder values are the same. According to the rules shouldn't this also be "True".

I cobbled together something resembling ddl and sample data in a format you should post with in the future.

create table #SomeTable

(

AppName char(4),

OS varchar(10),

Adder char(3)

)

insert #SomeTable

select 'App1', 'Windows', Null union all

select 'App1', 'Linux', 'UDB' union all

select 'App1', 'AIX ', 'UDB' union all

select 'App1', 'Linux', 'Sql' union all

select 'App2', 'AIX', 'UDB' union all

select 'App2', 'Windows', 'UDB' union all

select 'App2', 'Linux', 'UDB' union all

select 'App2', 'AIX', 'UDB'

Now to setup some data that meets the requirements of rule #2 I added some more data.

insert #SomeTable

select 'App3', 'AIX', 'UDB' union all

select 'App3', 'Windows', 'UDB' union all

select 'App3', 'Linux', 'UDB' union all

select 'App3', 'AIX', 'xxx'

OK. Now we have something to work with. If I understand your requirements I think you want something along these lines.

select *, case MyCount when 1 then 'True' end

from #SomeTable s

cross apply (select COUNT(distinct Adder) as MyCount from #SomeTable s2 where s2.AppName = s.AppName and s2.OS = 'AIX') x

_______________________________________________________________

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/