Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: Help, I am brain-dead

    I am brain dead and need help.

    I want to list the results from these similar queries as one column.
    How do I do that? My brain stopped working about an hour ago.

    So, I want one list of all customer ids, I don't care if there are duplicates.
    If I can join the three tables (one similar table between the two), fine. That is not required either however.

    example results:
    1st query might get:
    1,
    2,
    3

    2nd query might get:
    4

    I need the output:
    1
    2
    3
    4

    Thanks.

    PHP Code:
    SELECT 
      n
    .cust_id 
    FROM  
      network_issue_log n

      
    network_issue nn 
    WHERE 
    n
    .org_id 'FT' AND n.org_id nn.org_id AND n.component_cd nn.component_cd AND 
    n.issue_cd nn.issue_cd AND nn.severity_cd 'C'

    SELECT 
      f
    .cust_id 
    FROM  
      furnace_issue f

      
    network_issue nn 
    WHERE f
    .org_id 'FT' AND f.org_id nn.org_id AND f.component_cd nn.component_cd AND 
    f.issue_cd nn.issue_cd AND f.active_ind 'Y' AND nn.severity_cd 'C' 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    UNION, UNION ALL ????

    Gregg

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by gbrabham
    UNION, UNION ALL ????

    Gregg
    Can I do that in a subquery??

    eventually I want:
    PHP Code:
    where cust_id not in /* the combination of those two queries */
    1,2,3,4
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    re: Brain dead

    You can use a full outer join

    here is an example

    SQL> create table a (id number);
    SQL> create table b (id number);

    Table created.

    SQL> insert into a values(1);
    SQL> insert into a values(2);
    SQL> insert into b values(2);
    SQL> insert into b values(3);

    4 row created.

    SQL> select decode(a.id,null,b.id,a.id)
    2 from a full outer join b on a.id = b.id;

    DECODE(A.ID,NULL,B.ID,A.ID)
    ---------------------------
    1
    2
    3

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Duck ... I'm in the middle of doing some data conversions ... so I aint thinkin too straight, I don't guess ... Why does union not work ...

    Ex:
    select count(*) from load_cust_address where customernumber in
    (select customernumber from load_customer
    union
    select customernumber from load_cust_contacts);

    Gregg

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    union works but it turns out to be really inefficient.

    The decode/full outer join looks interesting.
    How could I do it against 3 tables like I have above?

    Can I join somehow?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    re Brain Dead

    Duck,

    Just Full outer join the 3rd table in

    SQL> create table c (id number);
    SQL> insert into c values(3);
    SQL> insert into c values(4);

    SQL> select decode(a.id,null,decode(b.id,null,c.id,b.id),a.id) ID
    2 from a full outer join b on a.id = b.id
    3* full outer join c on a.id = c.id
    SQL> /

    ID
    ----------
    1
    2
    3
    3
    4


    HIH

  8. #8
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    re: Brain Dead

    Sorry, misunderstood. I think something like this should work.

    PHP Code:
    SELECT a.cust_id
    FROM
    (SELECT decode(f.cust_id,null,n.cust_id,f.cust_idcust_id,
           
    decode(f.org_id,null,n.org_id,f.org_idorg_id,
           
    decode(f.component_cd,null,n.component_cd,f.conponent_cdcomponent_cd,
           
    decode(f.issue_cd,null,n.issue_cd,f.issue_cdissue_cd
    FROM furnace_issue f full outer join network_issue_log n on f
    .cust_id n.cust_id
    WHERE f
    .active_ind 'Y' 
       join network_issue nn on a
    .org_id nn.org_id
                             on a
    .component_cd nn.component_cd
                             on a
    .issue_cd nn.issue_cd                                                                                  
    WHERE a
    .org_id 'FT'   
      
    AND nn.severity_cd 'C' 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •