Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Stubert
Starting Member
10 Posts |
 Posted - 2011-04-12 : 07:29:55
|
Hi,When a run thisSELECT Allocation, Account, Posting_Date, Accounting_Document_Number, SAP_Amount, Text, CASE WHEN ([Text] <> NULL) THEN SUBSTRING([Text], 14, LEN([Text])) END AS Payment_CodeFROM RIC.CustAcct_SAP_Suspense_ExceptionsWHERE (Reconciliation_Period = '201103') AND (Account = '760156') AND (Posting_Date LIKE '2011-03%') AND (Allocation LIKE '20%') I get perfect results, with Payment_Code populated correctly. However, when I save it to a view, and then reference the view in another query (where I am doing some grouping), the column Payment_Code doesn't work. It just has nulls.Any suggestions? Can I group on a derived column in the same query?Cheers!! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-12 : 07:37:00
|
This may have to do with ANSI_NULLS being on/off in the view and being the other way when you query. Replace the "<>" with "IS NOT" and see if that makes any difference: SELECT Allocation, Account, Posting_Date, Accounting_Document_Number, SAP_Amount, Text, CASE WHEN ([Text] IS NOT NULL) THEN SUBSTRING([Text], 14, LEN([Text])) END AS Payment_CodeFROM RIC.CustAcct_SAP_Suspense_ExceptionsWHERE (Reconciliation_Period = '201103') AND (Account = '760156') AND (Posting_Date LIKE '2011-03%') AND (Allocation LIKE '20%') Also, the way you are comparing dates is not a safe or recommended approach - but you are not asking for a lecture, you are trying to solve a problem, so I will say nothing about that. |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-04-12 : 08:39:28
|
quote:
Originally posted by sunitabeck...Also, the way you are comparing dates is not a safe or recommended approach - but you are not asking for a lecture, you are trying to solve a problem, so I will say nothing about that.
Where is the fun in that !!   I'll say it...We suspect Posting_Date is a datetime. If that is the case, you should not use a 'like' text condition. If you want to get March 2011, there are many options. I personally like #1 because the datacolumn never has to convert to another data type.  1)Declare @PostMonth datetimeSet @PostMonth = convert(datetime,'2011-03-01')Select.....dateadd(mm,datediff(mm,0,Posting()),0) = @PostMonth... 2)Select.....Year(Posting_Date)=2011 and Month(Posting_Date)=3... 3) etc.Corey I Has Returned!! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-12 : 08:47:08
|
for performance reason, i would go for #33) Reconciliation_Period >= '2011-03-01'and Reconciliation_Period < '2011-04-01'
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-04-12 : 10:07:44
|
I tested... and was interested to see it does make a difference. But really only when there is an index on the column.Thanks for adding it though :Pquote:
Originally posted by khtan
for performance reason, i would go for #33) Reconciliation_Period >= '2011-03-01'and Reconciliation_Period < '2011-04-01'
KH[spoiler]Time is always against us[/spoiler]
CoreyI Has Returned!! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-12 : 10:21:45
|
yes. provided that there is index on that column. I prefer this way as if i would need to add an index there, i would not have to change my query to utilize it.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-04-12 : 10:58:27
|
| yeah, honestly I do both, but if I'm searching for a range of dates, I do #3 also. I guess I usually do #1 when i want to ignore times...CoreyI Has Returned!! |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-04-12 : 11:29:12
|
quote:
Originally posted by Seventhnight
yeah, honestly I do both, but if I'm searching for a range of dates, I do #3 also. I guess I usually do #1 when i want to ignore times...CoreyI Has Returned!!
I'd never apply a function to a column unless I absolutely needed to, so, I'd never use #1. Even if you added (or have) an index, sql could not take advantage of that index and you would have to change your code in order to do so. Thus, it's better to write sargable predicates. :) |
|
|
|
Stubert
Starting Member
10 Posts |
Posted - 2011-04-12 : 12:11:24
|
quote:
Where is the fun in that !!
hee hee, you guys and your lectures! The table is one I've inherited and the column is nchar(10) and I cannot change it. For the purposes of what I'm doing using LIKE works well enough, although I know its not good practice.Sunita, many thanks. You were correct re the ANSI_NULLS. Those pesky scamps have tripped me up before in my accountants adventures in SQL!!Its great to have this resource for newbies like myself. Cheers All. |
|
|
|
|
|
|
|
|
Inconsistent VIEW results