Apologies for the complete blunt question.

I have created Countries_View and placed the problematic CASE within the view and then JOINT Cities A table with Countries_View B

and all works like a...

Thank you, Sean

It comes back as VARCHAR for a DataType column

Thank you, Ed

This is an issue - I am not allowed to publish any code for that specific customer.

This is why I am asking for guidelines only, as I...

Forcing an index improved the performance from 03:55 to 01:46 only.

Anything else you could advise, please?

Thank you!

I have looked into the actual execution plan (I am not sure I can publish it here due to company's security policy).

What I have seen that with CASE it...

mister.magoo (7/1/2014)

---

You can use a recursive CTE to generate a relationship table:

(Note: I could not get this to work with your schema collection though, for some reason it complains about...

Thank for the reply. I am getting "XQuery [value()]: Heterogeneous sequences are not allowed in '=', found 'xdt:anyAtomicType' and 'xs:unsignedByte'." when I try it.

That said, the requirement has been extended...

Thank you.

From what I have seen, they end up with a same processing, meaning you need to know what level to look: SELECT @x.value('employees[1]/emp[2]/phone[1]','VARCHAR(20)') AS Phone

Agree - my bad. It should be read "that problematic DB of mine had a compatibility set back to 90 (2005)"

Thanks, but found the issue already - that problematic DB of mine had a compatibility set back to 80 (2005). Once changed, all is working.

Thanks again.

Something like this?

select TOP 1 t.* from #return_schedule t

where COALESCE(t.col_val,0)=0

AND DATEDIFF(minute, t.time_sched_end_dt,t.time_sched_start_dt)>= 60

ORDER BY t.time_sched_start_dt ASC, t.time_sched_end_dt ASC

Still not with you. Is it something you've been looking for?

WITH myEmp AS (

SELECT 1 AS empID,

'John' AS empName

UNION ALL

SELECT 2,

'Stephen'),

myAudit AS (

SELECT 1 AS auditID,

...

Not sure I understood what you want to achieve. Is it OK to ask you to show before / after / what would you like to have by the end...

Thanks for looking!

I think after giving it a proper thought, the solution was easy after all:

WITH currencies AS (

SELECT 'AED' AS Currency,

0.454438 AS BaseToHedgeRate,

0 AS BaseCurrency

UNION...

Hi Lowell

Thank you for the quick reply, I didn't expect a solution, but guidelines what to do.

Thanks again!