Dates and parametes in views

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  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,144252)

  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
  

• Jez-448386

  SSCarpal Tunnel

  Points: 4594

  More actions

  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

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  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
  

• Jez-448386

  SSCarpal Tunnel

  Points: 4594

  More actions

  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

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  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
  

• Jez-448386

  SSCarpal Tunnel

  Points: 4594

  More actions

  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

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  July 19, 2007 at 5:01 am

  #720678

  Thanks, I got it.

  ---

  Regards,

  goodguy

  Experience is a bad teacher whose exams precede its lessons