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 12»»

Urgent Query Help Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 3:41 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
The below query has to give Y if it's equals to zero but it gives always zero. can you modify this one



UPDATE T1 SET [coumnname] = CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName] IS NOT NULL AND [colname2] <> 0 THEN 'Y' ELSE 'N' END
FROM tablename T1
LEFT JOIN tablename2 T2 ON (T1.[Application ID] = T2.[Application ID] AND T1.[ServerName] = T2.[ServerName]) WHERE T2.[Data Period] = 'September 2012'
Post #1376700
Posted Wednesday, October 24, 2012 3:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 901, Visits: 7,165
This one says if the tested column is NOT = 0, then it will update with a 'y'. Your post isn't really clear about what the actual results of the update are.



And then again, I might be wrong ...
David Webb
Post #1376702
Posted Wednesday, October 24, 2012 4:00 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: Today @ 5:29 PM
Points: 3,513, Visits: 7,565
There's not much we can do without DDL and sample data.
To audit the problem, you could do a simple select instead of the update.

Another thing to watch is the datatype. You can't expect to return 'Y' if the data type is int.

--This first line will return an error
--UPDATE tablename SET = 'N'


--Commenting the UPDATE to transform into SELECT
--UPDATE T1 SET
-- [coumnname] = CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName] IS NOT NULL AND [colname2] <> 0 THEN 'Y' ELSE 'N' END
SELECT T2.[Application ID], --It's recommended not to use spaces for column/object names
T2.[ServerName],
colname2
FROM tablename T1
LEFT JOIN tablename2 T2 ON (T1.[Application ID] = T2.[Application ID] AND T1.[ServerName] = T2.[ServerName])
WHERE T2.[Data Period] = 'September 2012'
--Uncomment to be sure there're records that comply
--AND T2.[Application ID] IS NOT NULL
--AND T2.[ServerName] IS NOT NULL
--AND [colname2] <> 0




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1376704
Posted Wednesday, October 24, 2012 4:02 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
Please check this below one more time ...i give clearly


UPDATE T1 SET [September 2012 Billing File] = CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName]
IS NOT NULL AND [TB Billed This Month] <> 0 THEN 'Y' ELSE 'N' END
FROM TableName1 T1
LEFT JOIN TableName2 T2 ON (T1.[Application ID] = T2.[Application ID]
AND T1.[ServerName] = T2.[ServerName]) WHERE T2.[Data Period] = 'September 2012'
Post #1376705
Posted Wednesday, October 24, 2012 4:10 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
Please check this below one more time ...i give clearly

It is taking the one first row it should be take entire rows.


UPDATE T1 SET [September 2012 Billing File] = CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName]
IS NOT NULL AND [TB Billed This Month] <> 0 THEN 'Y' ELSE 'N' END
FROM TableName1 T1
LEFT JOIN TableName2 T2 ON (T1.[Application ID] = T2.[Application ID]
AND T1.[ServerName] = T2.[ServerName]) WHERE T2.[Data Period] = 'September 2012'
Post #1376709
Posted Wednesday, October 24, 2012 4:11 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
Were trying to point out that since we do not have your tables or data we cannot tell you why the case is evaluating to N

Before running your update,
Run a version of the query as a select statement.
Are the data elements in your query evaluating as you suspect?
Is T2.Application ID Not Null? Is ServerName Not Null?
and does TB Billed This Month Not equal 0?
SELECT 
T2.[Application ID]
, T2.[ServerName]
,[TB Billed This Month]
,CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName] IS NOT NULL AND [TB Billed This Month] <> 0 THEN 'Y' ELSE 'N' END
FROM TableName1 T1
LEFT JOIN TableName2 T2 ON (T1.[Application ID] = T2.[Application ID]
AND T1.[ServerName] = T2.[ServerName]) WHERE T2.[Data Period] = 'September 2012'

Post #1376710
Posted Wednesday, October 24, 2012 4:13 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 901, Visits: 7,165
OK, this code:

UPDATE T1 SET [September 2012 Billing File] = CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName] 
IS NOT NULL AND [TB Billed This Month] <> 0 THEN 'Y' ELSE 'N' END
FROM TableName1 T1
LEFT JOIN TableName2 T2 ON (T1.[Application ID] = T2.[Application ID]
AND T1.[ServerName] = T2.[ServerName]) WHERE T2.[Data Period] = 'September 2012'

will set [September 2012 Billing File] = 'y' if [TB Billed This Month] IS NOT = 0. If you want it to set [September 2012 Billing File] = 'y' if [TB Billed This Month] = 0 the the '<> 0' in your code needs to change to '= 0'.




And then again, I might be wrong ...
David Webb
Post #1376712
Posted Wednesday, October 24, 2012 4:54 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
2 possible things
The query is returning multiple rows, and the 'N' is being returned because of the last row.
OR/AND
Your performing a Left Join,
But you referencing T2.[Date Period] in your Where clause effectively makings it an inner join.
Unless the the value for Application ID and Server Name are NULL in the T2 table It will never evaluate to 'Y'

This is why we need sample data and DDL which would allow us to see these things and provide a better answer.

SELECT 
T2.[Application ID]
, T2.[ServerName]
,[TB Billed This Month]
,CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName] IS NOT NULL AND [TB Billed This Month] <> 0 THEN 'Y' ELSE 'N' END
FROM TableName1 T1
LEFT JOIN TableName2 T2 ON (T1.[Application ID] = T2.[Application ID]
AND T1.[ServerName] = T2.[ServerName]) WHERE T2.[Data Period] = 'September 2012'

Post #1376725
Posted Wednesday, October 24, 2012 5:18 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
Awesome......I use inner join instead of Left join
Now its working fyn...Thank you very much
Post #1376731
Posted Wednesday, October 24, 2012 8:55 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..Once again thanks for all your support. but my lead ask me to modify the query like below. I tried it but it giving error. Could any one modify it plzzz

Msg 8120, Level 16, State 1, Line 1
Column 'AEG_DB.dbo.MRO_TBL_EMCData.Application ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


select [September 2012 Billing File] = CASE WHEN T2.[Application ID] IS NOT NULL AND T2.[ServerName]
IS NOT NULL AND Sum([Billed This Month]) <> 0 THEN 'Y' ELSE 'N' END
FROM [AEG_DB].[dbo].[TBL_Work] T1
LEFT JOIN [TBL_Data] T2 ON (T1.[Application ID] = T2.[Application ID] AND T1.[ServerName] = T2.[ServerName])
WHERE T2.[Data Period] = 'September 2012'
Post #1376756
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse