November 14, 2014 at 5:15 am
Hi,
I am stuck on this query if someone could please help me. For a sub-query, I need to use the Group by on only one column and include a CASE statement in the select clause without using MAX function.
The sub-query is as follows which is the part of the main query below:
SELECT BOOKING_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'BK' THEN PARTY_NAME
WHEN COMPANY_ROLE_CD <> 'BK' AND BOOKED_BY_FLG = 'Y' THEN PARTY_NAME ELSE NULL END) AS [BOOKING PARTY]
FROM MiniGapp..MG_BOOKING_PARTY
GROUP BY BOOKING_ID
The main query is as below:
SELECT distinct [BOOKING PARTY],[USER_NAME],
MG_BOOKING.BOOKING_NUM
,MG_BOOKING.BOOKING_OFFICE_CD
,MG_BOOKING.BOOKING_STATUS_CD
,MG_BOOKING.CREATE_DT
,MG_BOOKING.POR_LOCATION_CD
,MG_BOOKING.POL_LOCATION_CD
,MG_BOOKING.PDL_LOCATION_CD
,MG_BOOKING.EDI_PARTNER_CD
,MG_BOOKING.EDI_PARTNER_REF
FROM
MG_BOOKING
LEFT OUTER JOIN MG_OFFICE
ON MG_BOOKING.BOOKING_OFFICE_CD = MG_OFFICE.OFFICE_CD
INNER JOIN MG_ISEC_APPLICATION_USERS ON
MG_BOOKING.LAST_UPDATE_USER_ID = MG_ISEC_APPLICATION_USERS.USER_ID
INNER JOIN MG_BOOKING_COMMODITY ON
MG_BOOKING.BOOKING_ID = MG_BOOKING_COMMODITY.BOOKING_ID
LEFT JOIN
(
SELECT
BOOKING_ID,
MAX(CASE WHEN COMPANY_ROLE_CD = 'BK' THEN PARTY_NAME
WHEN COMPANY_ROLE_CD <> 'BK' AND BOOKED_BY_FLG = 'Y' THEN PARTY_NAME ELSE NULL END) AS [BOOKING PARTY]
FROM MiniGapp..MG_BOOKING_PARTY
)MGP
ON MG_BOOKING.BOOKING_ID = MGP.BOOKING_ID
Thanks.
November 14, 2014 at 6:55 am
Can there be more than one row in MiniGapp..MG_BOOKING_PARTY table for a BOOKING_ID?
If so how do you determine which one?
If not then you do not need a sub query.
Far away is close at hand in the images of elsewhere.
Anon.
November 14, 2014 at 8:22 am
David Burrows (11/14/2014)
Can there be more than one row in MiniGapp..MG_BOOKING_PARTY table for a BOOKING_ID?If so how do you determine which one?
If not then you do not need a sub query.
Thanks,
There can be more than one rows for one BOOKING_ID. The criteria to select the specific row is as follows:
CASE WHEN COMPANY_ROLE_CD = 'BK' THEN PARTY_NAME
WHEN COMPANY_ROLE_CD <> 'BK' AND BOOKED_BY_FLG = 'Y' THEN PARTY_NAME ELSE NULL END AS [BOOKING PARTY]
However, if both the above rows are present then row where COMPANY_ROLE_CD = 'BK' should be chosen.
November 14, 2014 at 9:21 am
LEFT JOIN (
SELECT BOOKING_ID,
CASE WHEN COMPANY_ROLE_CD = 'BK' OR BOOKED_BY_FLG = 'Y'
THEN PARTY_NAME
ELSE NULL
END AS [BOOKING PARTY],
ROW_NUMBER() OVER (
PARTITION BY BOOKING_ID
ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 ELSE 2 END ASC) AS [ROWID]
FROM MG_BOOKING_PARTY
) MGP
ON MG_BOOKING.BOOKING_ID = MGP.BOOKING_ID
AND MGP.ROWID = 1
Far away is close at hand in the images of elsewhere.
Anon.
November 14, 2014 at 9:33 am
David Burrows (11/14/2014)
LEFT JOIN (
SELECT BOOKING_ID,
CASE WHEN COMPANY_ROLE_CD = 'BK' OR BOOKED_BY_FLG = 'Y'
THEN PARTY_NAME
ELSE NULL
END AS [BOOKING PARTY],
ROW_NUMBER() OVER (
PARTITION BY BOOKING_ID
ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 ELSE 2 END ASC) AS [ROWID]
FROM MG_BOOKING_PARTY
) MGP
ON MG_BOOKING.BOOKING_ID = MGP.BOOKING_ID
AND MGP.ROWID = 1
Cheers David, you nailed it and that too without any sample data or DDLs. Absolutely amazing !
Thanks a lot for your immense help !!!.
November 17, 2014 at 8:29 am
David Burrows (11/14/2014)
LEFT JOIN (
SELECT BOOKING_ID,
CASE WHEN COMPANY_ROLE_CD = 'BK' OR BOOKED_BY_FLG = 'Y'
THEN PARTY_NAME
ELSE NULL
END AS [BOOKING PARTY],
ROW_NUMBER() OVER (
PARTITION BY BOOKING_ID
ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 ELSE 2 END ASC) AS [ROWID]
FROM MG_BOOKING_PARTY
) MGP
ON MG_BOOKING.BOOKING_ID = MGP.BOOKING_ID
AND MGP.ROWID = 1
Thanks David,
Your solution worked in most of the cases but didnt work in one scenario:
If for e.g. COMPANY_ROLE_CD = 'SH' AND BOOKED_BY_FLG = 'Y' then in this case it should still display the PARTY NAME, however, it is displaying the value as NULL.
I dont know why this is happening because you have clearly specified this condition in your code.
There are actually multiple rows for one BOOKING_ID and as seen in the attached example ROWID = 5 is the correct row to display PARTY NAME but as per your code only ROWID = 1 should be displayed.
I am wondering could that be the reason that I am getting incorrect result ?
Thanks.
November 17, 2014 at 10:47 am
Yes you are correct
the line 'ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 ELSE 2 END ASC) AS [ROWID]'
puts the most desirable name at row 1 ie BK first
You may have to do either
ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' OR BOOKED_BY_FLG = 'Y' THEN 1 ELSE 2 END ASC) AS [ROWID]
or
ORDER BY CASE WHEN COMPANY_ROLE_CD IN ('BK','SH') OR BOOKED_BY_FLG = 'Y' THEN 1 ELSE 2 END ASC) AS [ROWID]
Far away is close at hand in the images of elsewhere.
Anon.
November 18, 2014 at 2:21 am
David Burrows (11/17/2014)
Yes you are correctthe line 'ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 ELSE 2 END ASC) AS [ROWID]'
puts the most desirable name at row 1 ie BK first
You may have to do either
ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' OR BOOKED_BY_FLG = 'Y' THEN 1 ELSE 2 END ASC) AS [ROWID]
or
ORDER BY CASE WHEN COMPANY_ROLE_CD IN ('BK','SH') OR BOOKED_BY_FLG = 'Y' THEN 1 ELSE 2 END ASC) AS [ROWID]
Thanks again David. But this still doesnt work as row (PARTY NAME) associated with COMPANY_ROLE_CD = 'BK' should always be given priority.
Actually the row should be strictly displayed as per the following order of preference:
1. If COMPANY_ROLE_CD = 'BK' is present then PARTY NAME for that row should be displayed.
2. If COMPANY_ROLE_CD = 'BK' does not exist in any row but BOOKED_BY_FLG = 'Y' exists then PARTY NAME associated with BOOKED_BY_FLG = 'Y' should be displayed.
3. If both COMPANY_ROLE_CD = 'BK' and BOOKED_BY_FLG = 'Y' exist in different (or same) rows, then PARTY NAME associated with COMPANY_ROLE_CD = 'BK' should be displayed. Row for BOOKED_BY_FLG = 'Y' should be ignored in this case.
4. If both COMPANY_ROLE_CD = 'BK' and BOOKED_BY_FLG = 'Y' don't exist, then NULL (or blank) value should be displayed.
This might be getting too much for you but I think the solution is quite close.
Thanks for your help.
November 18, 2014 at 2:33 am
Try this
ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 WHEN BOOKED_BY_FLG = 'Y' THEN 2 ELSE 3 END ASC) AS [ROWID]
it worked for me
Far away is close at hand in the images of elsewhere.
Anon.
November 18, 2014 at 3:06 am
David Burrows (11/18/2014)
Try this
ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 WHEN BOOKED_BY_FLG = 'Y' THEN 2 ELSE 3 END ASC) AS [ROWID]
it worked for me
Thanks very much, David. This worked perfectly and also proved to be good learning lesson as well !!! Cheers !
November 18, 2014 at 3:13 am
pwalter83 (11/18/2014)
David Burrows (11/18/2014)
Try this
ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 WHEN BOOKED_BY_FLG = 'Y' THEN 2 ELSE 3 END ASC) AS [ROWID]
it worked for me
Thanks very much, David. This worked perfectly and also proved to be good learning lesson as well !!! Cheers !
You're welcome
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy