troubleshooting Question

how to update the table where results are coming from select statement

Avatar of chaituu
chaituuFlag for India asked on
Oracle Database
3 Comments1 Solution159 ViewsLast Modified:
How to update GT_ADD_ISBNS table if CATEGORY_EXISTS=0 then error_mesage='category not found' , if FINAL_DOCUMENTS is not null then
finalename=FINAL_DOCUMENTS, and FINAL_DOCUMENTS is null then error_mesage='documents not found' ;

SELECT  (SELECT COUNT(*)
		FROM 
		TEMP_NODE_PROPERTIES ANP,
    TEMP_NODE_PROPERTIES ANPP,
		TEMP_NODE AN
		WHERE 
	  ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.TYPE_QNAME='asset' AND
    ANP.STRING_VALUE ='uPDF' AND ANPP.STRING_VALUE ='Entire PDF'  AND
    ANP.TYPE_QNAME='categories'  AND
 		ANP.NODE_ID IN(SELECT CHILD_ID 
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(GT.ISBN)) 
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID)
    )  CATEGORY_EXISTS,
    (SELECT LISTAGG(T1.STRING_VALUE, ',') WITHIN GROUP  (ORDER BY T1.STRING_VALUE)
       FROM 		TEMP_NODE_PROPERTIES T1
		WHERE   T1.TYPE_QNAME='name'  AND  (T1.STRING_VALUE LIKE '%pdf%'  AND  T1.STRING_VALUE NOT LIKE '%pod%') 
         AND  T1.NODE_ID IN (SELECT ANP.NODE_ID
		FROM 
		TEMP_NODE_PROPERTIES ANP,
    TEMP_NODE_PROPERTIES ANPP,
		TEMP_NODE AN
		WHERE 
	  ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.TYPE_QNAME='asset' AND
    ANP.STRING_VALUE ='uPDF' AND ANPP.STRING_VALUE ='Entire PDF' AND
    ANP.TYPE_QNAME='categories'  AND
 		ANP.NODE_ID IN(SELECT CHILD_ID 
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(GT.ISBN)) 
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID)))
     FINAL_DOCUMENTS
		FROM  GT_ADD_ISBNS GT;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros