CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
Kaylee Van De Feniks
Kaylee Van De Feniks

Reputation: 1

Need to query results of a CTE

I have a table that has call detail data. I've created a query to pull results based on language chosen (i.e. English, Spanish). This is accomplished by defining an AppLocationID (294 in this case) field with the language ID.

Is there a way to run a query of the returned results of the following query?

select Count(*),AppLocationID, EventPayload
from IVRCallDetail_TEST 
where AppLocationID = '294' 
and (EventPayload = 'English' or EventPayload = 'Spanish')
group by AppLocationID, EventPayload

Upvotes: 0

Views: 71

Answers (2)

Dance-Henry
Dance-Henry

Reputation: 953

The problem is with this CTE, the value of your EventPayLoad column is either "English" or "Spanish"

But in your SELECT statement after CTE, you specify (EventPayload = 'CP' or EventPayload = 'NCP'). However, the available values are only "English" and "Spanish". Definitely, it will return ZERO record to you.

Upvotes: 1

Joe Farrell
Joe Farrell

Reputation: 3542

Your CTE specifically queries records whose AppLocationID is '294', and your outer query selects records from the CTE whose AppLocationID is '29'. Since it's not possible for a record to satisfy both of these conditions, you get zero results.

Similarly, your CTE queries records whose EventPayload is 'English' or 'Spanish', and your outer query selects records from the CTE whose EventPayload is 'CP' or 'NCP', which is another impossibility.

If you can more carefully state exactly what you're trying to achieve, perhaps I (or someone else) can help you come up with a query that gets the job done.

Upvotes: 1

Related Questions