making a query into a view or sp

Question

Post reply

making a query into a view or sp

jessica-321853

SSCrazy

Points: 2207
More actions
July 18, 2006 at 12:22 pm

#118785

hi I have this access query in sql view and would like to put it in a stored procedure or view, i am having a problem in how to transfer the expressions , I already have query 1 as a view in sql server, can anyone help me please

SELECT [query 1].Customer_No, [query 1].Product_No, [Distinct Subform 1 query 1].ItemGroup, [query 1].SumOfSumOfCountOfMfr_Item_No, [query 1].MinOfMinOfInvoice_Date, [query 1].MaxOfMaxOfInvoice_Date, ROUND([MaxOfMaxOfInvoice_Date]-[MinOfMinOfInvoice_Date]) AS Expr1, IIf([Expr1]>0,ROUND([Expr1]/[SumOfSumOfCountOfMfr_Item_No])) AS Expr2, [MaxOfMaxOfInvoice_Date]+[Expr2]+60 AS Expr3, [Expr2]+60 AS Expr9, IIf([SumOfSumOfCountOfMfr_Item_No] Is Null,1) AS Expr6, IIf([Expr3]<=Now(),1) AS Expr4, IIf([Expr1]=0,1) AS Expr7, ROUND(Now()-[MaxOfMaxOfInvoice_Date]) AS Expr8, [query 1].Category
FROM [query 1];

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Is that working at all? I don't see where [Distinct Subform 1 query 1].ItemGroup is coming from

* Noel

jessica-321853 SSCrazy Points: 2207 More actions · Answer 2

sorry... basically this query is coming from another one (i did not create it) .. the one i posted before was the one i have been working on.

This are the originals:

query 1

SELECT DISTINCT N1Q1.Customer_No, N1Q1.Product_No, Count(N1Q1.Product_No) AS CountOfProduct_No1, IIf([CountOfProduct_No]>1,1,1) AS Expr5, [Item Group].Itemgroup, Sum(N1Q1.SumOfTotal_Sale_Amt) AS SumOfSumOfTotal_Sale_Amt, Sum(N1Q1.SumOfCountOfMfr_Item_No) AS SumOfSumOfCountOfMfr_Item_No, Min(N1Q1.MinOfInvoice_Date) AS MinOfMinOfInvoice_Date, Max(N1Q1.MaxOfInvoice_Date) AS MaxOfMaxOfInvoice_Date, N1Q1.Category

FROM N1Q1 LEFT JOIN [Item Group] ON (N1Q1.Product_No = [Item Group].Product_No) AND (N1Q1.Customer_No = [Item Group].Customer_No)

GROUP BY N1Q1.Customer_No, N1Q1.Product_No, IIf([CountOfProduct_No]>1,1,1), [Item Group].Itemgroup, N1Q1.Category;

This is the query i would like to make a view in sql server:

SELECT [query 1].Customer_No, [query 1].Product_No, [query 1].ItemGroup, [query 1].SumOfSumOfCountOfMfr_Item_No, [query 1].MinOfMinOfInvoice_Date, [query 1].MaxOfMaxOfInvoice_Date, Round([MaxOfMaxOfInvoice_Date]-[MinOfMinOfInvoice_Date]) AS Expr1, IIf([Expr1]>0,Round([Expr1]/[SumOfSumOfCountOfMfr_Item_No])) AS Expr2, [MaxOfMaxOfInvoice_Date]+[Expr2]+60 AS Expr3, [Expr2]+60 AS Expr9, IIf([SumOfSumOfCountOfMfr_Item_No] Is Null,1) AS Expr6, IIf([Expr3]<=Now(),1) AS Expr4, IIf([Expr1]=0,1) AS Expr7, Round(Now()-[MaxOfMaxOfInvoice_Date]) AS Expr8, [query 1].Category

FROM [query 1];

noeld SSC Guru Points: 96590 More actions · Answer 3

Give this is a go, there are expressions in your code that are not complete like IIF with only one argument.

I tried to guess what you wanted in those cases

SELECT c.*
       ,  CASE WHEN DATEDIFF(DAY, Expr3, GETDATE()) = 0 THEN 1 ELSE DATEDIFF(DAY,Expr3, GETDATE()) END  AS Expr4
FROM (
SELECT  b.*
       , DATEADD(d, Expr2 + 60, MaxOfMaxOfInvoice_Date ) AS Expr3
       , Expr2 + 60 AS Expr9

FROM(
SELECT         a.*
       , CASE WHEN  Expr1 > 0 THEN CAST( Expr1 / SumOfSumOfCountOfMfr_Item_No  AS INT)  ELSE 0 END  AS Expr2
       , CASE WHEN Expr1 =0 THEN 1 ELSE Expr1  END AS Expr7
FROM(
SELECT           Customer_No
       , Product_No
       , ItemGroup
       , SumOfSumOfCountOfMfr_Item_No
       , MinOfMinOfInvoice_Date
       , MaxOfMaxOfInvoice_Date
       , DATEDIFF(DAY, MaxOfMaxOfInvoice_Date ,MinOfMinOfInvoice_Date ) AS Expr1
       , COALESCE( SumOfSumOfCountOfMfr_Item_No ,1) AS Expr6
       , DATEDIFF(DAY,GETDATE(), MaxOfMaxOfInvoice_Date ) AS Expr8
       , Category
FROM [query 1]
)a)b)c

Cheers,

* Noel

jessica-321853 SSCrazy Points: 2207 More actions · Answer 4

jessica-321853

SSCrazy

Points: 2207

July 18, 2006 at 2:17 pm

#649950

wow Thank you!!! this will be a view correct?

noeld SSC Guru Points: 96590 More actions · Answer 5

Correct. Make sure it does what you need

* Noel