5 Replies Latest reply: Feb 22, 2012 5:18 PM by Frank Kulash RSS

    SQL Query

    Anjali
      I have tables Orders<->Order Lines<->Assets.

      Create an order, then order line for that order and asset for that order line.

      In assets table I can either have site_id or 3rd party location in AddressA as well as AddressB

      I need a single row showing AddressA and AddressB columns.


      SELECT DISTINCT
      O_RCN_ORDER_NO,
      A_LINE_NAME,
      A_ASSET_TAG,
      ASSET_LOC_B_ADDRESS,
      ASSET_LOC_A_ADDRESS
      from(
      SELECT DISTINCT
      O_RCN_ORDER_NO,
      A_LINE_NAME,
      A_ASSET_TAG,
      DECODE(ASSETS.A_SITE_B_SK, null, LOC.L_LOCATION_ADDRESS_1, '(' || TNS_SITE.S_TNS_SITE_ID || ') ' || TNS_SITE.S_ADDRESS_DETAIL) ASSET_LOC_B_ADDRESS,
      NULL as ASSET_LOC_A_ADDRESS
      from
      ORDERS,
      ORDER_LINES,
      ASSETS,
      TNS_SITE,
      LOC
      WHERE
      ORDERS.O_ROW_ID = ORDER_LINES.L_RCN_ORDER_SK and
      ORDER_LINES.L_ASSET_SK = ASSETS.A_ROW_ID and
      (ASSETS.A_SITE_B_SK = TNS_SITE.S_SITE_SK and TNS_SITE.S_ACTIVE = 'Y'
      or
      ASSETS.A_3RD_PARTY_LOC_B_SK = LOC.L_3RD_PARTY_LOC_SK and LOC.L_ACTIVE = 'Y'
      )
      and
      ORDERS.O_ACTIVE = 'Y' and
      ORDER_LINES.L_ACTIVE = 'Y' and
      ASSETS.A_ACTIVE = 'Y' and
      O_RCN_ORDER_NO = 1202001
      union all
      SELECT DISTINCT
      O_RCN_ORDER_NO,
      A_LINE_NAME,
      A_ASSET_TAG,
      NULL as ASSET_LOC_B_ADDRESS,
      DECODE(ASSETS.A_SITE_A_SK, null, LOC.L_LOCATION_ADDRESS_1, '(' || TNS_SITE.S_TNS_SITE_ID || ') ' || TNS_SITE.S_ADDRESS_DETAIL) ASSET_LOC_A_ADDRESS
      from
      ORDERS,
      ORDER_LINES,
      ASSETS,
      TNS_SITE,
      LOC
      WHERE
      ORDERS.O_ROW_ID = ORDER_LINES.L_RCN_ORDER_SK and
      ORDER_LINES.L_ASSET_SK = ASSETS.A_ROW_ID and
      (ASSETS.A_SITE_A_SK = TNS_SITE.S_SITE_SK and TNS_SITE.S_ACTIVE = 'Y'
      or
      ASSETS.A_3RD_PARTY_LOC_A_SK = LOC.L_3RD_PARTY_LOC_SK and LOC.L_ACTIVE = 'Y'
      )
      and
      ORDERS.O_ACTIVE = 'Y' and
      ORDER_LINES.L_ACTIVE = 'Y' and
      ASSETS.A_ACTIVE = 'Y' and
      O_RCN_ORDER_NO = 1202001 );

      Coming Output:

      O_RCN_ORDER_NO A_LINE_NAME A_ASSET_TAG ASSET_LOC_B_ADDRESS ASSET_LOC_A_ADDRESS
      1202001 TEST1 TEST1 HELLO -
      1202001 TEST1 TEST1 - (1001) 111


      Needed Output:

      O_RCN_ORDER_NO A_LINE_NAME A_ASSET_TAG ASSET_LOC_B_ADDRESS ASSET_LOC_A_ADDRESS
      1202001 TEST1 TEST1 HELLO (1001) 111


      Please help me out.
        • 1. Re: SQL Query
          Centinul
          Please see: {message:id=9360002}

          If you can provide sample data in the form of CREATE TABLE / INSERT statements as well as the expected output we should be able to help you.
          • 2. Re: SQL Query
            Frank Kulash
            Hi,
            Anjali wrote:
            ... I need a single row showing AddressA and AddressB columns.

            Coming Output:

            O_RCN_ORDER_NO A_LINE_NAME A_ASSET_TAG ASSET_LOC_B_ADDRESS ASSET_LOC_A_ADDRESS
            1202001 TEST1 TEST1 HELLO -
            1202001 TEST1 TEST1 - (1001) 111


            Needed Output:

            O_RCN_ORDER_NO A_LINE_NAME A_ASSET_TAG ASSET_LOC_B_ADDRESS ASSET_LOC_A_ADDRESS
            1202001 TEST1 TEST1 HELLO (1001) 111
            That sounds like a job for String Aggregation . Exactly how top do that depends on your exact requirements, and your Oracle version. See
            http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
            for several techniques, suitable for various versions.

            If you'd like help, post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data (formatted, inside \
             tags), as Centinul requested.
            Always say which version of Oracle you're using.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: SQL Query
              Anjali
              Ok,

              Here is the full requirement:

              Run the following queries:
              CREATE TABLE "ORDERS"
              (     "O_ROW_ID" NUMBER(*,0) NOT NULL ENABLE,      
                   "O_RCN_ORDER_NO" VARCHAR2(50 CHAR),
                   CONSTRAINT "ORDER_PK" PRIMARY KEY ("O_ROW_ID") ENABLE,
                   CONSTRAINT "ORDER_UC1" UNIQUE ("O_RCN_ORDER_NO") ENABLE
              )


              CREATE TABLE "ORDER_LINE"
              (     "L_ROW_ID" NUMBER(*,0) NOT NULL ENABLE,
                   "L_RCN_ORDER_SK" NUMBER(*,0) NOT NULL ENABLE,
                   "L_ASSET_SK" NUMBER(*,0),      
                   CONSTRAINT "ORDER_LINE_PK" PRIMARY KEY ("L_ROW_ID") ENABLE
              )
              /

              CREATE TABLE "ASSETS"
              (     "A_ROW_ID" NUMBER(*,0) NOT NULL ENABLE,
                   "A_3RD_PARTY_LOC_A_SK" NUMBER(*,0),
                   "A_3RD_PARTY_LOC_B_SK" NUMBER(*,0),
                   "A_LINE_NAME" VARCHAR2(100 CHAR),
                   "A_SITE_A_SK" NUMBER(*,0),
                   "A_SITE_B_SK" NUMBER(*,0),
                   CONSTRAINT "ASSETS_PK" PRIMARY KEY ("A_ROW_ID") ENABLE
              )

              create table LOC (
                   L_3RD_PARTY_LOC_SK INTEGER not null,
                   LOCATION_NAME VARCHAR2(200) not null,
                   L_LOCATION_ADDRESS_1 VARCHAR2(100) null,
                   constraint LOC primary key (L_3RD_PARTY_LOC_SK) );


              CREATE TABLE TNS_SITE (     
                   S_SITE_SK INTEGER NOT NULL ENABLE,
                   S_ADDRESS_DETAIL VARCHAR2(256),      
                   CONSTRAINT "TNS_SITE" PRIMARY KEY ("S_SITE_SK") ENABLE     
              )


              /
              ALTER TABLE "ASSETS" ADD CONSTRAINT "LOC_ASSETS_FK1" FOREIGN KEY ("A_3RD_PARTY_LOC_A_SK")
                   REFERENCES "LOC" ("L_3RD_PARTY_LOC_SK") ENABLE
              /
              ALTER TABLE "ASSETS" ADD CONSTRAINT "LOC_ASSETS_FK2" FOREIGN KEY ("A_3RD_PARTY_LOC_B_SK")
                   REFERENCES "LOC" ("L_3RD_PARTY_LOC_SK") ENABLE
              /
              ALTER TABLE "ASSETS" ADD CONSTRAINT "TNS_SITE_ASSETS_FK1" FOREIGN KEY ("A_SITE_A_SK")
                   REFERENCES "TNS_SITE" ("S_SITE_SK") ENABLE
              /
              ALTER TABLE "ASSETS" ADD CONSTRAINT "TNS_SITE_ASSETS_FK2" FOREIGN KEY ("A_SITE_B_SK")
                   REFERENCES "TNS_SITE" ("S_SITE_SK") ENABLE
              /
              ALTER TABLE "ORDER_LINE" ADD CONSTRAINT "ORDER_LINE_FK2" FOREIGN KEY ("L_ASSET_SK")
                   REFERENCES "ASSETS" ("A_ROW_ID") ENABLE
              /
              ALTER TABLE "ORDER_LINE" ADD CONSTRAINT "ORDER_LINE_FK1" FOREIGN KEY ("L_RCN_ORDER_SK")
                   REFERENCES "ORDERS" ("O_ROW_ID") ENABLE
              /



              Insert into loc values(1,'ABC','ABCA1');
              Insert into loc values(2,'DEF','DEFA1');

              Insert into tns_SITE values (1,'Site1');
              Insert into tns_SITE values (2,'Site2');

              Insert into orders values(1,2);



              Insert into Order_line values(1,1,1);


              Insert into Assets values(1,1,2,'ABC',NULL,NULL);

              Insert into orders values(2,3);
              Insert into Order_line values(2,2,2);
              Insert into Assets values(2,NULL,1,'Asset2',NULL,NULL);


              Now I need the records as below:

              O_RCN_ORDER_NO A_LINE_NAME ASSET_LOC_B_ADDRESS ASSET_LOC_A_ADDRESS
              1 ABC DEFA1 ABCA1
              2 Asset2 ABCA1


              Following query is not returning null values of locations .

              SELECT DISTINCT
              O_RCN_ORDER_NO,
              A_LINE_NAME,
              DECODE(ASSETS.A_SITE_B_SK, null, LOC1.L_LOCATION_ADDRESS_1, '(' || TNS_SITE1.S_SITE_SK || ') ' || TNS_SITE1.S_ADDRESS_DETAIL) ASSET_LOC_B_ADDRESS,
              DECODE(ASSETS.A_SITE_A_SK, null, LOC2.L_LOCATION_ADDRESS_1, '(' || TNS_SITE2.S_SITE_SK || ') ' || TNS_SITE2.S_ADDRESS_DETAIL) ASSET_LOC_A_ADDRESS
              from
              ORDERS,
              ORDER_LINE,
              ASSETS,
              TNS_SITE TNS_SITE1,
              TNS_SITE TNS_SITE2,
              LOC LOC1,
              LOC LOC2
              WHERE
              ORDERS.O_ROW_ID = ORDER_LINE.L_RCN_ORDER_SK and
              ORDER_LINE.L_ASSET_SK = ASSETS.A_ROW_ID and
              (ASSETS.A_SITE_B_SK = TNS_SITE1.S_SITE_SK or
              ASSETS.A_3RD_PARTY_LOC_B_SK = LOC1.L_3RD_PARTY_LOC_SK
              )
              and
              (ASSETS.A_SITE_A_SK = TNS_SITE2.S_SITE_SK or
              ASSETS.A_3RD_PARTY_LOC_A_SK = LOC2.L_3RD_PARTY_LOC_SK
              )

              Coming result:
              O_RCN_ORDER_NO A_LINE_NAME ASSET_LOC_B_ADDRESS ASSET_LOC_A_ADDRESS
              1 ABC DEFA1 ABCA1

              The query will not give null values. Please help me how can I get this corrected...
              • 4. Re: SQL Query
                Etbin
                Maybe NOT TESTED ! (no Database at hand -> restore the previous version where you don't want the nulls returned)
                SELECT DISTINCT 
                       O_RCN_ORDER_NO, 
                       A_LINE_NAME, 
                       DECODE(ASSETS.A_SITE_B_SK, null, LOC1.L_LOCATION_ADDRESS_1, 
                       '(' || TNS_SITE1.S_SITE_SK || ') ' || TNS_SITE1.S_ADDRESS_DETAIL) ASSET_LOC_B_ADDRESS, 
                       DECODE(ASSETS.A_SITE_A_SK, null, LOC2.L_LOCATION_ADDRESS_1, 
                       '(' || TNS_SITE2.S_SITE_SK || ') ' || TNS_SITE2.S_ADDRESS_DETAIL) ASSET_LOC_A_ADDRESS
                  from ORDERS, 
                       ORDER_LINE, 
                       ASSETS, 
                       TNS_SITE TNS_SITE1, 
                       TNS_SITE TNS_SITE2, 
                       LOC LOC1, 
                       LOC LOC2
                 WHERE ORDERS.O_ROW_ID = ORDER_LINE.L_RCN_ORDER_SK 
                   and ORDER_LINE.L_ASSET_SK = ASSETS.A_ROW_ID 
                
                   and (lnnvl(ASSETS.A_SITE_B_SK != TNS_SITE1.S_SITE_SK) 
                    or  lnnvl(ASSETS.A_3RD_PARTY_LOC_B_SK != LOC1.L_3RD_PARTY_LOC_SK)
                       ) 
                   and (lnnvl(ASSETS.A_SITE_A_SK != TNS_SITE2.S_SITE_SK) 
                    or  lnnvl(ASSETS.A_3RD_PARTY_LOC_A_SK != LOC2.L_3RD_PARTY_LOC_SK) 
                       ) 
                Regards

                Etbin
                • 5. Re: SQL Query
                  Frank Kulash
                  Hi,
                  Anjali wrote:
                  Ok,

                  Here is the full requirement:
                  That's some sample data. What are the requirements? Explain what you're trying to produce from this sample data. Give specific examples from the data and desired results you posted.
                  ... Now I need the records as below:

                  O_RCN_ORDER_NO A_LINE_NAME ASSET_LOC_B_ADDRESS ASSET_LOC_A_ADDRESS
                  1 ABC DEFA1 ABCA1
                  2 Asset2 ABCA1
                  Really? The sample data you posted doesn't have any row with o_rcn_order_no='1'.
                  Following query is not returning null values of locations .

                  SELECT DISTINCT
                  O_RCN_ORDER_NO,
                  A_LINE_NAME,
                  DECODE(ASSETS.A_SITE_B_SK, null, LOC1.L_LOCATION_ADDRESS_1, '(' || TNS_SITE1.S_SITE_SK || ') ' || TNS_SITE1.S_ADDRESS_DETAIL) ASSET_LOC_B_ADDRESS,
                  DECODE(ASSETS.A_SITE_A_SK, null, LOC2.L_LOCATION_ADDRESS_1, '(' || TNS_SITE2.S_SITE_SK || ') ' || TNS_SITE2.S_ADDRESS_DETAIL) ASSET_LOC_A_ADDRESS
                  from
                  ORDERS,
                  ORDER_LINE,
                  ASSETS,
                  TNS_SITE TNS_SITE1,
                  TNS_SITE TNS_SITE2,
                  LOC LOC1,
                  LOC LOC2
                  WHERE
                  ORDERS.O_ROW_ID = ORDER_LINE.L_RCN_ORDER_SK and
                  ORDER_LINE.L_ASSET_SK = ASSETS.A_ROW_ID and
                  (ASSETS.A_SITE_B_SK = TNS_SITE1.S_SITE_SK or
                  ASSETS.A_3RD_PARTY_LOC_B_SK = LOC1.L_3RD_PARTY_LOC_SK
                  )
                  and
                  (ASSETS.A_SITE_A_SK = TNS_SITE2.S_SITE_SK or
                  ASSETS.A_3RD_PARTY_LOC_A_SK = LOC2.L_3RD_PARTY_LOC_SK
                  )

                  Coming result:
                  O_RCN_ORDER_NO A_LINE_NAME ASSET_LOC_B_ADDRESS ASSET_LOC_A_ADDRESS
                  1 ABC DEFA1 ABCA1
                  Are you saying these are the results you're currently getting? As I mentioned before, the sample data you posted doesn't have any row with o_rcn_order_no='1'.

                  I assume that you really want this output:
                  O_
                  RCN_
                  ORDER
                  _NO   A_LINE_NAME ASSET_LOC_B_ADDRESS  ASSET_LOC_A_ADDRESS
                  ----- ----------- -------------------- --------------------
                  2     ABC         DEFA1                ABCA1
                  3     Asset2      ABCA1
                  You can get those results by changing the query you posted to do outer joins for the loc and tns_site tables, like this:
                  SELECT DISTINCT 
                       O_RCN_ORDER_NO
                  ,     A_LINE_NAME
                  ,     DECODE ( ASSETS.A_SITE_B_SK
                              , null     , LOC1.L_LOCATION_ADDRESS_1
                                 , '(' || TNS_SITE1.S_SITE_SK 
                                       || ') '
                                       || TNS_SITE1.S_ADDRESS_DETAIL
                              )      AS ASSET_LOC_B_ADDRESS
                  ,     DECODE ( ASSETS.A_SITE_A_SK
                              , null     , LOC2.L_LOCATION_ADDRESS_1
                                 , '(' || TNS_SITE2.S_SITE_SK 
                                       || ') '
                                       || TNS_SITE2.S_ADDRESS_DETAIL
                              )      AS ASSET_LOC_A_ADDRESS
                  from           ORDERS
                  join          ORDER_LINE     on     ORDERS.O_ROW_ID      = ORDER_LINE.L_RCN_ORDER_SK
                  join          ASSETS          on     ORDER_LINE.L_ASSET_SK      = ASSETS.A_ROW_ID 
                  left outer join     TNS_SITE      TNS_SITE1
                                      on     ASSETS.A_SITE_B_SK      = TNS_SITE1.S_SITE_SK 
                  left outer join TNS_SITE      TNS_SITE2
                                      on     ASSETS.A_SITE_A_SK      = TNS_SITE2.S_SITE_SK
                  left outer join     LOC           LOC1
                                      on     ASSETS.A_3RD_PARTY_LOC_B_SK = LOC1.L_3RD_PARTY_LOC_SK
                  left outer join     LOC           LOC2
                                      on     ASSETS.A_3RD_PARTY_LOC_A_SK = LOC2.L_3RD_PARTY_LOC_SK 
                  order by  ORDERS.O_RCN_ORDER_NO
                  ;
                  This seems like a bad design. If you have 2 addresses for the same asset, then addresses whould not be stored in the asset table; they should be stored in their own table, one address per row, with a foreign key constraint referencing the asset table.