April 4, 2019 at 8:25 pm
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
April 4, 2019 at 8:53 pm
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/
April 4, 2019 at 8:56 pm
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/
April 4, 2019 at 9:07 pm
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
April 4, 2019 at 9:20 pm
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/
April 4, 2019 at 9:30 pm
I have worked with inner join before..
the shipment table can be joined on order_no with the h.order_no field.
April 5, 2019 at 1:17 pm
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/
April 6, 2019 at 1:39 am
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.
April 8, 2019 at 2:01 pm
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
April 8, 2019 at 2:17 pm
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/
April 8, 2019 at 8:19 pm
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
...
April 8, 2019 at 8:46 pm
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;
April 8, 2019 at 9:09 pm
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.
April 9, 2019 at 2:26 pm
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/
March 26, 2022 at 7:28 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply