SQL views with parameter, select...into

Posted on 2012-08-31
Last Modified: 2012-09-04
Greetings to all Experts,
I hope that one of you can help me this afternoon.

I have an Access 2003 database in which I am trying to recreate a nonworking InfoMaker report.  The report references what I believe to be a view in a Sybase ARIA database.  

The view seems to have a parameter '@id' and will not run without an id being provided.  
I need this thing to allow me to query several hundred ids.

If I set an Access query and priivde a single id in the criteria row the view returns data.

It will not run when related to an Access table only containing the id.  
It will not 'SELECT ...INTO' (create a table) even if provided with an id.
Pass-through query with ReturnsRecords property set to True did not return any records (although if 'I SELECT * FROM vv_Name WHERE id = 1234' I get records).

It will not run with a subquery feeding it the id.

I am uncertain how to procede and seek my expert's advice.  
Thanks in advance for you time and trouble.
Question by:Prisoner362670
    LVL 29

    Expert Comment

    I don't wait follow what you're doing. Are you trying to import the table info Access? Or dId you link the table and replaced the recordsource of the report with a query that refers to this table?.
    LVL 40

    Expert Comment

    by:Vadim Rapp
    > Pass-through query with ReturnsRecords property set to True did not return
    > any records

    So set ReturnsRecords to False.
    LVL 29

    Assisted Solution

    I meant I don't quite follow...  
    This predictive text on my phone is unpredictable.  :-)
    LVL 67

    Accepted Solution

    SELECT INTO is treated as SELECT - which always returns a Resultset, even if empty. But SELECT INTO does not retrieve any rows, it is a DDL like CREATE TABLE. Why you would want to do that? The SELECT INTO will be executed remotely, and create a table in Sybase.

    What you could try is to create the IDs table in Sybase. This table can be joined to the view then, and you are getting your rows as expected by a simple SELECT.

    Author Comment

    A Good Afternoon to All,

    Although I have been unable to write I have been able to work on my query in both InfoMaker and Access 2003.  I started to rebuild the SQL query in InfoMaker but found ultimately I could not pull what I needed from the view.  So I moved totally into Access which is better for the user I am developing for anyway because they actually need an application and not a report.

    Anyway, in Access I found sucess not in trying to 'feed' the view the @id via a query or a vba codeset for recordset.  What I did was create a SQL query pulling four fields from the view (any more fields triggered an 'illegal' from Sybase).  This seems to work reliably and I have decided to continue development in Access with linked Sybase tables.

    I was unable to test Olemo suggestion as I do not have rights to create tables in ARIA/Sybase.

    I thank you both for your time and suggestions as so split the points between you.
    Persistance is Success!

    Author Closing Comment

    see above from congogrey.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    Oracle SQL Query Syntax 6 71
    Copy only dates 3 57
    Invalid Identifier Error 3 46
    Oracle PL/SQL syntax 4 29
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now