Solved

Convert Crosstab Query to OLEDB SQL

Posted on 2013-12-11
21
452 Views
Last Modified: 2014-02-09
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

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 in new window


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: Non Procedure Case       3      132
201      Physician      Admit-Attend-Disch-Consult: Procedure Case             11      280

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: Non Procedure Case              3      437
201      Physician      Admit-Attend-Disch-Consult: Procedure Case              11      287

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

Open in new window

0
Comment
Question by:lorenda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 3
  • 3
  • +2
21 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 39712941
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"").
0
 
LVL 6

Author Comment

by:lorenda
ID: 39712954
Thanks for the response. Unfortunately, this change produced a type mismatch error
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39712964
What is the data type of the Month field?
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 6

Author Comment

by:lorenda
ID: 39713219
It's a date field.
0
 
LVL 6

Author Comment

by:lorenda
ID: 39714105
I'm sorry - I completely missed the cdate function. I tried this, but now I'm receiving the following error:

error
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39714116
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")
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39714196
T-SQL uses a single quote to enclose data values so the date would be entered as:
'4/30/2010'
0
 
LVL 6

Author Comment

by:lorenda
ID: 39714254
This is my VBA.NET snippet for the above error.

"Run for other queries - CRITERIA].[Month]) > " & CDate("4/30/2010") & "))" & _
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39714293
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?
0
 
LVL 6

Author Comment

by:lorenda
ID: 39714653
Sorry, what is SSMS?
0
 
LVL 6

Author Comment

by:lorenda
ID: 39714663
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.
0
 
LVL 6

Author Comment

by:lorenda
ID: 39714683
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>;
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39720615
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.
0
 
LVL 6

Accepted Solution

by:
lorenda earned 0 total points
ID: 39720625
While I did manage to find a workaround (I took the subquery I was running and did an Insert Into (Access=Make-table query) and then built a crosstab from that.) But in the interest of having this in my knowledgebase, I'm still willing to try any ideas that are thrown out here.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39720642
Have you figured out what is SSMS, yet?
0
 
LVL 6

Author Comment

by:lorenda
ID: 39720671
Yes. Jimpen answered it above. SQL Server Management Studio. (Thanks, Jimpen!)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39722115
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.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39728311
Can you try this as a a prebuilt view in SQL or maybe as a stored procedure?
0
 
LVL 6

Author Comment

by:lorenda
ID: 39833602
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!
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39833700
If you run the query in the Access database, does it work?
0
 
LVL 6

Author Closing Comment

by:lorenda
ID: 39845127
It didn't "Fix" the problem, but it was a workaround.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question