Відео

Comparing null in case: Select case when null is null then 0 else 1 end from dual Answer is 0.

WITH ABC AS (SELECT QUOTE_ID , LISTAGG(ORDER_STATUS,',') WITHIN GROUP ( ORDER BY ORDER_STATUS) STATUS, COUNT(*) TOTAL FROM ORDERS GROUP BY QUOTE_ID) SELECT QUOTE_ID, CASE WHEN REGEXP_COUNT(STATUS,'DELIVERED') = TOTAL THEN 'COMPLETE' WHEN REGEXP_COUNT(STATUS,'DELIVERED') >=1 THEN 'IN DELIVERY' WHEN REGEXP_COUNT(STATUS,'SUBMITTED') >=1 THEN 'AWAITING FOR SUBMISSION' ELSE 'AWAITING FOR ENTRY' END AS ORDER_STATE FROM ABC; WITH ABC AS (SELECT QUOTE_ID, (SELECT COUNT(*) FROM ORDERS B WHERE A.QUOTE_ID = B.QUOTE_ID AND B.ORDER_STATUS = 'DELIVERED') DELIVER, (SELECT COUNT(*) FROM ORDERS B WHERE A.QUOTE_ID = B.QUOTE_ID AND B.ORDER_STATUS = 'SUBMITTED') SUBMIT, (SELECT COUNT(*) FROM ORDERS B WHERE A.QUOTE_ID = B.QUOTE_ID AND B.ORDER_STATUS = 'CREATED') CREATETED, COUNT(*) TOTAL FROM ORDERS A GROUP BY QUOTE_ID) SELECT QUOTE_ID , CASE WHEN TOTAL = DELIVER THEN 'COMPLETE' WHEN DELIVER >=1 THEN 'IN DELIVERY' WHEN SUBMIT>=1 THEN 'AWAITING FOR SUBMISSION' ELSE 'AWAITING FOR ENTRY' END ORDER_STATUS FROM ABC;

concept got cleared ❤thnks

C

C

Count (0) ?

👍

👍

wasn't aware of that function ....thanks for the information

very informative ..thanks

Very Informative

Thanks for the vital information

Excellent

Thanks for the information in such easy to understand when you teach

Thanks

very Helpful ...thanks for sharing.

gracias me sirvió mucho

Keep continue using WITH

Supper

Thansk. This exactly what I wa s looking for. Keep posting new videos for other plsql scenarios.

"promosm"

Do more interview questions and and you giving nice explanation for us ❤️

Is it possible to use for loop in it and display record in table

This table gives 0 record output

SELECT ID,CASE WHEN DEL>=3 THEN 'Complete' WHEN DEL>=1 AND SUB>=1 THEN 'In Delivery' WHEN SUB>=1 AND CRE>=1 THEN 'Pen Submit' WHEN SUB=0 AND CRE>=1 THEN 'In Entry' END sts FROM( SELECT * FROM SCHK PIVOT ( Count(STATUS) FOR STATUS IN ( 'DELIVERED' "DEL",'SUBMITTED' "SUB",'CREATED' "CRE") ) ORDER BY ID)

Complete

Good explanation, thank you

select QUOTE_ID, case when DELIVERED_COUNT = ORDER_COUNT then 'COMPLETE' when DELIVERED_COUNT > =1 then 'IN DELIVERY' when SUBMITTED_COUNT > = 1 then 'AWAITING FOR SUBMISSION' else 'AWAITING FOR ENTRY' end status from (SELECT QUOTE_ID, COUNT (ORDER_ID) order_count, SUM (CASE WHEN ORDER_STATUS = 'DELIVERED' THEN 1 ELSE 0 END) DELIVERED_COUNT, SUM (CASE WHEN ORDER_STATUS = 'SUBMITTED' THEN 1 ELSE 0 END) SUBMITTED_COUNT, SUM (CASE WHEN ORDER_STATUS = 'CREATED' THEN 1 ELSE 0 END) CREATED_COUNT FROM order_status GROUP BY QUOTE_ID )

Ho to use with INSERT ALL log errors into OBJECT_NAME reject limit unlimited;

Thank you for helping us

Super and well explanation 👍

Great man, I can say wow , simply superb.

Good information sir jee

Great question bro.. Literally I was searching this question 2 years back but not able to find satisfactory explaination... Thanks a lot..

Thank you very much sir ,I learned very important and useful information from this vedio.

something new to learn 👍

Clickbait

Well explained...I was asked this question recently in an interview...

Thank you from Guatemala Central America . It was very usefull to me because I coudn't use split, if you can a split video could be great

Great 👍 and upload more ...

Nice bro

Nice question. Thank you.

-->> PROBLEM STATEMENT: Ungroup the given input data. Display the result as per expected output id item_name total_count 1 Water Bottle 2 2 Tent 1 3 Apple 4 -->> EXPECTED OUTPUT: ID ITEM_NAME 1 Water Bottle 1 Water Bottle 2 Tent 3 Apple 3 Apple 3 Apple 3 Apple

Very good concept, ,try more

Please place the query in comment section, video is not clear

Brilliant 👏

thank you sir , your videos are realy helpful... please make a video on SQL and plsql important pre defined error ...some time as a beginner I don't understand what is the meaning of that particular error.

Hey, thank you for sharing! Helps me a lot! Are you in audea?

Thanks buddy