mia

Forum Replies Created

Viewing 15 posts - 16 through 30 (of 49 total)

RE: SELECT FROM a WHERE {end bit of field} not in B..?

Ollie,
Had a look again, and I think the following should give you what you need. I tested it briefly using the example data you provided & it produces the...

November 27, 2003 at 7:00 am

#483919
RE: SELECT FROM a WHERE {end bit of field} not in B..?
Maybe something like:
```
select * from Table2 

where substring(email, charindex('@', email) + 1, len(email)) not in

(select domain from Table1)
```
Assuming of course that email is not null and...
November 27, 2003 at 5:16 am

#483897
RE: relationships

I think what's wrong is that the nested select will return NULL for any rows in t1 where field doesn't appear in t2. This is because you are selecting...

November 27, 2003 at 4:30 am

#483892
RE: FOR XML

The long string returned is an XML document, its just not pretty-printed. You could select Tools > Options > Results in Query Analyser, and change the default results target...

November 27, 2003 at 2:43 am

#483876
RE: OPEN XML

Also, I noticed that some of your FIELD values will be truncated as you allow for varchar(20) in the openxml statement, but some of the FIELD values in the XML...

November 27, 2003 at 2:12 am

#483874
RE: OPEN XML
bodozer,
Try replacing your openxml with the following:
```
select *

from OPENXML(@iDocumentHandle,'/REQUEST/LETTER/FIELD',2)

WITH (FIELD varchar(20) '.',

NAME varchar(20) '@NAME')
```
Replacing '../FIELD' with '.' works for me using your example. Hope that...
November 27, 2003 at 2:02 am

#483873
RE: Remove alpha characters

Don't know if you've seen it, but SQL Books online has good sections on each of the bitwise operators AND (&), NOT (~), OR (|) and exclusive OR (^), which...

November 26, 2003 at 9:03 am

#483774
RE: OPENXML and NULLs
Just realised this! Rather than the long-winded case statement, you might be able to use the nullif() function. Sorry I didn't mention this sooner!
i.e. instead of:
```
 
```
November 26, 2003 at 8:08 am

#483751

RE: OPENXML and NULLs

The empty StartDate tag equates to an empty string. Inserting an empty string value into a datetime column, even when that column accepts nulls, will insert the value '1900-01-01'...

November 26, 2003 at 7:47 am

#483739

RE: OPENXML and NULLs

To get a null datetime value, you need to omit the tag completely or specify the null value explicitly. For example, you could use a case statement:

November 26, 2003 at 7:28 am

#483731

RE: E2 is eval. 1st in

Could there be something about operator precedence that is making E2 evaluate before E1? The expressions should evaluate left to right unless operator precedence dictates otherwise.

Cheers,

mia

Life moves pretty fast....

November 26, 2003 at 3:54 am

#483686

RE: ALTER TABLE with several ALTER COLUMN

NB - If you're adding rather than altering columns, you can do it in one go like this:



alter TABLE [dbo].[ARCHIVE_TABLE]

add [Tablename] varchar(255),

[sColumn] varchar(300)

go

Sorry for 2 posts...

November 26, 2003 at 3:14 am

#483682

RE: ALTER TABLE with several ALTER COLUMN

Don't think you can alter multiple columns in the same alter table statement - think you need to do it like this:



alter table [dbo].[ARCHIVE_TABLE]

alter column [TableName]...

November 26, 2003 at 3:06 am

#483679

RE: Trapping SP Input Param Error

hadn't noticed the name thing...

...now I really know

Now I have an abba song in my head, which is no bad thing I guess!

Cheers,

mia

November 25, 2003 at 10:43 am

#483586

RE: hard time with XML explicit

Glad to be of help Patrick - I only knew where to look for the problem because I've had exactly the same error myself when I started using FOR XML...

November 25, 2003 at 10:28 am

#483582

Viewing 15 posts - 16 through 30 (of 49 total)