New Topic

Community Topic Question View Builder

Chris McCarthy

Yellowfin Support Team

Assigned

Category View Builder

Product Version 9.0

Incorrect syntax near the keyword 'WITH'.

Tiago shared this question 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

The same question

Best Answer

Chris McCarthy ●

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)

Chris McCarthy ●

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

Reply URL

Tiago ●

Hi Chris.

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

Thank you for the quick reply.

Best regards,

Tiago

URL

Chris McCarthy ●

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

URL

Tiago ●

Hi Chris.

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

Best regards,

Tiago marçal

URL

Chris McCarthy ●

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

URL

Tiago ●

Hi Chris.

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

Thank you.

Best Regards,

Tiago

URL

Tiago ●

Edited: Duplicated reply.

URL

Chris McCarthy ●

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

URL

Tiago ●

Hi Chris.

Yes, mark this ticket as complete.

Bets regards,

Tiago

URL

Chris McCarthy ●

Hi Tiago,

Great! Have an excellent weekend!

Kind regards,

Chris

URL

Robert Cha ●

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.

URL

gm masrty ●

level devil 2 is a fun online multiplayer game that combines strategy, puzzle-solving, and battle royale features.

URL

Lia Sana ●

bet2tech is one of the well-known providers that can really surprise experienced gambling enthusiasts by showing something completely unusual and exciting. Their games impress with bright visual effects, exciting bonus rounds and high payout rates. It also pays attention to the convenience of the interface and the smoothness of the gameplay so that each user can enjoy the gaming process without unnecessary complications.

URL