Applying 2 conditions in a pass through

[TheNovice]

hi all, 

can't seem to figure this out. I have a table  A of ID's and ID_Date and I need to extract records from table B 

by matching ID where ID_Date >= Table B_Date

date in table A is in date format and date in table b is in datetime format

proc sql;
 CONNECT TO ORACLE (user=&name  pw=&pass  path=Exa);
 create table P_1 as
  select * from connection to oracle
  (select ID,SEQ_NO,Tableb_DATE,AMT
   from  p_act
       )
   

( WHERE ID IN (SELECT  ID FROM table a) 

and ID_date > tableb_Date )
    ;
 disconnect from oracle;
quit;

I keep getting an error. not sure how to go about this...

[tomrvincent]

What's the error?
I'd add a distinct to that ID select and maybe a trunc of tableb_date.

[TheNovice]

 I need all the records so i can sum the amt later

Changed it to this:

proc sql;
 CONNECT TO ORACLE (user=&name  pw=&pass  path=Exa);
 create table P_1 as
  select * from connection to oracle
  (select ID,SEQ_NO,Tableb_DATE,AMT
   from  p_act
       )
  
( WHERE ID IN (SELECT  ID FROM table a)
and ID_date > trunc(tableb_Date),'DD-Mon'YY' )
    ;
 disconnect from oracle;
quit;
 
error i get now is :

                                       22
                                               200
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
              GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,
              ~=. 

ERROR 200-322: The symbol is not recognized and will be ignored.

[tomrvincent]

you've got several errors there. Try this instead:

( WHERE ID IN (SELECT DISTINCT ID FROM table a)
and ID_date > trunc(tableb_Date)

[tomrvincent]

I missed a paren, but also you have nothing that joins a and p_act...both Oracle tables?

[TheNovice]

Thanks Tom,

Same error. I did notice the missing parenthesis and corrected it.

                         _
                                                22
                                                200
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
              GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,
              ~=. 

ERROR 200-322: The symbol is not recognized and will be ignored.

Table a is not an oracle table. it's a table i have created. I am not very good at nested arguments yet so i used this method instead.

[tomrvincent · Accepted solution]

Okay...so you need to do the oracle stuff all together and then join the results to a.

proc sql;
 CONNECT TO ORACLE (user=&name  pw=&pass  path=Exa);
 create table P_1 as
  select p1.*,a.* from connection to oracle
  (select ID,SEQ_NO,Tableb_DATE,trunc(tableb_Date) as p1date,AMT
   from  p_act
       ) p1
   left join a on p1.id=a.id and a.id_date > p1date
    ;
 disconnect from oracle;
quit;

[TheNovice]

thank you!

[hashman]

@TheNovice: From what I see here, you have an unbalanced single quote.