Solved

VB6 - Query from Oracle in apprilication issue

Posted on 2014-02-16
3
331 Views
Last Modified: 2014-02-16
HI all

I have this below SQL that work great in Oracle.

But when i want pull the extract from my VB6 app and get the result inside my MSHFlexgrid2, i have a Compile error, syntax error.

Would you know why?

Thanks again for your help

SQL in app:
Dim oconn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String

    strSQL = "SELECT DC, STORE_NUM, PICK_DC, RECEIVE_DC, LANE_NUM, RTRIM( " & _
             "EXTRACT( XMLAGG( XMLELEMENT( "x", RELEASE_DAY || ',' || ARRIVE_DAY " & _
             "|| ',' || RECEIVE_ARRIVE_OPEN || ',' || RECEIVE_ARRIVE_CLOSE || ',' ) " & _
             "ORDER BY RELEASE_DAY, ARRIVE_DAY, RECEIVE_ARRIVE_OPEN, RECEIVE_ARRIVE_CLOSE ), '/x/text()' ).GETSTRINGVAL(), " & _
             "','  ) X FROM (SELECT A.PICK_DC AS DC, A.STORE_NUM, C.PICK_DC, C.RECEIVE_DC, C.SHIP_DAY AS RELEASE_DAY, C.ARRIVE_DAY, " & _
             "C.RECEIVE_ARRIVE_OPEN, C.RECEIVE_ARRIVE_CLOSE, C.LANE_NUM FROM LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C " & _
             "WHERE A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM AND B.LANE_NUM= C.LANE_NUM AND B.LANE_GROUP=0) GROUP BY DC, " & _
             "STORE_NUM, PICK_DC, RECEIVE_DC, LANE_NUM"

    Set oconn = New ADODB.Connection
    oconn.Open "Provider=OraOLEDB.Oracle.1;Data Source=xxxxx;User Id=xxxxx;Password=xxxxx;"
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic
    rs.Open strSQL, oconn, adCmdText
    Set MSHFlexGrid2.DataSource = rs

    rs.Close
    oconn.Close
    Set rs = Nothing
    Set oconn = Nothing

Open in new window


SQL that works in Oracle Dev.
SELECT DC,
         STORE_NUM,
         PICK_DC,
         RECEIVE_DC,
         LANE_NUM,
         RTRIM(
             EXTRACT(
                 XMLAGG(
                     XMLELEMENT(
                         "x",
                            RELEASE_DAY
                         || ','
                         || ARRIVE_DAY
                         || ','
                         || RECEIVE_ARRIVE_OPEN
                         || ','
                         || RECEIVE_ARRIVE_CLOSE
                         || ','   
                     )
                     ORDER BY
                         RELEASE_DAY,
                         ARRIVE_DAY,
                         RECEIVE_ARRIVE_OPEN,
                         RECEIVE_ARRIVE_CLOSE
                 ),
                 '/x/text()'
             ).GETSTRINGVAL(),
             ','    
         )
             X
    FROM (SELECT
    A.PICK_DC AS DC,
    A.STORE_NUM,
    C.PICK_DC,
    C.RECEIVE_DC,
    C.SHIP_DAY AS RELEASE_DAY,
    C.ARRIVE_DAY,
    C.RECEIVE_ARRIVE_OPEN,
    C.RECEIVE_ARRIVE_CLOSE,
    C.LANE_NUM
FROM
    LCLRPT.DC_STORE_SETTINGS A, LCLRPT.LCL_STORE_LANE_REF B, LCLRPT.XD_LANES C
WHERE
    A.DC_STORE_SETTINGS_SEQ_NUM= B.DC_STORE_SETTINGS_SEQ_NUM
    AND B.LANE_NUM= C.LANE_NUM
    AND B.LANE_GROUP=0)
GROUP BY DC,
         STORE_NUM,
         PICK_DC,
         RECEIVE_DC,
         LANE_NUM;

Open in new window

0
Comment
Question by:Wilder1626
[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
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39863051
first some tip: as the query has no parameters, I usually would put the query into a view (in oracle), and your application will then only have to do:
SELECT * FROM VIEW_NAME

this will solve a couple of issues, the main one being to remove all the (complex) sql stuff from the actual application code. others are that you will give the power to the dba to eventually optimize the sql (view) without having the application to be recompiled and redistributed.

let me check what the error could be, meanwhile
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39863056
now, I see the issue:

... XMLELEMENT( "x", ...

inside your string declaration would need to be:
XMLELEMENT( ""x"",

as the " is already the string delimiter (in vb), to use the " inside the string, you need to duplicate it.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 39863100
You are right.

Thanks a lot. It's working now.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

691 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