Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Wednesday, September 11, 2013 4:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240


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

Post #1493935
Posted Wednesday, September 11, 2013 6:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 3,609, Visits: 5,220
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1493963
Posted Wednesday, September 11, 2013 11:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1493998
Posted Thursday, September 12, 2013 8:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1494192
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse