Convert Crosstab Query to OLEDB SQL
I'm having trouble with the syntax of a particular query I'm trying to convert from MS Access to a SQL string I'm running in VB.NET. (I've looked at all the "weird" things I normally run into like putting brackets around field and table names, replacing Access wildcard * with SQL wildcard %, etc) but I'm still not getting the results I expect.
This query, WHICH I PULLED DIRECTLY FROM THE VARIABLE I BUILT TO HOLD THE STRING
Gives me these results when I copy the above into an Access SQL query builder
Return when running via Access Query builder - THESE NUMBERS ARE CORRECT
ZONE Case Type Proc/Service I O
201 Physician Admit-Attend-Disch-Consult
201 Physician Admit-Attend-Disch-Consult
However, when I execute the query in my VB.NET script, I get the following:
ZONE Case Type Proc/Service I O
201 Physician Admit-Attend-Disch-Consult
201 Physician Admit-Attend-Disch-Consult
Note that the "O" (Outpatient) column is different.
Hoping someone sees something I've missed in the attached VB Sub
This query, WHICH I PULLED DIRECTLY FROM THE VARIABLE I BUILT TO HOLD THE STRING
TRANSFORM Sum([LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Count]) AS [Count] SELECT [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[ZONE], [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Case Type], IIf([Procedure/Service]="IP ONLY UNGROUPABLE",[Procedure/Physician],IIf([Procedure/Service]="Ungroupable",[Procedure/Physician],IIf([Procedure/Service] Is Null,[Procedure/Physician],[Procedure/Service]))) AS [Proc/Service] FROM [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA] LEFT JOIN [CROSSREF: Service Line OP Mapping] ON [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Procedure/Phys Code] = [CROSSREF: Service Line OP Mapping].[Procedure Code] WHERE ((([LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[ZONE]) = 201) And (([LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Month]) > #4/30/2010#)) GROUP BY [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[ZONE], [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Case Type], IIf([Procedure/Service]="IP ONLY UNGROUPABLE",[Procedure/Physician],IIf([Procedure/Service]="Ungroupable",[Procedure/Physician],IIf([Procedure/Service] Is Null,[Procedure/Physician],[Procedure/Service]))) ORDER BY [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[ZONE] DESC , [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Case Type], [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[I_O] PIVOT [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[I_O];Gives me these results when I copy the above into an Access SQL query builder
Return when running via Access Query builder - THESE NUMBERS ARE CORRECT
ZONE Case Type Proc/Service I O
201 Physician Admit-Attend-Disch-Consult
201 Physician Admit-Attend-Disch-Consult
However, when I execute the query in my VB.NET script, I get the following:
ZONE Case Type Proc/Service I O
201 Physician Admit-Attend-Disch-Consult
201 Physician Admit-Attend-Disch-Consult
Note that the "O" (Outpatient) column is different.
Hoping someone sees something I've missed in the attached VB Sub
strsql = "TRANSFORM Sum([LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Count]) AS [Count]" & _
" SELECT [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[ZONE], [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Case Type], IIf([Procedure/Service]=""IP ONLY UNGROUPABLE"",[Procedure/Physician],IIf([Procedure/Service]=""Ungroupable"",[Procedure/Physician],IIf([Procedure/Service] Is Null,[Procedure/Physician],[Procedure/Service]))) AS [Proc/Service]" & _
" FROM [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA] LEFT JOIN [CROSSREF: Service Line OP Mapping] ON [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Procedure/Phys Code] = [CROSSREF: Service Line OP Mapping].[Procedure Code]" & _
" WHERE ((([LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[ZONE]) = 201) And (([LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Month]) > #4/30/2010#))" & _
" GROUP BY [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[ZONE], [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Case Type], IIf([Procedure/Service]=""IP ONLY UNGROUPABLE"",[Procedure/Physician],IIf([Procedure/Service]=""Ungroupable"",[Procedure/Physician],IIf([Procedure/Service] Is Null,[Procedure/Physician],[Procedure/Service])))" & _
" ORDER BY [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[ZONE] DESC , [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[Case Type], [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[I_O]" & _
" PIVOT [LORENDA - REPORT-ALL: Summary - Run for other queries - CRITERIA].[I_O];"
'Open the query above into a recordset
rst.Open strsql, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic
'copy the query results into the spreadsheet starting at row 17 (leaving all the header information)
ws.Range("A17").CopyFromRecordset rst
JimFive
I don't see any differences so I wonder if the Access Date format (#4/30/2010#) is the problem. Maybe try CDate(""4/30/2010"").
ASKER
Thanks for the response. Unfortunately, this change produced a type mismatch error
What is the data type of the Month field?
ASKER
It's a date field.
ASKER
I'm sorry - I completely missed the cdate function. I tried this, but now I'm receiving the following error:
The CDate needs to be inside the query so you need to adjust your quoting that it says
Run for other queries - CRITERIA].[Month]) > CDATE("4/30/2010")
Run for other queries - CRITERIA].[Month]) > CDATE("4/30/2010")
T-SQL uses a single quote to enclose data values so the date would be entered as:
'4/30/2010'
'4/30/2010'
ASKER
This is my VBA.NET snippet for the above error.
"Run for other queries - CRITERIA].[Month]) > " & CDate("4/30/2010") & "))" & _
"Run for other queries - CRITERIA].[Month]) > " & CDate("4/30/2010") & "))" & _
You are using double quotes rather than single and I'm not sure that CDate() is a valid T-SQL function. I don't think it is.
Why not build the query using SSMS and then copy and paste it into your VB.Net app?
Why not build the query using SSMS and then copy and paste it into your VB.Net app?
ASKER
Sorry, what is SSMS?
ASKER
I'm pretty confident the issue isn't the date - I have several select statements with this same date criteria, and they are working properly. The only difference between those and this one is that this is a crosstab query.
ASKER
I don't suppose anyone would be willing to help me convert this crosstab into SQL Server-friendly SQL? I'm thinking maybe it might play better with .net. I've never tried to use this syntax, so I'm not sure what goes where.
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
I haven't read the whole thread yet, but some answers.....
Sorry, what is SSMS?
That is SQL Server Management Studio
I'm not sure that CDate() is a valid T-SQL function. I don't think it is.
CDate is an old JET to convert a string to a valid date. The T-SQL equivalent is Cast and Convert.
Since I haven't read the thread I can't tell where it is falling down but did want to throw in some hints of where to look.
Sorry, what is SSMS?
That is SQL Server Management Studio
I'm not sure that CDate() is a valid T-SQL function. I don't think it is.
CDate is an old JET to convert a string to a valid date. The T-SQL equivalent is Cast and Convert.
Since I haven't read the thread I can't tell where it is falling down but did want to throw in some hints of where to look.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you figured out what is SSMS, yet?
ASKER
Yes. Jimpen answered it above. SQL Server Management Studio. (Thanks, Jimpen!)
That is good. So go ahead and install it and then write the query using it. Look up the T-SQL syntax for the PIVOT clause.
Can you try this as a a prebuilt view in SQL or maybe as a stored procedure?
ASKER
Jim - all of this is being built in Access...I need it in Access, so pre-building views/stored procedures and the like are off the table. I'm going to go ahead and close this thread...I'm not seeing any answers that address MY issue any better than my workaround. Thank you all!
If you run the query in the Access database, does it work?
ASKER
It didn't "Fix" the problem, but it was a workaround.