query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-01-17 : 06:37:47

The following query

SELECT
distinct(SELECT LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) AS V2
FROM
LL D WITH(NOLOCK), CL E WITH(NOLOCK)
WHERE
(D.ND_CODE IS NOT NULL AND LEN(D.ND_CODE) > SPACE(0))
AND LTRIM(RTRIM(D.ND_CODE)) = E.CODE AND D.NO = T.NO

AND LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) <>' '
) AS V2
FROM TABLE1 T

is giving
NULL
A
B
C
D
...

I want to prevent the NULL from the result and the output should be
A
B
C
D
...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-17 : 07:08:44

At first site I would bet that this query isn't giving any results.
I will have a closer look but the next time you could be so polite to post a more readable query?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-17 : 07:16:45

[code]SELECT distinct
(SELECT
LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) AS V2
FROM LL D WITH(NOLOCK), CL E WITH(NOLOCK)
WHERE (D.ND_CODE IS NOT NULL AND LEN(D.ND_CODE) > SPACE(0))
AND LTRIM(RTRIM(D.ND_CODE)) = E.CODE
AND D.NO = T.NO
AND ISNULL(LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)),' ') <>' '
) AS V2
FROM TABLE1 T[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-17 : 07:18:08

and this is trash: LEN(D.ND_CODE) > SPACE(0) and I don't know what you are trying to do...

No, you're never too old to Yak'n'Roll if you're too young to die.

gagani
Posting Yak Master

112 Posts

Posted - 2012-01-17 : 07:38:37

SORRY, I didnt give properly
SELECT
distinct(SELECT LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) AS V2
FROM
LL D WITH(NOLOCK), CL E WITH(NOLOCK)
WHERE

(D.NV_2ND_CODE IS NOT NULL)

AND LTRIM(RTRIM(D.NV_2ND_CODE)) = E.CODE AND D.NO = T.NO

AND LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) <>' '

) AS V2, T.V1
FROM
(
SELECT

LTRIM(RTRIM(B.FIRST_NAME))+' '+ LTRIM(RTRIM(B.LAST_NAME)) AS V1
FROM
LL A WITH(NOLOCK), CL B WITH(NOLOCK)
WHERE

A.NV_CODE = B.CODE
AND
LTRIM(RTRIM(B.FIRST_NAME))+' '+LTRIM(RTRIM(B.LAST_NAME)) <>' '
AND
(NV_CODE IS NOT NULL)
AND
A._DATE >='2011-11-01 00:00:00' AND A._DATE<='2011-11-30 23:59:59'
) T

I tried your option, but still resulting in the same.

gagani
Posting Yak Master

112 Posts

Posted - 2012-01-17 : 07:40:09

it's working with the following, but i dont think its the right way of writing

SELECT
distinct(SELECT LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) AS V2
FROM
LL D WITH(NOLOCK), CL E WITH(NOLOCK)
WHERE

(D.NV_2ND_CODE IS NOT NULL)

AND LTRIM(RTRIM(D.NV_2ND_CODE)) = E.CODE AND D.NO = T.NO

AND LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) <>' '

) AS V2, T.V1
FROM
(
SELECT

LTRIM(RTRIM(B.FIRST_NAME))+' '+ LTRIM(RTRIM(B.LAST_NAME)) AS V1
FROM
LL A WITH(NOLOCK), CL B WITH(NOLOCK)
WHERE

A.NV_CODE = B.CODE
AND
LTRIM(RTRIM(B.FIRST_NAME))+' '+LTRIM(RTRIM(B.LAST_NAME)) <>' '
AND
(NV_CODE IS NOT NULL)
AND
A._DATE >='2011-11-01 00:00:00' AND A._DATE<='2011-11-30 23:59:59'
) T where

(SELECT LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) AS V2
FROM
LL D WITH(NOLOCK), CL E WITH(NOLOCK)
WHERE

(D.NV_2ND_CODE IS NOT NULL)

AND LTRIM(RTRIM(D.NV_2ND_CODE)) = E.CODE AND D.NO = T.NO

AND LTRIM(RTRIM(E.FIRST_NAME))+ ' '+ LTRIM(RTRIM(E.LAST_NAME)) <>' '

) is not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources