Helo All!
Got a problem. Is it possible to select fields from a subquery? I have seen different types of subqueries but I don't think i could use them.
I can not create views so it is out of question.
This is my query and it is supposed to be my subquery: -
-
SELECT POROW_AVSTEM.RUNDATE, POROW_AVSTEM.STOCKNO, POROW_AVSTEM.PONO, POROW_AVSTEM.POWDATE, POROW_AVSTEM.DELDATE, POROW_AVSTEM.VENDNO, POROW_AVSTEM.OTYPE, POROW_AVSTEM.ROWSTATUS, POROW_AVSTEM.POROWPOS, POROW_AVSTEM.PARTNO, POROW_AVSTEM.QTYPO, POROW_AVSTEM.QTYDEL, POROW_AVSTEM.POROWAMT, POROW_AVSTEM.POPRLCW, POROW_AVSTEM.CZPOCLAIMNO, POROW_AVSTEM.PODELTYPE, POROW_AVSTEM.INVREFNO, decode(extract(YEAR FROM DELDATE), '', extract(YEAR FROM POWDATE), extract(YEAR FROM DELDATE)) AS YEAR, decode(extract(MONTH FROM DELDATE), '', extract(MONTH FROM POWDATE), extract(MONTH FROM DELDATE)) AS MONTH, decode(CLASS4, 'G', 'ORD', 'FRUKT') AS FRUKT, decode(extract(MONTH FROM DELDATE), '', extract(MONTH FROM POWDATE), extract(MONTH FROM DELDATE)) || '.' || decode(extract(YEAR FROM DELDATE), '', extract(YEAR FROM POWDATE), extract(YEAR FROM DELDATE)) AS MONTHYEAR, STOCK.STOCKNAME, decode(OTYPE, '20', 'Retur', decode(ROWSTATUS, 130, 'Reklamasjon', 'Uavstemte Varer')) AS TYPE, decode(DELDATE, '', POWDATE, DELDATE) AS DATO, OTYPE || '-' || ROWSTATUS AS STATUS
-
FROM (POROW_AVSTEM INNER JOIN PART ON POROW_AVSTEM.PARTNO=PART.PARTNO) INNER JOIN STOCK ON POROW_AVSTEM.STOCKNO=STOCK.STOCKNO
-
GROUP BY POROW_AVSTEM.RUNDATE, POROW_AVSTEM.STOCKNO, POROW_AVSTEM.PONO, POROW_AVSTEM.POWDATE, POROW_AVSTEM.DELDATE, POROW_AVSTEM.VENDNO, POROW_AVSTEM.OTYPE, POROW_AVSTEM.ROWSTATUS, POROW_AVSTEM.POROWPOS, POROW_AVSTEM.PARTNO, POROW_AVSTEM.QTYPO, POROW_AVSTEM.QTYDEL, POROW_AVSTEM.POROWAMT, POROW_AVSTEM.POPRLCW, POROW_AVSTEM.CZPOCLAIMNO, POROW_AVSTEM.PODELTYPE, POROW_AVSTEM.INVREFNO, decode(extract(YEAR FROM DELDATE), '', extract(YEAR FROM POWDATE), extract(YEAR FROM DELDATE)), decode(extract(MONTH FROM DELDATE), '', extract(MONTH FROM POWDATE), extract(MONTH FROM DELDATE)), decode(CLASS4, 'G', 'ORD', 'FRUKT'), decode(extract(MONTH FROM DELDATE), '', extract(MONTH FROM POWDATE), extract(MONTH FROM DELDATE)) || '.' || decode(extract(YEAR FROM DELDATE), '', extract(YEAR FROM POWDATE), extract(YEAR FROM DELDATE)), STOCK.STOCKNAME, decode(OTYPE, '20', 'Retur', decode(ROWSTATUS, 130, 'Reklamasjon', 'Uavstemte Varer')), decode(DELDATE, '', POWDATE, DELDATE), OTYPE || '-' || ROWSTATUS;
-
Thx in advance!!!!!
|
