Search results for query: *

bborrisov: Grr! My code example is right but I miss typed the statment after the code - sorry for the confusion there! gmmastros: adding the min() solves the problem - thanks for the tip! Now, if I understand what's going on in this code... the select statement is checking oC.Id and oC.Fld2 to...

...FROM @orders_current oC FULL JOIN @orders_historical oH ON oC.Id = oH.Id RESULT: 1 aaaaaaaa -- Record from oC 2 bbbbbbbbb -- Record from oC 3 ccccccccc -- Record from oH 4 aaaaaaaa -- Record from oH * It is this last record I need to get rid of because it is in cO as well.

The select statement I'm using to create my view has about 4 joins to get to various files that I need, however, there are two main files that I am pulling data from. One is the orders_current [ oC ] and the other is orders_historical [ oH ]. Here's what I need to do: If I find a record in [...

I'm not sure how they are bringing the data in... that's part of the problem. We're going to schedule another meeting to talk with them about this and other issues. Thanks again for all your help, Steve

Unfortunately, they want option 1 So, I guess that confirms my suspicion that I now have to place the ball back in their court. Thanks for the help!

Thanks for the responses - sorry my reply is so slow! gmmastros - I like that Idea, I think I'll implement the Nullif code. It will help shorten my current t-sql a bit and teach me a new trick at the same time! :0) Alex - from what I've seen, you are correct, the datetime format always gives...

Thanks for taking a look at my question. I'm sending data to corporate for analysis by their programmers, However, this past Friday I received the following email (emphasis supplied by me). Here's the problem: The code I'm using was nicely provided by gmmastros over on Link: this thread...

Wow! That was quick and simple! ... sad. I've been struggling with this all morning. *sigh* but my ordeal is now over. Thanks a million! [have a star]

I should also mention that removing the wildcard character (in blue) does not change the results. CASE WHEN LEFT(C6B9CD, 2) LIKE '¿·' THEN REPLACE(C6B9CD, '¿·', '') WHEN RIGHT(C6B9CD, 1) LIKE '}' THEN REPLACE(C6B9CD, '}', '') ELSE C6B9CD END AS SHIPTO_KEY

...affected. Actually, I should say that I have it 90% working, but there are some records with a trailing curly bracket ( } ) that will not leave... *sigh* Ok, here's a sample of the data I'm working with, and the code I'm using. What do I need to change in order to make this work? SAMPLE...

Try this... CAST(AmountField AS DECIMAL(10,2)) AS AMOUNT or CAST(AmountField AS NUMERIC(10,2)) AS AMOUNT I think that should do what you're looking for. Just change the (10,2) to whatever precision you need. Cheers!

Thanks for the help guys! I went through and found several gotchas, once I cleaned those up and CAST some of the fields to the required lengths the query works like it should. ps - I really do apologize for such a long code post that's not normally my style, but it appears to have helped in...

...TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ORDER_DATA_C') DROP VIEW ORDER_DATA_C; GO CREATE VIEW ORDER_DATA_C AS SELECT /* OPEN ORDERS */ 'DUBUQUE' AS BU_KEY ,CASE WHEN (MBC6.C6ACDT = '0' OR MBC6.C6ACDT IS NULL) THEN '0' WHEN MBC6.C6ACDT <> '0' THEN...

...The interesting thing is that if I create the views (current / historical) seperatly the query works without error???? The error I get is this: [query used] select top 500 * from order_data [error returned] Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

...again for the testing code and the help, Steve [QUERY ONE] DBCC FREEPROCCACHE WITH NO_INFOMSGS DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS select a.* from tester a inner join tester2 b on left(a.val, 1) = b.val [w/o index] 18 seconds to select 1199992 rows select (0%) <-- Parallelism (9%)...

Hmmm very interesting... thanks for the test data, let me play with it for an evening and see what I can come up with. I appreciate the help.

Thanks Alex, This is actually going to be used in a view, more specifically on a zipcode column on a join in a view. Confused yet... :0) Here's the code I'm using LEFT JOIN ZIPFILE ZIPF ON MBAL.ALCPTX = ZIPF.CITY AND LEFT(MBAL.ALCVCD, 5) = ZIPF.ZIP5 The column being trimmed to 5...

I'm working with a smallish recordset of just over 2 million records. In order to select the correct information, one of the select statements needs only the 5 leftmost characters. Currently I'm using LEFT(col3, 5) Does anyone have any advice as to which of the three methods would be faster...

Oops! I was playing with your code to see what would happen if I took out one of the convert's. the correct code should read... ,CASE WHEN MBF9.FEGHNB = '0' THEN '0' WHEN MBF9.FEGHNB <> '0' THEN CONVERT(VarChar(10), Convert(DateTime, Convert(VarChar(8), MBF9.FEGHNB + 19000000))...

You are quite correct, I found 3972 items where the original row value was '0'. I used the following code to fix the problem, and hopefully this 'help request' is put to bed. ;0) CREATE VIEW INVOICE_DATA AS SELECT 'LOCATION' AS BU_KEY ,CASE WHEN MBF9.FEGHNB = '0' THEN '0' WHEN...