Query not working... - 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 not working...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-20 : 10:53:48

I tried this but nothing is returned because the mvt_loc field is a varchar (3) field with a space entered in front of the value like this' S2' (A space is in front of the S)

Below is the create table and some values to go into the table.
Thanks!

What am I doing wrong?


SELECT mvt_cdt, COUNT(cossn) AS pendddscnt, mvt_loc
FROM test
WHERE mvt_typ = 'R' 
AND mvt_loc IN (' R*', ' S*', ' V*')
GROUP BY mvt_cdt, mvt_loc



CREATE TABLE [dbo].[Test](
	[COSSN] [char](6) NOT NULL,
	[MVT_TYP] [char](1) NOT NULL,
	[MVT_LOC] [char](3) NOT NULL,
	[MVT_DEST] [varchar](3) NOT NULL,
	[MVT_CDT] [datetime] NULL
) ON [PRIMARY]

insert into test
select '458962', 'R', ' S2', '  ', '3/15/1993' union all

select '454442', 'R', ' V5', '  ', '9/13/1996' union all

select '569962', 'T', ' V1', 'C19', '12/21/1999' union all

select '895962', 'T', ' 03', 'S24', '7/10/2002' union all

select '999962', 'T', ' S2', 'R42', '2/3/2003' union all

select '452262', 'R', ' S2', '   ', '1/22/2004' union all

select '458962', 'R', ' V3', '   ', '5/5/2004' union all

select '458962', 'R', ' V2', '858', '6/8/2004' union all

select '458962', 'T', ' S7', 'S24', '7/19/2004' union all

select '458962', 'R', ' 04', '   ', '4/8/2010'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-20 : 12:08:41

Since you didn't show any expected output, I'll have to guess that you are trying to use the * as a wildcard character. If that is the case, then maybe this will help.

SELECT mvt_cdt, COUNT(cossn) AS pendddscnt, mvt_loc
FROM test
WHERE mvt_typ = 'R' 
AND 
(
    mvt_loc LIKE ' R%'
    OR  mvt_loc LIKE ' S%'
    OR mvt_loc LIKE ' V%'
)
GROUP BY mvt_cdt, mvt_loc

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-20 : 14:38:32

Thanks so much Lamprey this is exactly what I wanted!

Sorry for not adding the results


Cossn     MVT_Typ     MVT_Loc  pendddscnt    MVT_CDT   
485692      R           SO       1            3/15/93		
454442      R           V5       1            9/13/96
569962      T           V1       1           12/21/99

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources