I want to join two queries to create a new query which will have all the information

[ANILESWAR]

Below is the details of two queries and sql behind it and i'm doing this in Peoplesoft.Using Psquery.
Any help or procedure greatly appreciated.

IIGL_DEPT_AP_BVA_DETAIL:

SQL:

SELECT A.BUSINESS_UNIT, A.ACCOUNT, E.DESCR, A.LINE_DESCR, D.NAME1, F.INVOICE_ID, TO_CHAR(F.INVOICE_DT,'YYYY-MM-DD'), C.MONETARY_AMOUNT, A.DEPTID, B.DESCR, A.JRNL_LINE_SOURCE, A.CURRENCY_CD, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD'), A.JOURNAL_ID,E.SETID,E.ACCOUNT,TO_CHAR(E.EFFDT,'YYYY-MM-DD') 
  FROM PS_JRNL_LN A, PS_SP_BU_GL_OPRVW A1, PS_JRNL_HEADER B, PS_VCHR_ACCTG_LINE C, PS_SP_BU_AP_OPRVW C1, PS_PYMNT_XREF_VW F, PS_PAYMENT_TBL D, PS_GL_ACCOUNT_TBL E 
  WHERE A.BUSINESS_UNIT = A1.BUSINESS_UNIT 
    AND A1.OPRID = 'CHAYNES' 
    AND B.BUSINESS_UNIT = A1.BUSINESS_UNIT 
    AND C.BUSINESS_UNIT = C1.BUSINESS_UNIT 
    AND C1.OPRID = 'CHAYNES' 
    AND ( A.LEDGER = 'ACTUALS' 
     AND A.DEPTID = :1 
     AND A.JOURNAL_DATE BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD') 
     AND A.BUSINESS_UNIT = B.BUSINESS_UNIT 
     AND A.JOURNAL_ID = B.JOURNAL_ID 
     AND A.JOURNAL_DATE = B.JOURNAL_DATE 
     AND ( A.JRNL_LINE_SOURCE = 'GAP' 
     AND A.BUSINESS_UNIT =  C.BUSINESS_UNIT_GL(+) 
     AND A.JOURNAL_ID =  C.JOURNAL_ID(+) 
     AND A.JOURNAL_DATE =  C.JOURNAL_DATE(+) 
     AND A.JOURNAL_LINE =  C.JOURNAL_LINE(+) 
     AND A.LEDGER =  C.LEDGER(+) 
     AND B.JRNL_HDR_STATUS = 'P' 
     AND C.BUSINESS_UNIT = F.BUSINESS_UNIT 
     AND C.VOUCHER_ID = F.VOUCHER_ID 
     AND D.REMIT_VENDOR = F.VENDOR_ID) 
     AND F.REMIT_SETID = D.REMIT_SETID 
     AND F.REMIT_VENDOR = D.REMIT_VENDOR 
     AND F.BANK_SETID = D.BANK_SETID 
     AND F.BANK_CD = D.BANK_CD 
     AND F.BANK_ACCT_KEY = D.BANK_ACCT_KEY 
     AND F.PYMNT_ID_REF = D.PYMNT_ID_REF 
     AND F.PYMNT_ID = D.PYMNT_ID 
     AND D.PYMNT_STATUS IN ('N','P') 
     AND A.BUSINESS_UNIT = '21000' 
     AND E.ACCOUNT = A.ACCOUNT 
     AND E.EFFDT = 
        (SELECT MAX(E_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL E_ED 
        WHERE E.SETID = E_ED.SETID 
          AND E.ACCOUNT = E_ED.ACCOUNT 
          AND E_ED.EFFDT <= SYSDATE) 
     AND A.CURRENCY_CD = 'USD' ) 
  ORDER BY 11, 14, 2

RECORDS:

    AJRNL_LN - Journal Line Data
 
    BJRNL_HEADER - Journal Header Data
 
    C VCHR_ACCTG_LINE - AP Accounting Entries
 
    F PYMNT_XREF_VW - AP Payment Voucher Information
 
        D PAYMENT_TBL - AP Disbursements
 
        E GL_ACCOUNT_TBL - Accounts

IIGL_DEPT_BVA_DETAILS:

SQL:

SELECT A.BUSINESS_UNIT, A.JOURNAL_ID, TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD'), A.ACCOUNT, A.CURRENCY_CD, A.MONETARY_AMOUNT, A.LINE_DESCR, A.JRNL_LINE_SOURCE, A.DEPTID, B.DESCR 
  FROM PS_JRNL_LN A, PS_SP_BU_GL_OPRVW A1, PS_JRNL_HEADER B 
  WHERE A.BUSINESS_UNIT = A1.BUSINESS_UNIT 
    AND A1.OPRID = 'CHAYNES' 
    AND B.BUSINESS_UNIT = A1.BUSINESS_UNIT 
    AND ( A.LEDGER = 'ACTUALS' 
     AND A.DEPTID = :1 
     AND A.JOURNAL_DATE BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD') 
     AND A.BUSINESS_UNIT = B.BUSINESS_UNIT 
     AND A.JOURNAL_ID = B.JOURNAL_ID 
     AND A.JOURNAL_DATE = B.JOURNAL_DATE 
     AND A.JRNL_LINE_SOURCE NOT IN ('TWL') 
     AND B.JRNL_HDR_STATUS = 'P' 
     AND A.BUSINESS_UNIT = '21000' 
     AND A.ACCOUNT > '499999' 
     AND B.SOURCE <> 'CLO' ) 
  ORDER BY 4, 8, 3

RECORDS:

        A JRNL_LN - Journal Line Data
 
        B JRNL_HEADER - Journal Header Data

Thanks

[ck9663]

Aside from AND A.DEPTID = :1 , everything seems to be just an INNER join. I don't know what those date functions are, though. And that (+) after the column. Other than that, most of those syntax will work in MS SQL, unless I miss something.

Happy Coding!!!

~~ CK

[ANILESWAR]

Thanks CK.... for your response
I tried inner and outer joint but didn't get it may be missing some expression,don't know

[ck9663]

Can you post what your MS SQL query looks like?

~~ CK

[ANILESWAR]

Hi Ck,
I have sql query only since i,m a functional consultant idon't have access to database.
Above mentioned are Sql queries.

Thanks for your response and contribution.

[ck9663]

What does those colon means?


~~ CK