Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Question Unanswered: view returning 0 when selecting, but fine in QA

    I have created the view below, when I highlight the select statement in QA it works fine, but when I try to access the view I get back 0 records!
    ie:
    select * from Email_All

    It believe its something to do with the last not in section:
    EmailsAddresses.Email not in (
    if I comment it out everything works fine!

    does not make any sense...anyone have any clues?

    they are all views except the EmailSubscriptions table.

    -------------------------------------------
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER VIEW [dbo].[Email_All]
    AS




    select * from
    (
    --SHELBY
    select * from hcf..HM_EmailAddressShelby
    union all
    --KMS
    select * from hcf..HM_EmailAddressKmai
    union all
    --EMAIL SUBSCRIPTIONS
    select * from hcf..HM_EmailAddressWeb
    where id in (select distinct id from hcf.dbo.EmailSubscriptions where List = 'PeriodicEmailsFromGreg' and status = 1)
    ) as EmailsAddresses
    where
    ltrim(rtrim(isnull(EmailsAddresses.Email,''))) <> ''
    and
    EmailsAddresses.Email not in (
    select Email from hcf..ShelbyIdEmailSkip --OFF list SHELBY
    union
    select Email from hcf..KmaiIdEmailSkip --OFF list KMAI
    union
    select Email from hcf..EmailSubscriptions where List = 'PeriodicEmailsFromGreg' and status = 0 --OFF list PERIODIC FROM GREG
    )




    GO

    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't see any problems in your SQL, except that the use of "select *" is considered sloppy and error-prone. The use of nested views can also lead to buggy code that is hard to optimize for efficiency.
    If ShelbyIdEmailSkip and KmaiIdEmailSkip are views, I suggest you check to make sure they are operating correctly. Try commenting each of the views in turn and see if any records are returned by your code.
    How complex are the EmailSkip views? Can you post one or both?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Wink Good Eye Blindman

    The problem was with the hcf..KmaiIdEmailSkip view, I had made a change in the join and it was linking incorrectly. I corrected that and the problem is resolved. Thanks for the help!

    The select * is because these are identically laid out views pulling from multiple systems to standardising the data to its easily joined.


    If I can ask, I am not quite sure why the join was failing, is there a limit on the number of records for an IN() clause? The matching values were there but there was also a bunch of other junk that would not have matched.

    Here is the table with the email address, the address comes out of the NOTE column and I was not correctly filtering the TYPE column so I was getting TONS of rows!
    ----------------------------------------------------------
    CREATE TABLE [dbo].[A06desc](
    [AcctNbr] [int] NOT NULL,
    [Type] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Occur] [int] NOT NULL,
    [Note] [char](70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AddDate] [datetime] NULL,
    [ChgDate] [datetime] NULL,
    [NextActivityDate] [datetime] NULL,
    [AddUserId] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ChgUserId] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRIMARY KEY CLUSTERED
    (
    [AcctNbr] ASC,
    [Type] ASC,
    [Occur] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Posting Permissions

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