Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2012
    Posts
    15

    Unanswered: Finding an occurrence that does not exist

    Hi everyone,
    In the table you can create below, each set of order number records should contain one record with the OrderNoIndicator field set to 1. i.e. OrderNo 7032357. Is there a way of finding(without using the count function) sets of records that don’t contain an OrderNoIndicator set to 1 i.e. OrderNo 7088650 all the OrderNoIndicator fields are set to 2

    Thanks in advance.

    use LEIS
    If OBJECT_ID ('dbo.temp') IS NOT NULL
    DROP TABLE dbo.temp
    GO
    CREATE TABLE dbo.temp
    ( OrderNo bigint not null,
    OrderNoindicator tinyint not null
    );

    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7032357, 1 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7032357, 2 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7032357, 2 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7034826, 1 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7034826, 2 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7088650, 2 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7088650, 2 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7088650, 2 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7088650, 2 )
    INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
    VALUES (7088650, 2 )

    SELECT * FROM dbo.temp

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrew a View Post
    Is there a way of finding(without using the count function) sets of records that don’t contain an OrderNoIndicator set to 1
    Code:
    SELECT OrderNo
      FROM dbo.temp
    GROUP
        BY OrderNo
    HAVING COUNT(CASE WHEN OrderNoindicator=1 THEN 'uh oh' END) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by r937 View Post
    Code:
    SELECT OrderNo
      FROM dbo.temp
    GROUP
        BY OrderNo
    HAVING COUNT(CASE WHEN OrderNoindicator=1 THEN 'uh oh' END) = 0
    Rudy, Rudy, you ignored a crucial precondition in the specs:
    Is there a way of finding(without using the count function) sets of records that don’t contain an OrderNoIndicator set to 1
    A workaround this precondition:
    Code:
    SELECT OrderNo
      FROM dbo.temp
    GROUP
        BY OrderNo
    HAVING SUM(CASE WHEN OrderNoindicator=1 THEN 1 ELSE 0 END) = 0
    Andrew, why is "without using the count function" important?
    Is your client still working with SQL Server 1845 that doesn't support the COUNT function? Did someone challenged you that you couldn't solve this without using the COUNT function? ...
    I'm really curious about the rationale behind that precondition.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Andrew,

    You can also try:

    Code:
    SELECT OrderNo FROM dbo.temp
    where OrderNoindicator > 0
    group by OrderNo
    having MIN(OrderNoindicator) > 1
    Hope this helps.

  5. #5
    Join Date
    Mar 2012
    Posts
    15
    Thank you all for your input so far. I'm new to SQL and having problems using the Count function. I have to fit the above into another query which goes a bit like this:


    CREATE
    view
    [dbo].[temp]
    as
    select
    datepart(month, convert(date,line05.EpisodeEndDate,112)) Month
    , line01.CDSTypeCode --- Mandatory
    , case
    when line01.CDSTypeCode in ('120','130','140') then 'IP'
    else 'Invalid'
    end CDS_Type
    , line01.CDSUpdateType --- Conditional
    , case
    when line01.CDSUpdateType IN ('1','9')
    then 'Valid'
    when line01.CDSUniqueIdentifier = ''
    OR line01.CDSUniqueIdentifier IS null
    then 'Blank'
    else 'Invalid'
    end CDSUpdateCheck
    , line01.CDSUniqueIdentifier
    ------------------------------------------------------------------
    -------------------------------------------------------------------------
    ----Big snip of 30+ more case statments but you should get the idea
    ------------------------------------------------------------------------
    --------------------------------------------------------------------
    FROM
    LEIS.dbo.orderitems line01


    left join
    LEIS.dbo.wrkCDSAuditLine05 line05
    on
    line01.OrderID = line05.OrderID


    I dont seem to be able to add the Count function into the above code without having to add all the included columns into a GROUP BY at the bottom

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrew a View Post
    I dont seem to be able to add the Count function into the above code without having to add all the included columns into a GROUP BY at the bottom
    COUNT is an aggregate function

    you ~gots~ to use a GROUP BY clause if there are ~any~ non-aggregate expressions in your SELECT clause (e.g. line01.CDSTypeCode, line01.CDSUpdateType...)

    however, you might consider doing the grouping in a subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    with CTE as
    (
        SELECT OrderNo FROM LEIS.dbo.orderitems
        where OrderNoindicator > 0
        group by OrderNo
        having MIN(OrderNoindicator) > 1
    )
    
    select 
        datepart(month, convert(date,line05.EpisodeEndDate,112)) Month,
        line01.CDSTypeCode,
        case 
            when line01.CDSTypeCode in ('120','130','140') 
                then 'IP' 
            else 'Invalid'
        end CDS_Type,
        line01.CDSUpdateType --- Conditional
        case 
            when line01.CDSUpdateType IN ('1','9') 
                then 'Valid'
            when line01.CDSUniqueIdentifier = '' OR 
                 line01.CDSUniqueIdentifier IS null
                then 'Blank'
            else 'Invalid'
        end CDSUpdateCheck,
        line01.CDSUniqueIdentifier
    FROM CTE
    JOIN LEIS.dbo.orderitems line01 on line01.OrderNo = CTE.OrderNo
    left join LEIS.dbo.wrkCDSAuditLine05 line05 
        on line01.OrderID = line05.OrderID
    Hope this helps.

  8. #8
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by r937 View Post
    COUNT is an aggregate function

    you ~gots~ to use a GROUP BY clause if there are ~any~ non-aggregate expressions in your SELECT clause (e.g. line01.CDSTypeCode, line01.CDSUpdateType...)

    however, you might consider doing the grouping in a subquery
    Yes I (Eventually) have come(with all your help thank you) to that conclusion. I just thought I'd cast the net as in my expereince there are tricks that aren't always in the manuals or scnarios where things work when they should'nt. You've come up with some interersting ideas which all helps in the learning process.

    Now I just need to learn how to group min a sub query.

    Cheers

  9. #9
    Join Date
    Mar 2012
    Posts
    15
    Quote Originally Posted by imex View Post
    Try something like this:

    [CODE]with CTE as
    (
    SELECT OrderNo FROM LEIS.dbo.orderitems
    where OrderNoindicator > 0
    group by OrderNo
    having MIN(OrderNoindicator) > 1
    )

    select
    datepart(month, convert(date,line05.EpisodeEndDate,112)) Month,
    line01.CDSTypeCode,
    case
    when line01.CDSTypeCode in ('120','130','140')
    <snip>
    Hope this helps.

    The select statment works on its own, but I need it to be more along the lines of the rest of code:

    USE LEIS
    SELECT *
    ,CASE
    WHEN EXISTS
    (
    SELECT OrderNo FROM dbo.temp
    WHERE OrderNoindicator > 0
    GROUP BY OrderNo
    HAVING MIN(OrderNoindicator) > 1 )
    THEN 'INVALID'
    ELSE 'VALID'
    END LEISCHECK
    FROM dbo.temp

    It doesnt work when put in a case statement. I'm tearing my hair out here, it seems such a simlpe operation to perform (it would've taken me 2 minuets to do this with Filemaker) is all SQL this difficult? Thank you for your help it may not be working but I think I'm learning something.
    Last edited by andrew a; 05-16-12 at 10:42.

  10. #10
    Join Date
    Apr 2012
    Posts
    213
    Try something like that:

    Code:
    with CTE as
    (
        SELECT OrderNo FROM dbo.temp
        WHERE OrderNoindicator > 0
        GROUP BY OrderNo
        HAVING MIN(OrderNoindicator) > 1
    )
    
    SELECT 
        t.*,
        CASE WHEN c.OrderNo IS NULL THEN 'INVALID' ELSE 'VALID' END LEISCHECK 
    FROM dbo.temp as t
    LEFT JOIN CTE as c ON c.OrderNo = t.OrderNo
    Hope this helps.

  11. #11
    Join Date
    Mar 2012
    Posts
    15
    hopefullly i think it does, thank you imex. i need to get my head around what you've done/how it works before i get to excited.

  12. #12
    Join Date
    Mar 2012
    Posts
    15
    I've not used the WITH clause before, I'm placing it before (I must admit I'm guessing it goes there) the CREATE VIEW statement but I'm getting an error "create view must be the only staement in the batch" I've tried seperating it with a GO statement but that comes up with a incorrect syntax error.

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON

    GO

    WITH CTE AS
    (
    SELECT OrderNo FROM dbo.temp
    WHERE OrderNoindicator > 0
    GROUP BY OrderNo
    HAVING MIN(OrderNoindicator) > 1
    )

    GO <-------------this gives a sysntax error

    CREATE VIEW [dbo].[CDSAuditIPCheck]
    AS
    SELECT

    datepart(month, convert(date,line05.EpisodeEndDate,112)) SubmissionMonth
    , line01.CDSTypeCode --- Mandatory
    <snip>

  13. #13
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    This code is not within the create view statement.

    Create View....

    Code:
    WITH CTE AS
    (
    SELECT OrderNo FROM dbo.temp
    WHERE OrderNoindicator > 0
    GROUP BY OrderNo
    HAVING MIN(OrderNoindicator) > 1
    )

  14. #14
    Join Date
    Mar 2012
    Posts
    15
    I'm sorry I don't know what you mean by that. If you look back earlier in the thread, the WITH CTE clause was brought in to try and provide a solution that worked inside a query that had a Create view command in it. Sorry if I'm being dum, I'm new to SQL so please forgive me if I'm talking nonsense.

  15. #15
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    CREATE VIEW [dbo].[CDSAuditIPCheck]
    AS
    
        WITH CTE AS
        (
            SELECT OrderNo FROM dbo.temp
            WHERE OrderNoindicator > 0
            GROUP BY OrderNo
            HAVING MIN(OrderNoindicator) > 1
        )
    
        SELECT
            datepart(month, convert(date,line05.EpisodeEndDate,112)) SubmissionMonth
            , line01.CDSTypeCode --- Mandatory

Posting Permissions

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