Incorrect syntax near the keyword 'WITH'.

Tiago shared this question 9 months ago
Completed

Hi.

I have an SQL query using keyword WITH.

If I use this query to create a view, Yellowfin gives the error used for this title. However, if I use the same query to create a report or a Transformation Flow, Yellowfin says the query is valid.

Can you help me understand the problem?

Thank you.

The query is something like this (name of the tables and names of the attributes were modified):

WITH D AS (
		select
		no,
		de,
		convert(datetime, date1, 103) as ate1,
		convert(datetime, date2, 103) as ate2,
		first,
		last,
		ti
		from table1),

PWS AS (
		select
		erId,
		onId,
		er,
		te,
		ode,
		tion,
		nd
		from table2),

L AS (
		select
		id,
		ice
		from table3),

SE AS (
		select
		eid,
		id,
		ate
		from table4),

GSE AS (
		select
		ueId,
		rade
		from table5)

select top (100)
D.no,
D.de,
D.ate1,
D.ate2,
D.first,
D.last,
D.ti,
PWS.te,
PWS.ode,
PWS.tion,
L.ice,
SE.ate,
GSE.rade
from D
left join PWS on PWS.er = D.no COLLATE Latin1_General_CI_AS
left join L on L.id = PWS.onId
left join SE on SE.id = PWS.erId
left join GSE on GSE.ueId = SE.eId
where	PWS.te not like '%something1%' and
		PWS.nd = 'something2' and
		D.ate2 is not null and
		CONVERT(datetime, D.ate2, 103) >= CONVERT(DATETIME, '01-01-1960 00:00:00', 103) and
		PWS.te not like '%aomething3%'
order by D.ate2 desc

Best Answer
photo

Hi Tiago,

No worries!

If there's nothing else I'll mark this ticket as complete and we can track progress of the enhancement from the other post.

Let me know.

Kind regards,

Chris

Replies (1)

photo
1

Hi Tiago,

Thanks for contacting Yellowfin. As you're posting from an @gmail address, unfortunately it hasn't filled in which organisation you work for. Can you let me know?

Bear in mind that CTEs like the WITH statement are not supported in freehand SQL views at this time, but it may be implemented at a later date.

Kind regards,

Chris

photo
1

Hi Chris.

I work for Blueworks. Should I create an account with my professional email?

Thank you for the quick reply.

Best regards,

Tiago

photo
1

Hi Tiago,

Thanks very much!

I can't find Blueworks under our list here, are you a new customer or perhaps working with one of our partners?

Kind regards,

Chris

photo
1

Hi Chris.

We are a new customer, and purchased our first license to Yellowfin less than 2 months ago.

Best regards,

Tiago marçal

photo
1

Hi Tiago,

Thanks very much!

So we do have an open Idea request (what we also call an Enhancement request) to see this feature added to Yellowfin, but I can see it's been some time since it was last looked at. Please could you have a look at the post and check if it fits the goal of what you're trying to achieve here. If so, please could you give it a thumbs up.

I'll see if I can follow up on the request internally and get some feedback and hopefully push it along.

Welcome to Yellowfin! I hope it's all going okay. If you have any other questions, please don't hesitate to ask!

Kind regards,

Chris

photo
1

Hi Chris.

I think you are right. I thumbs up the request.

Thank you.

Best Regards,

Tiago

photo
1

Edited: Duplicated reply.

photo
1

Hi Tiago,

No worries!

If there's nothing else I'll mark this ticket as complete and we can track progress of the enhancement from the other post.

Let me know.

Kind regards,

Chris

photo
1

Hi Chris.

Yes, mark this ticket as complete.

Bets regards,

Tiago

photo
1

Hi Tiago,

Great! Have an excellent weekend!

Kind regards,

Chris

photo
1

Staying healthy is one of my top priorities, and I’m always looking for ways to be more proactive. I found out about a cancer detection test that can identify issues before symptoms even appear. This is such a valuable tool for anyone who wants to stay ahead of their health. Here’s the link: https://ways2well.com/product/cancer-detection-test. I’m definitely thinking about adding this to my routine, especially since early detection is key.

photo
Leave a Comment
 
Attach a file