SELECT CASE
WHEN ROW_NUMBER ()
OVER (PARTITION BY C.SUPPLIER_ID ORDER BY C.SUPPLIER_ID) =1
THEN C.SUPPLIER_NAME || '(' || C.SUPPLIER_ID || ')' ELSE NULL END SUPPLIER_NAME,D.PRODUCT_NAME,B.QTY,B.BUY_PRICE,NVL(B.BUY_PRICE,0)*NVL(B.QTY,0) Amount
FROM PURCHASE_MASTER A, PURCHASE_DETAILS B,SUPPLIERS C,PRODUCTS D
WHERE A.BILL_NO=B.BILL_NO
AND A.SUPPLIER_ID=C.SUPPLIER_ID
AND B.PRODUCT_ID=D.PRODUCT_ID
AND A.SUPPLIER_ID LIKE NVL(:P51_SUPPLIER_ID,'%')
ORDER BY C.SUPPLIER_ID;
========================================
SELECT CASE WHEN ROW_NUMBER () OVER (PARTITION BY D.DEPARTMENT_ID ORDER BY D.DEPARTMENT_ID) =1 THEN D.DEPARTMENT_NAME || '(' || D.DEPARTMENT_ID || ')' ELSE NULL END DEPARTMENT_NAME, CASE WHEN ROW_NUMBER () OVER(PARTITION BY D.DEPARTMENT_ID ORDER BY D.DEPARTMENT_ID) =1 THEN E.SALARY ELSE NULL END DEPT_SAL, E.FIRST_NAME||' '||E.LAST_NAME EMP_NAME,E.SALARY FROM EMPLOYEES E,DEPARTMENTS D WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID; ==================================================
SELECT CASE WHEN ROW_NUMBER () OVER (PARTITION BY D.DEPARTMENT_ID ORDER BY D.DEPARTMENT_ID) =1 THEN D.DEPARTMENT_NAME || '(' || D.DEPARTMENT_ID || ')' ELSE NULL END DEPARTMENT_NAME, E.FIRST_NAME||' '||E.LAST_NAME EMP_NAME,E.SALARY FROM EMPLOYEES E,DEPARTMENTS D WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;