Link to home
Start Free TrialLog in
Avatar of bose3
bose3

asked on

SQL Query Sorting Order

Hello,

I have been trying to do sorting in DB2 with the following but have no success so far. I will post more details if it's confusing

Here is the before scenario

Branch A             Branch B

PIR                      PIR
PIR                      WB
WB                      PIR
WB                      WB

Is it possible to make it look like this so that Branch A and Branch B values are parallel to each other ?

Branch A             Branch B

PIR                      PIR
PIR                      PIR
WB                      WB
WB                      WB

Thanks
Avatar of Sean Stuber
Sean Stuber

Are branch A and branch B separate tables or are they columns within a single table?

Will they always be 1-to-1? If not, what do you want the results to be if there are more values in A than in B or vice versa?
The second set of data does not have the same rows as the first set. Perhaps you wanted something like this, so that you have all the parallels first, and then the non-parallels later?

Branch A             Branch B

PIR                      PIR
WB                      WB
PIR                      WB
WB                      PIR

This would SORT them as such, if I assumed correctly:

SELECT BranchA, BranchB
FROM YourTable
ORDER BY CASE WHEN BranchA = BranchB THEN '0' ELSE '1' END,
    BranchA, BranchB

Open in new window

Avatar of bose3

ASKER

Sdstuber,

Branch A and Branch B are seperate tables. The relationship could be 1 to Many. Assumption is that Branch B table value will always be in Branch A table for a given identifier. If there is a case where Branch B value doesn't exist in Branch A then that is considered a break, which requires end user action.
Not knowing what your field name is, I plugged the name Field1, so change as needed. This may work:

SELECT BranchA.Field1, BranchB.Field1
FROM BranchA
INNER JOIN BranchB ON BranchB.Field1 = BranchA.Field1
ORDER BY CASE WHEN BranchA.Field1 = BranchB.Field1 THEN '0' ELSE '1' END,
    BranchA.Field1, BranchB.Field1

Open in new window

Do you need to see those rows in BranchB that aren't in BranchA? The above query did not take that into account. Or would that simply be a second query, something like this?

SELECT *
FROM BranchB
WHERE Field1 NOT IN (SELECT Field1 FROM BranchA)

Open in new window

Avatar of bose3

ASKER

dsacker,

I tried to use your solution but go the following error.

> Script lines: 1-98 -------------------------
 [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "FACILITY_BRANCH" was found following "FACILITY_BRANCH".  Expected tokens may include:  "AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MI".  SQLSTATE=42601 "

Here is the logic for the branch match. Facility Branch (aka Branch A), Outstanding Branch (aka Branch B). Ideally I want to see the breaks only but sometimes it helps to see all the data.

Select

FAC.FAC_PID_FACILITY,
FPP_CDE_BRANCH as Facility_Branch,
FPP_CDE_PORTFOLIO as "Facility Portfolio",
FPP_CDE_EXPENSE as "Facility Expense",
OST_RID_OUTSTANDNG,
OUT.OST_NME_ALIAS as "Outstanding Alias",
OUT.OFS_CDE_BRANCH as Outstanding_Branch,
OUT.OFS_CDE_PORTFOLIO as "Outstanding Portfolio",
OUT.OFS_CDE_EXPENSE as "Outstanding Expense",

From L7001.VLS_FACILITY FAC

Left Join FPP on FPP_PID_FACILITY = FAC_PID_FACILITY

iNNER join OUT OUT on OUT.OST_PID_FACILITY = FAC_PID_FACILITY

ORDER BY CASE WHEN Facility_Branch = Outstanding_Branch THEN '0' ELSE '1'END Facility_Branch, Outstanding_Branch

Open in new window

Looks like simply some typos. There was no space before "END", and no comma after for the subsequent fields. Try this:

SELECT  FAC.FAC_PID_FACILITY,
        FPP_CDE_BRANCH as Facility_Branch,
        FPP_CDE_PORTFOLIO as "Facility Portfolio",
        FPP_CDE_EXPENSE as "Facility Expense",
        OST_RID_OUTSTANDNG,
        OUT.OST_NME_ALIAS as "Outstanding Alias",
        OUT.OFS_CDE_BRANCH as Outstanding_Branch,
        OUT.OFS_CDE_PORTFOLIO as "Outstanding Portfolio",
        OUT.OFS_CDE_EXPENSE as "Outstanding Expense",

FROM    L7001.VLS_FACILITY FAC

LEFT JOIN FPP on FPP_PID_FACILITY = FAC_PID_FACILITY

INNER JOIN OUT OUT on OUT.OST_PID_FACILITY = FAC_PID_FACILITY

ORDER BY CASE
            WHEN Facility_Branch = Outstanding_Branch THEN '0'
            ELSE '1'
         END,
         Facility_Branch,
         Outstanding_Branch

Open in new window

Avatar of bose3

ASKER

dsacker,

I fixed the typos but still getting the old results. Here is the output

FACILITY_BRANCH      OUTSTANDING_BRANCH
PIR                                     PIR
PIR                                     WB
WB                                    PIR
WB                                    WB
Hmmmm, that ORDER BY should fix that, because it accomplishes the following:

If they are the same, sets a value of '0'
Else sets a value of '1'
Sorts on that value first, THEN on the remaining columns.

Makes me wonder if they are truly passing the CASE IF = test.

Can you add an additional field, just to see?

Somewhere within the SELECT list, add the following:

        CASE
            WHEN Facility_Branch = Outstanding_Branch THEN '0'
            ELSE '1'
        END AS Match_Indicator,

Open in new window

Please let me see the two branches AND this indicator.
Avatar of bose3

ASKER

See the output below

FACILITY_BRANCH      OUTSTANDING_BRANCH      MATCH_INDICATOR
PIR                                  PIR                                                0
PIR                                  WB                                               1
WB                                  PIR                                               1
WB                                  WB                                              0
You have 3 tables in your query,  Which tables/columns correspond to the two tables you're interested in.

If there is a case where Branch B value doesn't exist in Branch A then that is considered a break,

What should that look like?

Also, given two PIR values in A and two PIR values in B, who do I know which ones should be lined up?
Keep the Match_Indicator in your SELECT LIST. Don't take it out.

Change the ORDER BY to:

ORDER BY Match_Indicator,
         Facility_Branch,
         Outstanding_Branch

Open in new window

Does that change the output? (I think we simply have to play with this a bit, and it should get you there.)
Avatar of bose3

ASKER

dsacker,

I think the issue is that there is no uniqe identifier between the multiple branches which leads to dupes and then sorting those dupes is quite tricky. I usually pull the data from 2 tables which consist branch information then sorting them independently and just apply Match formula to get the breaks.

The following join also generates the same result as "Match_Indicator"

iNNER join OUT OUT on OUT.OST_PID_FACILITY = FAC_PID_FACILITY
                 AND OUT.OFS_CDE_BRANCH <> FPP_CDE_BRANCH
Well, that would certainly make it a challenge. Perhaps you can UNION two queries, (1) SELECT joining on Facility_Branch = Outstanding_Branch, and then (2) SELECT joining on OUT.OST_PID_FACILITY = FAC_PID_FACILITY AND Facility_Branch <> Outstanding_Branch.

That may get you a little closer, but seems like you know the data on both sides well. Data mining is usually what gets you through it.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bose3

ASKER

sdstuber,

I have tried your solution and it seems to work majority of the time. I am currently applying minor tweaks to get the desired result. There are cases where Branch A has 1 row count but Branch B has 2 counts. In those cases I intentionally added dupes so that I could get the same row count as Branch B.

will keep you posted

Thanks
Seems like dsacker should get assist point awards, preferably excellent, considering the time invested and productive dialogue with author.