I would not recommend any type of waterfall at least for chameleons as far as my research and other cham keepers have said. Unless.. just like Jac1582 said, if you pretty much clean it out everyday to maintain it to prevent bacteria build up then it would be a great addition to a enclosure setup. I really wanted one too because it would make my setup look so much nicer, but I'll stay with the less maintenance setup to make it easier for me to take care of Kiddo and to ensure an excellent health.
Technically the waterfall can be "standing water" as water is consistently covering an area where the water travels.
I am new to the chameleon world, but I've done much research(still so much to learn) before bringing Kiddo my 9 weeks old male Veiled Chameleon home. Jac1582