Translate to tsql 1

[kmclane]

I have an Access Query that I would like to run directly in Query analyzer. Here is my query:

UPDATE MonthVitalAuth iif(Left([MonthVitalAuth]![CardNo],1)="3", SET MonthVitalAuth.CardNo = Left([MonthVitalAuth]![CardNo],1) & "xxxxxxxxxxx" & Mid([MonthVitalAuth]![CardNo],12,4), SET MonthVitalAuth.CardNo = Left([MonthVitalAuth]![CardNo],1) & "xxxxxxxxxxx" & Mid([MonthVitalAuth]![CardNo],13,4));

I would like to know if this would run as is or if it needs to be changed. I haven't built the db yet, just trying to look ahead. Thanks.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[jbenson001]

You will have to change this.

There is no iif in tsql, you will need to use a case.
The Left and Mid(SubString in sql) functions will need to be changed also.

 

[kmclane]

Can I find this in the "Books on line" ?

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[jbenson001]

Absolutely.. That will be your best friend for a long time while learning SQL. I am always going back to it. Take a poke around and look for string functions and the Case statemtnt. If you have more questions, this site is great to get answers. Lots of good SQL people here..

 

[kmclane]

Thanks.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[SQLBill]

Another thing to remember...SQL Server doesn't have FORMS, so it won't understand what this is:

([MonthVitalAuth]![CardNo]

-SQLBill

Posting advice: FAQ481-4875

 

[kmclane]

Yes, I actually knew that. I wasn't sure about the iif and hadn't even thought about the string functions. Thanks again. Now I need to reload server 2003, SQL server and Reporting Services. Then Import all the data, about 40 GB's worth in 5-6 tables. Took me three days last time then I was told to erase it all because of the credit card numbers. Nobody thought about xing them out. Duh!!

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

OK here is mt SQL code, which is not working:

UPDATE [MonthPictureAuth].[dbo].[ARUMonth]
CASE 'Substring([CardNumber],1,1)'
When "3" Then SET [CardNumber]=SubString(CardNumber,1,1) + "xxxxxxxx" + Substring(CardNumber,12,4)
Else SET [CardNumber]=SubString(CardNumber,1,1) + "xxxxxxxxx" + Substring(CardNumber,13,4)

I get the error "Incorrect syntax near the keyword 'CASE'" when trying to validate. I'm obviously missing something. Can someone point out my error please?

Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[jbenson001]

Remove the quotes wournd the case statement. Also, SQL Server uses single quotes to denote a character string. So use When '3' not When "3

 

[kmclane]

I can't seem to get the case statement to work, but I can run three different queries that do what I need. I Have 5-6 tables I need to run these against, once a month to make sure Everything is truncated, is there a way to automate running a bunch of queries? I also think I need to put in a check for x's so I don't waste time on records that have already been processed. Thanks.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[jbenson001]

You can set up an automated job though enterpirse manager. You need to create a DTS package then you can schedule it to run when needed.

The idea of flagging processed rows is a good idea especially if you have lots of rows to deal with. I suggest using a bit column. Update it to 1 when processed, otherwise it is 0.

 

[kmclane]

I was thinking of just using my where clause to check Like this: Where Substring([cardnumber],1,1) = '3' AND Substring([cardnumber],1,2) <> 'x'

Would this work?

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[evaleah]

Since you say that you are running all seperate queries not instead of the case statement, yes that should work.

 

[kmclane]

Cool, so if I save all these separate queries to .sql files I can set them to run as a DTS package? Or do I need to string them all together in code somehow? Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[JamesLean]

FYI, you can re-write your original single query like this:

UPDATE monthpictureauth.dbo.arumonth
SET cardnumber = CASE
	WHEN cardnumber LIKE '3%' THEN '3xxxxxxxx' + SUBSTRING(cardnumber, 12, 4)
	ELSE LEFT(cardnumber, 1) + 'xxxxxxxxx' + SUBSTRING(cardnumber, 13, 4) END

--James

 

[kmclane]

James, can I include my exclusion in that so it skips rows that have already been done? Also I havent seen "END" in any of the examples in the books on line. Is that needed?
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[evaleah]

Yes, END is *definitely* needed.

Yes, you can include your exclusion.

Just saving this to a .sql file is not enough to run it. In order to put it into a DTS package that you can schedule you will need to copy and paste the text into an Execute SQL task in a DTS package that you create.

Unfortunately, explaining how to create a DTS package is a bit much for a forum like this. Look up DTS in Books Online and you should be able to get started there.

HTH
Eva

 

[JamesLean]

I take it rows that have already been done will be ones which contain an 'x'?

UPDATE ...
SET ...
WHERE cardnumber NOT LIKE '%x%'

The END keyword is needed to complete the CASE expression.

--James

 

[kmclane]

Thanks, I wonder why they left it out of the examples? Nevermind I am looking at UPDATE, not Case right now. Duh! Thanks again!

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg