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
bose3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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?
0
dsackerContract ERP Admin/ConsultantCommented:
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

0
bose3Author Commented:
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.
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

dsackerContract ERP Admin/ConsultantCommented:
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

0
bose3Author Commented:
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

0
dsackerContract ERP Admin/ConsultantCommented:
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

0
bose3Author Commented:
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
0
dsackerContract ERP Admin/ConsultantCommented:
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.
0
bose3Author Commented:
See the output below

FACILITY_BRANCH      OUTSTANDING_BRANCH      MATCH_INDICATOR
PIR                                  PIR                                                0
PIR                                  WB                                               1
WB                                  PIR                                               1
WB                                  WB                                              0
0
sdstuberCommented:
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?
0
dsackerContract ERP Admin/ConsultantCommented:
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.)
0
bose3Author Commented:
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
0
dsackerContract ERP Admin/ConsultantCommented:
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.
0
sdstuberCommented:
using your original two table model...
this will create an ordering that will allow successful joining without duplicates
change the order by within the OVER clauses to something more useful if you can determine a meaningful pairing between A and B

  SELECT a.col, b.col
    FROM (SELECT branch_a.*, ROW_NUMBER() OVER(PARTITION BY col ORDER BY col) rn FROM branch_a) a
         LEFT JOIN
         (SELECT branch_b.*, ROW_NUMBER() OVER(PARTITION BY col ORDER BY col) rn FROM branch_b) b
             ON a.col = b.col AND a.rn = b.rn
ORDER BY a.col, b.col
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bose3Author Commented:
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
0
dsackerContract ERP Admin/ConsultantCommented:
Seems like dsacker should get assist point awards, preferably excellent, considering the time invested and productive dialogue with author.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.