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