Home » SQL & PL/SQL » SQL & PL/SQL » If not in an oracle script
If not in an oracle script [message #435692] Thu, 17 December 2009 07:31 Go to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
Hi,

In the following script, the record with SPRIDEN_PIDM = 59929 has no records in NBRBJOB B. I want to get the record with the SPRIDEN information no matter has no records in NBRBJOB.

Thanks for your help.

SELECT
B.NBRBJOB_PIDM,
B.NBRBJOB_SUFF,
B.NBRBJOB_BEGIN_DATE,
B.NBRBJOB_END_DATE,
SPRIDEN_ID
FROM NBRBJOB B, SPRIDEN
WHERE
B.NBRBJOB_SUFF =
SELECT MAX(A.NBRBJOB_SUFF)
FROM NBRBJOB A
WHERE A.NBRBJOB_PIDM = B.NBRBJOB_PIDM)
AND B.NBRBJOB_PIDM = SPRIDEN_PIDM
AND SPRIDEN_PIDM = '59928'
AND B.NBRBJOB_POSN = 410403
AND SPRIDEN_CHANGE_IND IS NULL

[Updated on: Thu, 17 December 2009 07:38] by Moderator

Report message to a moderator

Re: If not in an oracle script [message #435693 is a reply to message #435692] Thu, 17 December 2009 07:39 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Your question makes absolutely no sense. Please provide a test case (create table and insert scripts) along with expected results and a logical explanation of the requirements.
Re: If not in an oracle script [message #435695 is a reply to message #435692] Thu, 17 December 2009 07:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think that you want an outer join between the two tables.
Re: If not in an oracle script [message #435696 is a reply to message #435693] Thu, 17 December 2009 07:48 Go to previous messageGo to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
Thanks for you response:

1. I am trying to get the fields that has data in the table SPRIDEN no matter that the select has no records in the second table NBRBJOB

FOR EXAMPLE:

for spriden x I have records in both tables and the script select the max value of NBRBJOB_SUFF but when no records in NBRBJOB the script has no data to fetch. I want to get the records of spriden if has no records in NBRBJOB. (sorry my english is not so good)
Re: If not in an oracle script [message #435697 is a reply to message #435692] Thu, 17 December 2009 07:48 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Try full outer join:
SELECT   B.Nbrbjob_pidm, B.Nbrbjob_suff, B.Nbrbjob_begin_date,
         B.Nbrbjob_end_date, Spriden_id
  FROM       Spriden
         FULL OUTER JOIN
             Nbrbjob B
         ON B.Nbrbjob_pidm = Spriden_pidm
 WHERE       B.Nbrbjob_suff = (SELECT   MAX (A.Nbrbjob_suff)
                                 FROM   Nbrbjob A
                                WHERE   A.Nbrbjob_pidm = B.Nbrbjob_pidm)
         AND Spriden_pidm = '59928'
         AND B.Nbrbjob_posn = 410403
         AND Spriden_change_ind IS NULL

Shocked
Re: If not in an oracle script [message #435698 is a reply to message #435696] Thu, 17 December 2009 07:52 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
sorry my english is not so good
Well, I speak no language other than English, so you are at least one up on me. It looks like others have understood you perfectly well (i.e. The outer join looks like a solution for you)
Re: If not in an oracle script [message #435699 is a reply to message #435697] Thu, 17 December 2009 07:53 Go to previous messageGo to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
People this is a Oracle Script
Re: If not in an oracle script [message #435700 is a reply to message #435699] Thu, 17 December 2009 07:54 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
wplaza wrote on Thu, 17 December 2009 13:53
People this is a Oracle Script

not sure of the significance of this statement. Do you believe that outer joins are not available in Oracle?
Re: If not in an oracle script [message #435701 is a reply to message #435697] Thu, 17 December 2009 07:58 Go to previous messageGo to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
I try your solution but remains in no data to fetch. Any other suggestion? Thanks.
Re: If not in an oracle script [message #435702 is a reply to message #435701] Thu, 17 December 2009 08:02 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Provide a test case. Provide the SQL that you tried, the results you got and what you were expecting (based on the test case that you provide)
Re: If not in an oracle script [message #435703 is a reply to message #435702] Thu, 17 December 2009 08:15 Go to previous messageGo to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
Please see the attached file
  • Attachment: TEST CASE.pdf
    (Size: 60.82KB, Downloaded 427 times)
Re: If not in an oracle script [message #435704 is a reply to message #435703] Thu, 17 December 2009 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Most of us can't or don't want to download a file
2/ A Test case is create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Thu, 17 December 2009 08:18]

Report message to a moderator

Re: If not in an oracle script [message #435705 is a reply to message #435697] Thu, 17 December 2009 08:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The op's request doesn't call for a full outer join.

Try this:
SELECT B.NBRBJOB_PIDM,
       B.NBRBJOB_SUFF,
       B.NBRBJOB_BEGIN_DATE,
       B.NBRBJOB_END_DATE,
       s.SPRIDEN_ID
FROM   NBRBJOB B, 
       SPRIDEN s
WHERE  NVL(B.NBRBJOB_SUFF,'X') = NVL(SELECT MAX(A.NBRBJOB_SUFF)
                                     FROM   NBRBJOB A
                                      WHERE A.NBRBJOB_PIDM = B.NBRBJOB_PIDM),'X')
AND    B.NBRBJOB_PIDM(+) = s.SPRIDEN_PIDM
AND    s.SPRIDEN_PIDM = '59928'
AND    B.NBRBJOB_POSN(+) = 410403
AND    s.SPRIDEN_CHANGE_IND IS NULL
Re: If not in an oracle script [message #435706 is a reply to message #435705] Thu, 17 December 2009 08:33 Go to previous messageGo to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
I got the following message:
Exception: DBD, ORA-00936: missing expression
State: N/A
Re: If not in an oracle script [message #435708 is a reply to message #435706] Thu, 17 December 2009 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The syntax is wrong, fix it.

Regards
Michel
Re: If not in an oracle script [message #435709 is a reply to message #435705] Thu, 17 December 2009 08:58 Go to previous messageGo to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
JRowbotton,

You know about virtual tables into the script?. I think that is the solution but I don't have experience with that.
Re: If not in an oracle script [message #435710 is a reply to message #435709] Thu, 17 December 2009 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>You know about virtual tables into the script?. I think that is the solution but I don't have experience with that.

Is virtual table similar to a virtual job & virtual paycheck?
Re: If not in an oracle script [message #435711 is a reply to message #435710] Thu, 17 December 2009 09:07 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
BlackSwan wrote on Thu, 17 December 2009 15:06
>You know about virtual tables into the script?. I think that is the solution but I don't have experience with that.

Is virtual table similar to a virtual job & virtual paycheck?

virtually
Re: If not in an oracle script [message #435712 is a reply to message #435709] Thu, 17 December 2009 09:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
You know about virtual tables into the script?.


Nope - never heard of them.

Cue Google....

Ahh - you mean an Inline View

Quote:
I think that is the solution but I don't have experience with that.


If you don't know much about them, then you're not in a great position to say whether or not they'll fix your problem.....

Here's a test case demonstrating how to do what you seem to be trying to do:
create table test_116 (value_col  number, sort_col  number,  join_col   number);

create table test_115 (col_1  number, join_col number);

insert into test_115 values (1,100);
insert into test_115 values (2,200);
insert into test_115 values (3,300);

insert into test_116 values (-1,1,100);
insert into test_116 values (-2,2,100);

insert into test_116 values (-10,2,200);
insert into test_116 values (-11,4,200);

select t115.col_1
      ,t116.value_col
      ,t116.sort_col
from   test_115 t115
      ,test_116 t116
where  t115.join_col = t116.join_col(+)
and    nvl(t116.sort_col,-999) = nvl((select max(t116a.sort_col)
                                      from   test_116  t116a
                                      where  t116a.join_col = t116.join_col),-999);

     COL_1  VALUE_COL   SORT_COL
---------- ---------- ----------
         1         -2          2
         2        -11          4
         3

Re: If not in an oracle script [message #435715 is a reply to message #435711] Thu, 17 December 2009 10:10 Go to previous messageGo to next message
wplaza
Messages: 9
Registered: December 2009
Junior Member
People, here the solution: Virtual Table: VPOSN

I hope it help you with your selects.

SELECT DISTINCT
B.NBRBJOB_PIDM,
SPRIDEN_ID,
SPRIDEN_LAST_NAME,
SPRIDEN_FIRST_NAME,
VPOSN.SUFF,
VPOSN.BEGIN_DATE,
VPOSN.END_DATE
FROM NBRBJOB B, SPRIDEN,
(select C.NBRBJOB_PIDM PIDM,
C.NBRBJOB_SUFF SUFF,
C.NBRBJOB_BEGIN_DATE BEGIN_DATE,
C.NBRBJOB_END_DATE END_DATE
FROM NBRBJOB C
WHERE C.NBRBJOB_POSN = '00000'
AND C.NBRBJOB_SUFF =
(SELECT MAX(A.NBRBJOB_SUFF)
FROM NBRBJOB A
WHERE A.NBRBJOB_PIDM = C.NBRBJOB_PIDM)) VPOSN
WHERE SPRIDEN_ID = 'xxxxxxxxxx'
AND SPRIDEN_CHANGE_IND IS NULL
AND B.NBRBJOB_PIDM = SPRIDEN_PIDM
AND B.NBRBJOB_SUFF =
(SELECT MAX(A.NBRBJOB_SUFF)
FROM NBRBJOB A
WHERE A.NBRBJOB_PIDM = B.NBRBJOB_PIDM)
AND VPOSN.PIDM(+) = SPRIDEN_PIDM
Re: If not in an oracle script [message #435718 is a reply to message #435715] Thu, 17 December 2009 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SELECT DISTINCT B.NBRBJOB_PIDM    ,
                SPRIDEN_ID        ,
                SPRIDEN_LAST_NAME ,
                SPRIDEN_FIRST_NAME,
                VPOSN.SUFF        ,
                VPOSN.BEGIN_DATE  ,
                VPOSN.END_DATE
FROM            NBRBJOB B                              ,
                SPRIDEN                                ,
                (SELECT C.NBRBJOB_PIDM       PIDM      ,
                        C.NBRBJOB_SUFF       SUFF      ,
                        C.NBRBJOB_BEGIN_DATE BEGIN_DATE,
                        C.NBRBJOB_END_DATE   END_DATE
                FROM    NBRBJOB C
                WHERE   C.NBRBJOB_POSN = '00000'
                AND     C.NBRBJOB_SUFF =
                        (SELECT MAX(A.NBRBJOB_SUFF)
                        FROM    NBRBJOB A
                        WHERE   A.NBRBJOB_PIDM = C.NBRBJOB_PIDM
                        )
                )
                VPOSN
WHERE           SPRIDEN_ID               = 'xxxxxxxxxx'
AND             SPRIDEN_CHANGE_IND IS NULL
AND             B.NBRBJOB_PIDM           = SPRIDEN_PIDM
AND             B.NBRBJOB_SUFF           =
                (SELECT MAX(A.NBRBJOB_SUFF)
                FROM    NBRBJOB A
                WHERE   A.NBRBJOB_PIDM = B.NBRBJOB_PIDM
                )
AND             VPOSN.PIDM(+) = SPRIDEN_PIDM
Re: If not in an oracle script [message #435720 is a reply to message #435715] Thu, 17 December 2009 10:29 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, among it (but not only) "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Set time parameter in date/time field
Next Topic: counting files
Goto Forum:
  


Current Time: Thu Sep 29 10:51:03 CDT 2016

Total time taken to generate the page: 0.10691 seconds