Unexpected error when CTE used in query (1 Viewer)

[Galaxiom]

I have a CTE I want to use in a big query. The CTE works perfectly.

The big query works perfectly until I join in the simple subquery that uses the CTE.

Once combined, the big query returns two records then throws an error:

'Cannot convert a char value to money. The char value has incorrect syntax.'

The line referred to in the error is the first line of the CTE declaration: WITH ctename AS ( etc

There are no conversions from char to money in the CTE or the big query and no joins between char and money columns.

 

[Galaxiom]

Found it.

I had used COALESCE on a money column with a string as the Null replacement in the subquery that used the CTE.

 

[Minty]

I find CTE's quite confusing sometimes.
I think I still have only got about 60% of their "mojo" into my brain, and the other 40% is the clever bit, which I still haven't really grasped.

 

[Galaxiom]

I find CTE's quite confusing sometimes.
I think I still have only got about 60% of their "mojo" into my brain, and the other 40% is the clever bit, which I still haven't really grasped.

CTEs are like sub queries that only run once at the beginning of the main query with their results able to be subsequently used throughout. More or less like starting a sequence of queries in Access that begin with a make table query but without actually being written to the disk.

They are particularly useful when requiring consistent output that needs to be used multiple times from non-deterministic functions such as ROW_NUMBER().

 

[Minty]

@Galaxiom - That's one of the best descriptions I've come across, and actually clears up a good deal of the wooliness I had. Thank you.

I've used them quite a bit in recursive lookups in SQL server, and whilst I can always make them work, sometimes I'm not entirely certain of how I've made it work, that will help me a lot.