Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Unanswered: where condition oddity SQL 2000

    I have a saved query that has been running flawlessly or sometime now that has suddenly broken. Here is the scenario.

    This query

    SELECT 'E' as DataType,COUNT(*) as RC
    FROM tblPrimary p
    join tblrecords r on r.record_id = p.record_id
    WHERE r.pttype = 2 AND p.servcode = 2

    on SERVER A returns error:

    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value 'C' to a column of data type int.

    The same query on SERVER B returns records & no error.

    The only way to get the SERVER A query to run is the following:

    SELECT 'E' as DataType,COUNT(*) as RC
    FROM tblPrimary p
    join tblrecords r on r.record_id = p.record_id
    WHERE r.pttype = 2 AND p.servcode = '2'

    Both columns (pttype and servcode) are of a Char(1) NUllable datatype.
    Both servers properties are the same.
    I looked in the table for a 'C' in the servcode column - no such value(1-7 or blank).

    The pttype field will always have a numeric value (yeah, I know >:|) but the servcode will have a numeric value or a blank. Why would one Char(1) field in the where condition accept a non quoted number criteria while the other will not and on one server only?

    I am baffled. Obviously something changed, I 'm just not quite sure where to look. Any helpful hints would be greatly appreciated!

    Thanks!
    Last edited by rmetz; 11-09-09 at 13:22. Reason: Incorrect query string.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    got ddl?

    post that please
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    39
    CREATE TABLE [tblPrimary] (
    [record_id] [uniqueidentifier] NOT NULL ,
    [bdat] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ethn] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [adms] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [admt] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [adat] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [dxp] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [dxa] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [dxe1] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXP_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXRV1] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXRV2] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXRV3] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE1_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE2] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE2_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE3] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE3_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE4] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE4_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE5] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE5_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE6] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE6_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE7] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE7_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE8] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE8_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE9] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE9_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE10] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE10_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE11] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE11_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE12] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DXE12_POA] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [pina] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [pinb] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [pinc] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [prp] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [prpmod1] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [prpmod2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [prpmod3] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [prpmod4] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [prpd] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ddat] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ptstatus] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [sopid] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [soptype] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [sopid2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [soptype2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [provid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [tc] [money] NULL ,
    [lvdays] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [billtype] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [servcode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [mrn] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [stperiodf] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [stperiodt] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ccode1] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ccode2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ccode3] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [certnum] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ecid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_tblPrimary_record_id] PRIMARY KEY CLUSTERED
    (
    [record_id]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [FK_tblPrimary_tblRecords] FOREIGN KEY
    (
    [record_id]
    ) REFERENCES [tblRecords] (
    [record_id]
    )
    ) ON [PRIMARY]

    CREATE TABLE [tblRecords] (
    [record_id] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_tblRecords_record_id] DEFAULT (newid()),
    [batch_id] [uniqueidentifier] NOT NULL ,
    [hnum] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [pcontrol] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [pttype] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [pos] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [valid_status] [bit] NOT NULL CONSTRAINT [DF_tblRecords_valid_status] DEFAULT (1),
    CONSTRAINT [tblRecords_PK] PRIMARY KEY CLUSTERED
    (
    [record_id]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [tblBatches_tblRecords_FK1] FOREIGN KEY
    (
    [batch_id]
    ) REFERENCES [tblBatches] (
    [batch_id]
    )
    ) ON [PRIMARY]

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT p.servcode, p.*
       FROM tblPrimary p
       WHERE  0 = IsNumeric(p.servcode)
         OR   0 = IsNumeric(p.record_id)
    
    SELECT r.pttype, r.*
       FROM tblrecords AS r
       WHERE  0 = IsNumeric(r.pttype)
         OR   0 = IsNumeric(r.record_id)
    -PatP
    Last edited by Pat Phelan; 11-09-09 at 15:10. Reason: I decided to be more thorough
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    holy denormalized table batman

    Well, it it runs when it's written correctly....you should fix it

    WHY it bombs?

    RUN THIS AND POST WHAT YOU GET BACK

    Code:
    SELECT servcode, COUNT(*) AS Occurs
    FROM tblPrimary
    GROUP BY servcode
    
    SELECT pttype, COUNT(*) AS Occurs
    FROM tblrecords
    GROUP BY pttype
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2003
    Posts
    39
    Found the issue. There are Alpha (K, C, A etc...) characters in the data. Someone submitted incorrect data (in an untimely fashion) that I was unaware of. Sorry for the skull sweat guys! I appreciate your time! You were very helpful!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just curious, but how did you discover the incorrect data? Knowing what helped you this time might help us to help someone else in the future.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2003
    Posts
    39
    Sure. Well, our data analysts informed me of a test file someone loaded into our system (we collect data through a web app). There should have been only a single quarters data in the table. However, the test data had a different quarter flag. The query was filtering on this flag originally because sometimes there may be more than one quarters worth of data in other situations but not "test data". Once I found that out I pulled the qtr/year filter off and saw the bad data. We removed that junk and all is well. You guys were on target in where you were looking. I was looking there too but got confused because I wasn't expecting anything other that the single qtr. I was getting correct values when I pulled distinct with the '' around the 2 but the "hidden" data was causing the choke.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •