SSRS query builder, generated SELECT statement causing error
-
I'm working on a mysql query using the Visual Studio tableAdapter Query Builder, so far i'm getting this message (see image). Although the Query Builder executes the data given but cannot generate the columns needed in my dataset. I want to know what i'm doing wrong when my query works fine on my code. I'm trying to convert this query from mysql to sql-server without any good results
Here is the text code:
SELECT CONCAT(hgen.GENDESC, ',', CASE WHEN hdmhdr.brandname IS NULL THEN '' ELSE CONCAT(' ', hdmhdr.brandname) END, CASE WHEN hdmhdr.dmdnost IS NULL
THEN '' ELSE CONCAT(' ', CONVERT(hdmhdr.dmdnost,char(14))) END, CASE WHEN hstre.STREDESC IS NULL THEN '' ELSE hstre.STREDESC END,
CASE WHEN hform.FORMDESC IS NULL THEN '' ELSE CONCAT(' ', hform.FORMDESC) END, CASE WHEN hroute.rtedesc IS NULL THEN '' ELSE CONCAT(' ',
hroute.rtedesc) END) AS itemdesc, COUNT(DISTINCT hrxoissue.issuedte) AS 'Total Count per RX', SUM(hrxoissue.qty) AS 'Quantity', hrxoissue.dmdcomb,
hcharge.chrgdesc
FROM hdmhdr LEFT OUTER JOIN
hroute ON hdmhdr.rtecode = hroute.rtecode LEFT OUTER JOIN
hstre ON hdmhdr.strecode = hstre.strecode INNER JOIN
hdruggrp ON hdruggrp.grpcode = hdmhdr.grpcode INNER JOIN
hform ON hform.formcode = hdmhdr.formcode INNER JOIN
hgen ON hgen.gencode = hdruggrp.gencode INNER JOIN
hrxoissue ON hdmhdr.dmdcomb = hrxoissue.dmdcomb INNER JOIN
hcharge ON hrxoissue.chrgcode = hcharge.chrgcode
WHERE hdmhdr.dmdstat = 'A' AND hrxoissue.issuedte >= '2023-02-14 00:00:00' AND hrxoissue.issuedte <= '2023-02-14 23:59:00'
GROUP BY 1 -
I'm less familiar MySQL, and I also don't use the query builder (so maybe I shouldn't be trying to answer your question!) Anyway, I am looking at this from a T-SQL perspective... What stands out to me are the quotes around CHAR - whether you need them at all, and if you do whether they are the correct ones. In the screen shot above, I see CHAR surrounded by opening single quotes, whereas straight single quotes are what I commonly use and see. Sometimes they can creep in when copying and pasting code.
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
-
This looks like valid syntax for MySQL, but am I correct thinking you want this code to run against SQL Server? I created some tables to test the code and found a couple of syntax variations between MySQL and T-SQL.
- CONVERT requires data type first, then column. This seems to be the opposite of the MySQL convery function.
--THEN '' ELSE CONCAT(' ', CONVERT(hdmhdr.dmdnost, char(14)))
THEN '' ELSE CONCAT(' ', CONVERT(CHAR(14),hdmhdr.dmdnost))2) GROUP BY 1 does not work in T-SQL. I don't think you need to group by the entire itemdesc formula, but you do need to group by all the columns that are selected and not aggregated.
--GROUP BY 1
GROUP BY hgen.GENDESC, hdmhdr.brandname, hdmhdr.dmdnost, hstre.STREDESC,
hform.FORMDESC, hroute.rtedesc, hrxoissue.dmdcomb, hcharge.chrgdescI created some tables with made-up data types and this runs without error. I don't know what you have to do to make SSRS work. You may need to define the connection as SQL Server if the syntax is to be autogenerated.
SELECT CONCAT
(
hgen.GENDESC, ',',
CASE WHEN hdmhdr.brandname IS NULL THEN '' ELSE CONCAT(' ', hdmhdr.brandname) END,
CASE WHEN hdmhdr.dmdnost IS NULL THEN '' ELSE CONCAT(' ', CONVERT(CHAR(14),hdmhdr.dmdnost)) END,
CASE WHEN hstre.STREDESC IS NULL THEN '' ELSE hstre.STREDESC END,
CASE WHEN hform.FORMDESC IS NULL THEN '' ELSE CONCAT(' ', hform.FORMDESC) END,
CASE WHEN hroute.rtedesc IS NULL THEN '' ELSE CONCAT(' ', hroute.rtedesc) END
) AS itemdesc,
COUNT(DISTINCT hrxoissue.issuedte) AS 'Total Count per RX',
SUM(hrxoissue.qty) AS 'Quantity',
hrxoissue.dmdcomb,
hcharge.chrgdesc
FROM dbo.hdmhdr
LEFT OUTER JOIN dbo.hroute ON hdmhdr.rtecode = hroute.rtecode
LEFT OUTER JOIN dbo.hstre ON hdmhdr.strecode = hstre.strecode
INNER JOIN dbo.hdruggrp ON hdruggrp.grpcode = hdmhdr.grpcode
INNER JOIN dbo.hform ON hform.formcode = hdmhdr.formcode
INNER JOIN dbo.hgen ON hgen.gencode = hdruggrp.gencode
INNER JOIN dbo.hrxoissue ON hdmhdr.dmdcomb = hrxoissue.dmdcomb
INNER JOIN dbo.hcharge ON hrxoissue.chrgcode = hcharge.chrgcode
WHERE hdmhdr.dmdstat = 'A'
AND hrxoissue.issuedte >= '2023-02-14 00:00:00' AND hrxoissue.issuedte <= '2023-02-14 23:59:00'
GROUP BY hgen.GENDESC, hdmhdr.brandname, hdmhdr.dmdnost, hstre.STREDESC, hform.FORMDESC, hroute.rtedesc,
hrxoissue.dmdcomb, hcharge.chrgdesc
/*
CREATE TABLE dbo.hdmhdr
( formcode VARCHAR(10), strecode VARCHAR(10),
rtecode VARCHAR(10), grpcode VARCHAR(10), brandname VARCHAR(10),
dmdnost VARCHAR(10), dmdcomb VARCHAR(10) , dmdstat CHAR(1)
)
CREATE TABLE dbo.hstre(strecode VARCHAR(10), STREDESC VARCHAR(10))
CREATE TABLE dbo.hdruggrp (grpcode VARCHAR(10), gencode VARCHAR(10))
CREATE TABLE dbo.hform (formcode VARCHAR(10), FORMDESC VARCHAR(10))
CREATE TABLE dbo.hgen (gencode VARCHAR(10), GENDESC VARCHAR(10))
CREATE TABLE dbo.hrxoissue (issuedte DATETIME, qty INT, dmdcomb VARCHAR(10), chrgcode VARCHAR(10))
CREATE TABLE dbo.hcharge (chrgcode VARCHAR(10), chrgdesc VARCHAR(10))
CREATE TABLE dbo.hroute (rtecode VARCHAR(10), rtedesc VARCHAR(10))
*//*
DROP TABLE IF EXISTS hdmhdr
DROP TABLE IF EXISTS hstre
DROP TABLE IF EXISTS hdruggrp
DROP TABLE IF EXISTS hform
DROP TABLE IF EXISTS hgen
DROP TABLE IF EXISTS hrxoissue
DROP TABLE IF EXISTS hcharge
DROP TABLE IF EXISTS hroute
*/ -
Thanks for the response, I already followed your changes but still getting the error
I can't seem to find the line code where the 'END', ',' and ')' are not being parsed and unrecognized
-
Here is the latest Error that occurred:
-
MySQL <> MS SQL Server
Is the query source MySQL ( as per driver mysql.data)
or MSSQL Server?
As Ed B mentioned, there are some differences in syntax
-
I am not familiar with table adapters in SSRS. Do you have to specify a source database type? The error message mentions MySQL which suggests the source is MySQL, or the adapter is specific to MySQL.
If the database is MySQL then your original code looked OK, but maybe replace the complex case/concat statement with a single column to see if the it works in principle, and if it does, add back the case statement in pieces to gradually build the item_desc column.
It might help to CAST instead of CONVERT as the syntax is ansi standard (I think) and appears to be the same in MySQL and SQL Server
CAST(hdmhdr.dmdnost AS CHAR(14))
-
I found a workaround this time to bypass the parsing error message. I pasted my query from MYSQL workbench to SRSS query builder, omiting those errors and tried to preview the query to work this time. I changed the database provider to .NET Framework Data Provider for ODBC instead of .NET Framework Data Provider for MySQL.
although the error "unable to parse query text" still exist.
-
Now my rdlc report is only generating blank rows with multiple pages. I already set my Dataset in Datasources and completed each step process. I even tested it using preview data in DataSet and its working. Need to know what's still missing here.
The only unusual code line i saw is from the DataSet xml file under this line:
<Parameters>
<Parameter AllowDbNull="false" AutogeneratedName="" ColumnName="issuedte" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="@DMFrom" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="Stocknumber" SourceColumnNullMapping="false" SourceVersion="Current" />
<Parameter AllowDbNull="false" AutogeneratedName="" ColumnName="issuedte" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="@DMTo" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="Stocknumber" SourceColumnNullMapping="false" SourceVersion="Current" />
</Parameters>
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply