Post reply

Dates and parametes in views

  • July 19, 2007 at 12:45 am

    #120755

    Below is my DDL:

    CREATE TABLE #PARENT(SDATE DATETIME, GRN_ID INT, PO_ID INT, SUP_ID INT, INV BIGINT, LOC_ID INT, PAYTYPE INT, CR_DAYS INT, TOTAL NUMERIC, SUP_TOTAL NUMERIC, STATUS INT)

    INSERT INTO #PARENT (SDATE, GRN_ID, PO_ID, SUP_ID, INV, LOC_ID, PAYTYPE, CR_DAYS, TOTAL, SUP_TOTAL, STATUS) VALUES

    SELECT ‘05/07/2007’, 1, 1, 1, 1223, 2, CASH, 0, 175000, 175000, 3 UNION ALL

    SELECT ‘05/07/2007’, 2, 2, 2, 1225, 2, CASH, 0, 1280, 1200, 3 UNION ALL

    SELECT ‘05/07/2007’, 3, 3, 3, 4900, 1, CASH, 0, 3782, 3782, 2 UNION ALL

    SELECT ‘05/07/2007’, 4, 4, 3, 566352, 1, CREDIT, 15, 111665, 111665, 3 UNION ALL

    SELECT ‘05/07/2007’, 5, 5, 2, 77866, 2, CHEQUE, 7, 600050, 1 UNION ALL

    SELECT ‘05/07/2007’, 6, 6, 1, 65544, ggg, 1, CHEQUE, 14, 49400, 49400, 1, UNION ALL

    SELECT ‘05/07/2007’, 7, 7, 6, 112235, 2, CASH, 0, 391000, 391000, 3 UNION ALL

    SELECT ‘05/07/2007’, 8, 8, 2, A542, 2, CASH, 0, 3500, 3500, 3 UNION ALL

    SELECT ‘05/07/2007’, 9, 9, 6, P875565, 1, CHEQUE, 14, 14425, 14425 2)

    CREATE TABLE #CHILD (GRN_ID INT, PROD_ID INT, COST NUMERIC, QTY INT, UNIT INT)

    INSERT INTO #CHILD (GRN_ID, PROD_ID, COST, QTY, UNIT) VALUES(

    SELECT 1, 2, 5000,, 5, 1, UNION ALL

    SELECT 1, 1, 10000, 15, 2, UNION ALL

    SELECT 2, 1, 1280, 1, 2, UNION ALL

    SELECT 3, 8, 750, 5, 1, UNION ALL

    SELECT 3, 2, 5, 5, 1, UNION ALL

    SELECT 3, 1, 1, 7, 2, UNION ALL

    SELECT 4, 8, 750, 1, 1, UNION ALL

    SELECT 4, 2, 5, 2, 1, UNION ALL

    SELECT 4, 6, 1500, 3, 3, UNION ALL

    SELECT 4, 3, 19000, 4, 1, UNION ALL

    SELECT 4, 1, 1, 5, 2, UNION ALL

    SELECT 4, 5, 1000, 6, 1, UNION ALL

    SELECT 4, 4, 1200, 7, 3, UNION ALL

    SELECT 4, 7, 2000, 8, 1, UNION ALL

    SELECT 5, 2, 5, 10, 1, UNION ALL

    SELECT 5, 6, 1500, 20, 3, UNION ALL

    SELECT 5, 3, 19000, 30, 1, UNION ALL

    SELECT 6, 5, 1000, 15, 1, UNION ALL

    SELECT 6, 4, 1200, 12, 3, UNION ALL

    SELECT 6, 7, 2000, 10, 1, UNION ALL

    SELECT 7, 6, 1000, 11, 3, UNION ALL

    SELECT 7, 3, 19000, 20, 1, UNION ALL

    SELECT 8, 6, 1500, 1, 3, UNION ALL

    SELECT 8, 7, 2000, 1, 1, UNION ALL

    SELECT 9, 2, 5, 5, 1, UNION ALL

    SELECT 9, 5, 1000, 6, 1, UNION ALL

    SELECT 9, 4, 1200, 7, 3)

    I need to make a view from which I can extract SUM(QTY) for each PROD_ID in each LOC_ID between two parameter SDATE values.

    The view has to look up and replace from LOC_ID with LOC_NAME from LOCATION Table and PROD_ID with PROD_NAME from PRODUCTS Table.

    I am having great trouble trying to build the query for my view, with EM failing with error messages that a column cannot be part of the SELECT statement unless it is appears as an AGGREGATE function or in the GROUP BY clause. There are many fields that I need that do not need to be either. How do I get round it?

    I managed to make a simple view to get me SUM(QTY) per PROC_ID per LOC_ID, but could not include SDATE in it. Now I cannot use this view as a Crystal Report data source because I need to filter by SDATE. How can I do that?

    I also need to know if it is possible to define placeholders as parameters in views for which values can be passed as arguments by the calling code?

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • July 19, 2007 at 2:18 am

    #720633

    As far as the error message, there is no way round it.  Every column in the select has to be either in the Group By or part of an aggregate function (min, max, sum etc).

    It's not possible to pass parameters to views.  You could select from the view using the dates in a where clause or you could just write it as a stored procedure and pass the start and end dates as parameters or even a function (if you want to use the results in another query).

     

    J

  • July 19, 2007 at 2:37 am

    #720643

    Thanks, it is what I was afraid of.

    Question: I still want to work with a view, how can I write a Store Proc to return a view? (It's a dumb question, but then taht's why we are here, non?)

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • July 19, 2007 at 2:50 am

    #720647

    Same as you would for return rows from a table:

     

    create procedure Myproc @start_date datetime, @end_date datetime

    select <columns>

    from vw_summary

    where sdate between @start_date and @end_date

     

    J

  • July 19, 2007 at 3:10 am

    #720653

    Thanks, again, and please help me one last time:

    How do I get a handle on this recordset from my VB6 code?

    I normally run Stored Procedures with the statement

    Conn.Execute "stored_procedure_name".

    How can I assign this statement to an ADODB recordset variable?

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • July 19, 2007 at 3:49 am

    #720662

    Have a look at http://www.w3schools.com/ado/

    set conn=Server.CreateObject("ADODB.Connection")

    conn.Provider="Microsoft.Jet.OLEDB.4.0"

    conn.Open "c:/webdata/northwind.mdb"

    set rs=Server.CreateObject("ADODB.recordset")rs.Open "exec stored_proce 'start_date', 'end_date'", conn
    There are other ways of doing it like using command type and adding parameters but I cannot find any examples at the moment.
     
    J

  • July 19, 2007 at 5:01 am

    #720678

    Thanks, I got it.

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply