14 Replies Latest reply: May 18, 2012 1:15 AM by 846290 RSS

    Write a query / pl/sql statement for desired results

    skyfox
      Here is the table data:

      FSSETY FSUSER FSOBNM FSA FSCHNG FSDLT FSIOK FSICPY FSATN1
      ---------------------------------------------------------------------------------
      1 DSTEIN P0030A N N N Y N Y
      1 FINAB001 P0030A Y Y Y Y Y Y
      1 FINAB001 P01012 Y Y Y Y Y Y
      1 DSTEIN P01012 Y Y Y Y Y Y
      1 DSTEIN P01013 Y Y Y Y Y Y
      1 FINAB001 P01013 Y Y Y Y Y Y
      1 FINAB001 P0111 Y Y Y Y Y Y
      1 DSTEIN P0111 Y Y Y Y Y Y
      1 DSTEIN P01111 Y Y Y Y Y Y
      1 FINAB001 P01111 Y Y Y Y Y Y
      1 FINAB001 P0115 Y Y Y Y Y Y
      1 DSTEIN P0115 Y Y Y Y Y Y
      1 DSTEIN P01BDWRD Y Y Y Y Y Y
      1 FINAB001 P01BDWRD Y Y Y Y Y Y
      1 FINAB001 P0411 Y Y Y Y Y Y
      1 DSTEIN P0411 N N N Y N Y

      What I need in results is:
      For each FSOBNM, return the rows with different FSUSER values and non-matching values in fsa OR fschng OR fsdlt OR fsicpy.

      So, the desired output of the query should return the first two rows and the last two rows, as they meet this criteria.

      I need help in the statement that would return the desired results.
      Thanks!

      Edited by: skyfox on 17-May-2012 5:54 PM

      Edited by: skyfox on 17-May-2012 5:54 PM
        • 2. Re: Write a query / pl/sql statement for desired results
          skyfox
          Yes, searched for clues, in vain.

          SQL> select * from table_1;
          FSSETY FSUSER FSOBNM FSA FSCHNG FSDLT FSIOK FSICPY FSATN1
          ---------------------------------------------------------------------------------
          1 DSTEIN P0030A N N N Y N Y
          1 FINAB001 P0030A Y Y Y Y Y Y
          1 FINAB001 P01012 Y Y Y Y Y Y
          1 DSTEIN P01012 Y Y Y Y Y Y
          1 DSTEIN P01013 Y Y Y Y Y Y
          1 FINAB001 P01013 Y Y Y Y Y Y
          1 FINAB001 P0111 Y Y Y Y Y Y
          1 DSTEIN P0111 Y Y Y Y Y Y
          1 DSTEIN P01111 Y Y Y Y Y Y
          1 FINAB001 P01111 Y Y Y Y Y Y
          1 FINAB001 P0115 Y Y Y Y Y Y
          1 DSTEIN P0115 Y Y Y Y Y Y
          1 DSTEIN P01BDWRD Y Y Y Y Y Y
          1 FINAB001 P01BDWRD Y Y Y Y Y Y
          1 FINAB001 P0411 Y Y Y Y Y Y
          1 DSTEIN P0411 N N N Y N Y


          The results are such because for each FSOBNM, the rows with different FSUSER values and non-matching values in fsa OR fschng OR fsdlt OR fsicpy are returned.


          The desired results from the query / pl/sql statement should be:

          FSSETY FSUSER FSOBNM FSA FSCHNG FSDLT FSIOK FSICPY FSATN1
          ---------------------------------------------------------------------------------
          1 DSTEIN P0030A N N N Y N Y
          1 FINAB001 P0030A Y Y Y Y Y Y
          1 FINAB001 P0411 Y Y Y Y Y Y
          1 DSTEIN P0411 N N N Y N Y

          Thanks.

          Edited by: skyfox on 17-May-2012 5:51 PM
          • 3. Re: Write a query / pl/sql statement for desired results
            sb92075
            What about doing 6, 7, 9 in Guidelines?
            • 4. Re: Write a query / pl/sql statement for desired results
              skyfox
              SQL> desc mjm.matt;
              Name Null? Type
              ----------------------------------------- -------- --------------------
              FSSETY VARCHAR2(1)
              FSUSER VARCHAR2(20)
              FSOBNM VARCHAR2(20)
              FSA VARCHAR2(1)
              FSCHNG VARCHAR2(1)
              FSDLT VARCHAR2(1)
              FSIOK VARCHAR2(1)
              FSICPY VARCHAR2(1)
              FSATN1 VARCHAR2(1)
              • 5. Re: Write a query / pl/sql statement for desired results
                sb92075
                Three strikes & you are OUT!
                • 6. Re: Write a query / pl/sql statement for desired results
                  skyfox
                  So helpful - Very Professional!
                  • 7. Re: Write a query / pl/sql statement for desired results
                    sb92075
                    skyfox wrote:
                    So helpful - Very Professional!
                    Since you refuse to provide me with actual SQL to make table & load test data (INSERT statements),
                    you reaped what you sowed.
                    • 8. Re: Write a query / pl/sql statement for desired results
                      skyfox
                      Alright then, here is the create statement:

                      CREATE TABLE "MJM"."MATT"
                      ( "FSSETY" VARCHAR2(1),
                      "FSUSER" VARCHAR2(20),
                      "FSOBNM" VARCHAR2(20),
                      "FSA" VARCHAR2(1),
                      "FSCHNG" VARCHAR2(1),
                      "FSDLT" VARCHAR2(1),
                      "FSIOK" VARCHAR2(1),
                      "FSICPY" VARCHAR2(1),
                      "FSATN1" VARCHAR2(1)
                      ) SEGMENT CREATION IMMEDIATE
                      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                      TABLESPACE "USERS"

                      ThankYOu.
                      • 9. Re: Write a query / pl/sql statement for desired results
                        skyfox
                        Please note that the Insert statements are unavailable as the data was loaded while back by a user.
                        • 10. Re: Write a query / pl/sql statement for desired results
                          sb92075
                          SELECT f1.FSSETY, 
                                 f1.FSUSER, 
                                 f1.FSOBNM 
                          FROM   matt f1, 
                                 matt f2 
                          WHERE  f1.FSOBNM = F2.FSOBNM 
                                 AND F1.FUSER != F2.FUSER 
                                 AND ( F1.FSA != F2F2.fsa 
                                        OR F1.FSCHNG != F2.FSCHNG 
                                        OR F1.FSDLT != F2.FSDLT 
                                        OR F1.FSICPY != F2.FSICPY ); 
                          • 11. Re: Write a query / pl/sql statement for desired results
                            skyfox
                            A little tweaking and got the results required - ThankYou very much!

                            Regards.
                            • 12. Re: Write a query / pl/sql statement for desired results
                              sb92075
                              skyfox wrote:
                              A little tweaking and got the results required - ThankYou very much!

                              Regards.
                              So why NOT share the correct results?
                              • 13. Re: Write a query / pl/sql statement for desired results
                                skyfox
                                SQL> SELECT f1.FSSETY,
                                2 f1.FSUSER,
                                3 f1.FSOBNM,
                                4 f1.FSA,
                                5 f1.FSCHNG,
                                6 f1.FSDLT,
                                7 f1.FSIOK,
                                8 f1.FSICPY,
                                9 f1.FSATN1
                                10 FROM matt f1,
                                11 matt f2
                                12 WHERE f1.FSOBNM = F2.FSOBNM
                                13 AND F1.FSUSER != F2.FSUSER
                                14 AND ( F1.FSA != F2.FSA
                                15 OR F1.FSCHNG != F2.FSCHNG
                                16 OR F1.FSDLT != F2.FSDLT
                                17 OR F1.FSICPY != F2.FSICPY );

                                F FSUSER FSOBNM F F F F F F
                                - -------------------- -------------------- - - - - - -
                                1 FINAB001 P0030A Y Y Y Y Y Y
                                1 DSTEIN P0030A N N N Y N Y
                                1 FINAB001 P0411 Y Y Y Y Y Y
                                1 DSTEIN P0411 N N N Y N Y

                                ThankYou.
                                • 14. Re: Write a query / pl/sql statement for desired results
                                  846290
                                  Hi Skyfox,

                                  Below is the query you might be looking into
                                  SQL> 
                                  SQL> WITH test AS(
                                    2  SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P0030A' FSOBNM, 'N' FSA, 'N' FSCHNG, 'N' FSDLT, 'Y' FSIOK, 'N' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                    3  SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P0030A' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                    4  SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P01012' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                    5  SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P01012' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                    6  SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P01013' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                    7  SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P01013' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                    8  SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P0111' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                    9  SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P0111' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                   10  SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P01111' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                   11  SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P01111' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                   12  SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P0115' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                   13  SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P0115' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                   14  SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P01BDWRD' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                   15  SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P01BDWRD' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                   16  SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P0411' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL  UNION ALL
                                   17  SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P0411' FSOBNM, 'N' FSA, 'N' FSCHNG, 'N' FSDLT, 'Y' FSIOK, 'N' FSICPY, 'Y' FSATN1 FROM DUAL)
                                   18  SELECT *
                                   19    FROM test a
                                   20   WHERE EXISTS (SELECT 1 FROM test
                                   21                  WHERE fsobnm = a.fsobnm
                                   22                    AND fsuser != a.fsuser
                                   23                    AND (fsa != a.fsa OR fschng != a.fschng OR fsdlt != a.fsdlt OR fsicpy != a.fsicpy))
                                   24  /
                                   
                                      FSSETY FSUSER   FSOBNM   FSA FSCHNG FSDLT FSIOK FSICPY FSATN1
                                  ---------- -------- -------- --- ------ ----- ----- ------ ------
                                           1 DSTEIN   P0030A   N   N      N     Y     N      Y
                                           1 FINAB001 P0030A   Y   Y      Y     Y     Y      Y
                                           1 FINAB001 P0411    Y   Y      Y     Y     Y      Y
                                           1 DSTEIN   P0411    N   N      N     Y     N      Y
                                   
                                  SQL>