Unable to get the results im expecting with a join or sub select

  • cubangt

    SSC Enthusiast

    Points: 129

    I have a query that pulls in from 7 tables..

    One of those tables i just need 1 column returned for each record. The issue is that the table can have multiple records or no records.. and if there are multiple records, i just need to return 1 value, i dont need to have 3 records returned, currently everything that is returned is exploded out to the number of records that match in the 2nd table

    I only need to know if there is a value in that table, dont need the value or care what it is, i just need to know that there are records or there arent any..

    I created a case statement on the select of that one column, but doesnt work as expected..

    (SELECT

    CASE

    WHEN SSN.ASN IS NULL THEN ‘NO ASN’

    WHEN (COUNT(SSN.ASN) > 0) THEN SSN.ASN

    ELSE SSN.ASN

    END AS ASN

    FROM RMS13.SHIPMENT SSN

    WHERE ROWNUM <= 1

    GROUP BY SSN.ASN ) AS ASN

  • Michael L John

    One Orange Chip

    Points: 25605

    cubangt wrote:

    I have a query that pulls in from 7 tables..

    One of those tables i just need 1 column returned for each record. The issue is that the table can have multiple records or no records.. and if there are multiple records, i just need to return 1 value, i dont need to have 3 records returned, currently everything that is returned is exploded out to the number of records that match in the 2nd table

    I only need to know if there is a value in that table, dont need the value or care what it is, i just need to know that there are records or there arent any..

    I created a case statement on the select of that one column, but doesnt work as expected..

    (SELECT

    CASE

    WHEN SSN.ASN IS NULL THEN 'NO ASN'

    WHEN (COUNT(SSN.ASN) > 0) THEN SSN.ASN

    ELSE SSN.ASN

    END AS ASN

    FROM RMS13.SHIPMENT SSN

    WHERE ROWNUM <= 1

    GROUP BY SSN.ASN ) AS ASN

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John

    One Orange Chip

    Points: 25605

    Correct, that will not work. It appears that you need an EXISTS clause in the where clause. Without seeing the query and tables, the syntax is as follows:

    SELECT

    blah, blah, blah,

    FROM Table

    INNER JOIN OtherTables

    WHERE EXISTS (SELECT * FROM StillAnotherTable WHERE StillAnotherTable.Column = Table.Column)

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • cubangt

    SSC Enthusiast

    Points: 129

    Ok, sorry had to run to meeting and was not able to post everything originally

    Here is the entire query

    SELECT DISTINCT h.ORDER_NO, dps.DEPT_NAME, im.CLASS, c.CLASS_NAME, s.SUP_NAME, d.EIDE AS EISD,

    (SELECT

    CASE

    WHEN SSN.ASN IS NULL THEN ‘NO ASN’

    WHEN (COUNT(SSN.ASN) > 0) THEN SSN.ASN

    ELSE SSN.ASN

    END AS ASN

    FROM SHIPMENT SSN

    WHERE ROWNUM <= 1

    GROUP BY SSN.ASN ) AS ASN

    FROM ord h,

    SHIPMENT sasn,

    loc d,

    item im,

    cls c,

    sps s,

    dps

    WHERE h.ORDER_NO = d.ORDER_NO

    AND d.ITEM = im.ITEM

    AND im.CLASS = c.CLASS

    AND h.SUPPLIER = s.SUPPLIER

    AND im.DEPT = dps.DEPT

    AND h.STATUS = ‘C’

    AND im.ITEM_LEVEL = im.TRAN_LEVEL

    AND d. EIDE > ((SELECT period.VDATE FROM period) + 1)

    GROUP BY h.ORDER_NO,

    dps.DEPT_NAME,

    im.CLASS,

    c.CLASS_NAME,

    s.SUP_NAME,

    d. EIDE,

    sasn.ASN

    ORDER BY EISD,

    h.ORDER_NO;

    Seems i dont have access to upload a picture, but the above runs and returns records within a few second, the issue is that i only need 1 record per ORDER_NO regardless how many records have a ASN from the case statement..

    And case statement may not be the solution, so just need some guidance on where i should look and try to get the correct list of records returned

  • Michael L John

    One Orange Chip

    Points: 25605

    There are more things wrong with this query than just this part.

    For starters, this join syntax, while it works, is not really preferred.

    Do you know the syntax for an INNER JOIN?

    That table SHIPMENT is not joined to any other table. This creates a full join. Is that what you want?

    If you are using a GROUP BY, you do not need DISTINCT.

    What column in the table Shipment can be joined to one of the other tables?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • cubangt

    SSC Enthusiast

    Points: 129

    I have worked with inner join before..

    the shipment table can be joined on order_no with the h.order_no field.

  • Michael L John

    One Orange Chip

    Points: 25605

    Your date calculation may not produce the correct results.

    Is the case of this code correct? Does it match the database? I’m guessing that your database is case-insensitive, but it’s infinitely more readable if you write your code to match the objects.

    Is this database your design? The names are pretty bad. There are keywords being used for object names. That’s probably not a good idea.

    DECLARE @StartDate datetime

    /*

    This may not produce the correct results.

    If there is a time portion of the date, you may not

    be getting all of the records.

    That’s assuming that you want the full day.

    */

    SELECT @StartDate = DATEADD(day, 1, P.VDATE)

    FROM [period] P

    SELECT

    H.ORDER_NO

    ,DS.DEPT_NAME

    ,IM.CLASS

    ,C.CLASS_NAME

    ,S.SUP_NAME

    ,D.EIDE AS EISD

    FROM ord H

    INNER JOIN loc D ON H.ORDER_NO = D.ORDER_NO

    INNER JOIN item IM ON D.ITEM = IM.ITEM

    INNER JOIN cls C ON IM.CLASS = C.CLASS

    INNER JOIN sps S ON H.SUPPLIER = S.SUPPLIER

    INNER JOIN dps DS ON IM.DEPT = DS.DEPT

    WHERE

    H.STATUS = ‘C’

    AND IM.ITEM_LEVEL = IM.TRAN_LEVEL

    AND D.EIDE > @StartDate

    AND EXISTS (SELECT *

    FROM SHIPMENT SSN

    WHERE SSN.order_no = H.order_no

    GROUP BY

    H.ORDER_NO

    ,DS.DEPT_NAME

    ,IM.CLASS

    ,C.CLASS_NAME

    ,S.SUP_NAME

    ,D.EIDE

    ,sasn.ASN –Is this correct? It is not in the select

    ORDER BY

    D.EIDE –Your old syntax is no longer supported

    ,H.ORDER_NO;

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • cubangt

    SSC Enthusiast

    Points: 129

    Yea the names of the tables and columns have been changed before posting as this is for my work.

    Ill have to check everything on monday when i get back to work..

    But i appreciate the reply and will let you know if these suggestions worked.

  • cubangt

    SSC Enthusiast

    Points: 129

    well i made the suggested changes, but the query ran for 701.57(11 minutes) seconds before i cancelled it..

    Before the changes the query would only take about 15 to 20 seconds to run

  • Michael L John

    One Orange Chip

    Points: 25605

    If it ran for 15-20 seconds before, did it produce the correct results?

    The original code you posted will not run. The syntax is not correct. Can you post the code that runs in 15-20 seconds, as well as the create table statements, sample data, and sample results?

    The link in my signature tells you how to do that.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Jonathan Szeto

    SSC Journeyman

    Points: 97

    Because you don’t have any sort of join relationship between SHIPMENT and any other table (either in the FROM clause or the sub-select subquery), the query is performing two cross joins against SHIPMENT.

    Is the combination of ASN and ROWNUM unique within the SHIPMENT table? If so, then you can do a left outer join from SHIPMENT to a derived table set of SHIPMENT, using the first value of ROWNUM for the given ASN. Like so:

    SELECT

    CASE

    WHEN ssn.ASN IS NULL THEN ‘NO ASN’

    ELSE ssn.ASN

    END AS ASN,

    FROM SHIPMENT sasn

    LEFT JOIN (

    SELECT

    ASN,

    min(ROWNUM)

    FROM SHIPMENT

    GROUP BY

    ASN

    ) ssn

    ON sasn.ASN = ssn.ASN

    AND sasn.ROWNUM = ssn.ROWNUM

  • cubangt

    SSC Enthusiast

    Points: 129

    Ive made the additional changes and suggestions and provided the results to the team to validate if it will work for what they are needing..

    So the shipment table is only needed to determine if ANY ASN value exists, If it does or does not they just need to know if something exists for the order number.

    THIS ONE HAS NO ORDER NUMBER AT ALL IN THE TABLE, BUT DOES NOT RETURN “NO ASN”

    =======================

    SELECT

    CASE

    WHEN SSN.ASN IS NULL THEN ‘NO ASN’

    WHEN (COUNT(SSN.ASN) = 0) THEN ‘NO ASN’

    WHEN (COUNT(SSN.ASN) > 0) THEN SSN.ASN

    ELSE SSN.ASN

    END AS ASN

    FROM SHIPMENT SSN

    WHERE ROWNUM <= 1 AND SSN.ORDER_NO = ‘4479951’

    GROUP BY SSN.ASN;

    =======================

    THIS ONE HAS A RECORD IN THE TABLE AND NO ASN, SO THIS CASE CONDITION IS MET AND RETURNS “NO ASN”

    =======================

    SELECT

    CASE

    WHEN SSN.ASN IS NULL THEN ‘NO ASN’

    WHEN (COUNT(SSN.ASN) = 0) THEN ‘NO ASN’

    WHEN (COUNT(SSN.ASN) > 0) THEN SSN.ASN

    ELSE SSN.ASN

    END AS ASN

    FROM SHIPMENT SSN

    WHERE ROWNUM <= 1 AND SSN.ORDER_NO = ‘4880349’

    GROUP BY SSN.ASN;

    =======================

    THIS ONE HAS A RECORD AND HAS A ASN IN THE TABLE, SO IT RETURNS THE ASN AS EXPECTED.

    =======================

    SELECT

    CASE

    WHEN SSN.ASN IS NULL THEN ‘NO ASN’

    WHEN (COUNT(SSN.ASN) = 0) THEN ‘NO ASN’

    WHEN (COUNT(SSN.ASN) > 0) THEN SSN.ASN

    ELSE SSN.ASN

    END AS ASN

    FROM SHIPMENT SSN

    WHERE ROWNUM <= 1 AND SSN.ORDER_NO = ‘4416373’

    GROUP BY SSN.ASN;

  • cubangt

    SSC Enthusiast

    Points: 129

    The reason i need to return “no asn” in the results is because they export these records out and send out to users that need the data and they need to be able to filter out anything without a ASN

    And yes i know they can simply filter out based on “No ASN” and any records that is blank in that column, but rather just have 2 filter types

    No ASN and the ASN number itself.

  • Michael L John

    One Orange Chip

    Points: 25605

    You are only providing us part of the information, and your requirements have changed somewhat since your original post.

    Again, I am making a guess here because I cannot see what you are seeing, but I suggest doing a LEFT JOIN to the SSN table on SSN.order_no = H.order_no.

    You can then do this:

    CASE WHEN SSN.ASN IS NULL

    THEN ‘NO ASN’

    ELSE  SSN.ASN

    END

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply