SQL syntax in a SQL Query Container Type - Issue with using Left / Right function

Hi,

I'm trying to create a report based on a SQL query tested in my SQL Management Studio.

When I'm creating a new Container and using the SQL Query type, I'm running into issues when I'm using this type of coding:

Select fieldname1, left(fieldname,4) as project number, Mid(fieldname,5,5) as ProjectNumber  from Tablename,

If I remove the Left or Mid Function everything works fine.

What is the best way to get over this hurdle? Detail Explanations would be great :)

Thanks.

Eric

Views: 285

Reply to This

Replies to This Discussion

Hi Eric.

LEFT and RIGHT work but MID doesn't. You can use SUBSTRING instead.  I tested the below example and it worked fine.

SELECT LEFT(IDCUST,2) AS CUSTCODE, SUBSTRING(IDINVC,2,2) AS INVTYPE, RIGHT(AUDTORG,3) AS ORGCODE FROM AROBL

Thanks Peter for your reply.

I forgot to mention I was working on a Sage 50 database. I don't know if that's the reason but your solution unfortunately didn't work :(

Any other solution you can think about?

Thanks again for you time Peter, greatly appreciated.

Eric

Hi Eric.

Sage 50 US runs on Pervasive, however I tested the below string and it worked fine. I'm not sure what the problem could be.

SELECT LEFT(JOBID,3) AS ABC, SUBSTRING(JOBID,2,2) AS BCD, RIGHT(JOBID,3) AS CDE FROM JOBS

Please post an image of the error you're getting.

Thanks Peter for your reply.

I'm actually using Sage 50 Canadian edition which is using a MySQL database instead. Still surprised that doesn't with that version...

I don't know if I have to use the other Container possibilities to be able to get what I want but that seems to be a lot of twist and turns for such a easy task...

EricG

I have a 50 CND image. I'll take a look and see what I find.

Thanks.

Here is the error I'm getting and the SQL Query I'm running.

SQL Query:

SELECT tsoline.lSOId, tsalordr.sSONum as OrderNumber, tsoline.nLineNum, tsalordr.lCurrncyId as Currency, tsalordr.dtSODate As OrderDate, tsalordr.nNetDay as Terms, tsoline.sPartCode as ItemNumber, tsoline.sDesc as ItemDesc, tsoline.dOrdered as QTYOrd, tsoline.dPrice as UnitPrice, tsoline.dAmount as Total, tsoline.lAcctId as GLNumber, left(tsolinep.lProjId,tproject.sName,4) AS ProjectNumber, SUBSTRING(tsolinep.lProjId,tproject.sName,5,47) AS ProjectNumber
FROM (((tsalordr INNER JOIN tsoline ON tsalordr.lId = tsoline.lSOId) LEFT JOIN tsolinep ON tsoline.lSOId = tsolinep.lSOId) LEFT JOIN tproject ON tsolinep.lProjId = tproject.lId)

Hi Peter, I tried a few things since my last reply. 

If I select only one table, I can use the Right, Left or MID function but if I try it inside a SQL query container with multiples tables, that's where I'm getting the error. Any idea how to get over that problem?

Thanks again.

Eric

Hi Peter,

Just to let you know that I was able to fix my problem.

Thanks,

Eric

Good to hear.

I see you were using multiple tables/fields in the functions which I don't think's allowed.

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service