SUM(CASE WHEN SalesAnalysisHistory.InvoiceDate BETWEEN '01/1/08' AND '01/31/08' THEN SalesAnalysisHistory.ExtendedLineItemAmount ELSE NULL END) AS [JAN],
etc Feb
etc March
etc
FROM ABW.dbo.SalesAnalysisHistory SalesAnalysisHistory, ABW.dbo.Customer Customer
WHERE SalesAnalysisHistory.CustomerCode = Customer.CustomerCode AND SalesAnalysisHistory.InvoiceDate BETWEEN '01/1/08' AND '12/31/08'
GROUP BY SalesAnalysisHistory.CustomerCode, Customer.CustomerName
ORDER BY SalesAnalysisHistory.CustomerCode
This query works great, except that whoever designed our SQL tables, made a completely separate table for "Miscellaneous Charges", which include shipping, freight and tax. They also didn't include the invoice date on the chart, instead choosing to list each entry by invoice number, month number and year. The table/columns for the Misc chargers are as follows:
ABW.dbo.SalesAnalysisMiscChargesHistory
SalesAnalysisMiscChargesHistory.InvoiceNumber
SalesAnalysisMiscChargesHistory.LineItemAmount (includes all charges, including freight and tax)
SalesAnalysisMiscChargesHistory.SalesPeriod (ie. 1-12)
SalesAnalysisMiscChargesHistory.SalesYear (ie. 2008)
So basically, I need to amend my orginal query to:
SalesAnalysisHistory.ExtendedLineItemAmount between '01/1/08' AND '12/31/08'
Plus
SalesAnalysisMiscChargesHistory.LineItemAmount WHEN SalesAnalysisMiscChargesHistory.SalesPeriod = 1 and WHEN
SalesAnalysisMiscChargesHistory.SalesYear = 2008
end as JAN
I really really appreciate any help I may get on this, thank you!
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
hello there... i need help with cross tab query with sum(case actually iam using ms access 2007 and vb.net 2005 i have problems with the sintaxis ill paste the code below:
TRANSFORM SUM(cantidad*(case tipo WHEN 'Entrada' else 1 then 1 end 0)-cantidad(case tipo WHEN 'Salida' else 1 then 0 end)) AS TOTAL SELECT S.nombre,S.grupo,SUM(cantidad) AS Sub FROM Movimiento AS M, Punto AS P, Sorteo AS S WHERE(M.id_sorteo = s.id_sorteo And M.id_punto = P.id_punto) GROUP BY P.nombre, S.nombre, S.grupo ORDER BY grupo PIVOT P.nombre"
i need the correct sintaxis for this sentece: SUM(cantidad*(case tipo WHEN 'Entrada' else 1 then 1 end 0)-
or another way to do this , the finally of this query is get the actual inventory.
Hi i'm trying to run a CASE, Database is TS_Positions Column is Position Type, we usually get data being -1 or 1, i would like use the Function CASE to change that in my query(easier to read) 1=being a BUY... -1=being a SELL. For Some reason my query will NOT Work, Every other part works just not the CASE part.. Any ideas?????? Query:
SELECT CASE PositionType WHEN PositionType '1' THEN 'BUY' WHEN PositionType '-1' THEN 'SELL' AS [BS}, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume, LEFT(Contracttype,1) as KIND, strike, expiringdate, comment, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE] FROM TS_Positions WHERE (Contract LIKE 'LI%') OR (Contract LIKE 'LK%') OR (Contract LIKE 'LL%') OR (Contract LIKE 'LM%') ORDER BY Contract
The below query should only use the AND parts if the variables contain a value.
How do I fix the CASE clause??
Thanks.
Code Block SELECT SeniorMgr, Manager FROM Client f INNER JOIN Portfolio p ON p.ClientID = f.ClientID INNER JOIN Staff s ON (s.ClientID = p.ClientID) AND (s.FundID = p.PortfolioID) AND (s.ClientID = f.ClientID) WHERE s.ClientID = @ClientID AND s.FundID = @FundID CASE WHEN @SeniorManager != '' THEN AND SeniorMgr = @SeniorManager CASE WHEN @Manager != '' THEN AND Manager = @Manager END
I am trying to solve a data conversion problem using CASE and need some advice.
Column "tsstl9000details_vfeed.t.amountserviceaffected" should have numeric data entered, but since it is free-form via a web interface, alphanumeric data sometimes gets entered. I'm wanting to get the numeric portion of the column value with the following snippet, and ending up with an error. It works fine as long as the column value is numeric:
select f.assignedlinenum, t.amountserviceaffected, CAST(((CAST((CASE t.amountserviceaffected WHEN ISNUMERIC(t.amountserviceaffected) THEN t.amountserviceaffected ELSE LEFT(t.amountserviceaffected,PATINDEX(' ',t.amountserviceaffected)) END) AS REAL) / CAST(f.assignedlinenum AS REAL)) * 100) AS INTEGER) from fccdetails_vfeed f left outer join tsstl9000details_vfeed t on t.eventnum=f.eventnum where f.eventnum='wisw029147'
assignedline = 10910 actual column value amountserviceaffected = 151 lines actual column value
Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value '151 lines' to a column of data type int.
I want get get results in sql that are all written in UPPERCASE but I want to receive them in Initial Case format I know UPPERCASE is UPPER lowercase is lower but what is Initial Case(first letter Capital in a word)
In one SQL server table table1 it contains following data ID Date CA01-001 01/01/2000 CA01-002 02/01/2000 CA01-003 01/15/2000 CA01-004 02/11/2000 CB02-001 01/01/2001 CB02-002 02/01/2001
ID is grouped by first four characters CA01 or CB02, then each record increased by 1 to be 001, 002 .. untill 999 in sequence
How do I write SQL query just to get each group largerest record such as for our case: CA01-004 02/11/2000 CB02-002 02/01/2001
HelloI am using an allready Full database MS SQL 2000my 3 tables -->Report :ReportID (PK)RNameRValueProduct :PNameCategoryReportID (FK)Infos :ICommentsIVaLuemy query (to get a new table with only columns, or a .NETcollection) -->SELECT Report.ReportID AS RID, Report.RName AS RN, Report.RValue AS RV, Infos.Commentar AS IC,MAX(CASE WHEN Product.Category = 50 THEN Product.PName END) AS P50, MAX(CASE WHEN Product.Category = 54 THEN Product.PName END) AS P54, MAX(CASE WHEN Product.Category = 78 THEN Product.PName END) AS P78, MAX(CASE WHEN Product.Category = 540 THEN Product.PName END) AS P540, MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421FROM Report INNER JOIN Product ON Report.ReportID = Product.ReportID LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue WHERE (Report.ReportID = 10)GROUP BY Report.ReportID, Report.RName, Report.RValue, Infos.ICommentsReport.ReportID = Product.ReportID --> Primary Key to Foreign KeyReport.RValue = Infos.IValue --> only on full text (100 char)they are not indexedin Product can be a few million of lines, a few 10.000 in Report, about 1000 in Infosit can be very longhow can i do it in a better way ? (of course I cannot change the structure of tables, another aplication is using it)thank you
Why couldn't they make SQL syntax error mistakes a little less vague.
Anyway, I was wondering, is it possible to use a set in your case statement?
CASE ( select distinct tbhtg.TrainingBlockHistoryTypeGroupingCd from tblTrainingBlockHistory tbh inner join tblTrainingBlockHistoryType tbht on tbh.TrainingBlockHistoryTypeCd = tbht.TrainingBlockHistoryTypeCd inner join tblTrainingBlockHistoryTypeGrouping tbhtg on tbht.TrainingBlockHistoryTypeGroupingCd = tbhtg.TrainingBlockHistoryTypeGroupingCd where (select dbo.fnTrainingBlockStatus( 1234, getdate())) = tbht.TrainingBlockHistoryTypeCd ) WHEN 'S' then (COUNT(DISTINCT TRD.TrainingBlockHistoryId) = COUNT(DISTINCT SWT.TrainingBlockHistoryId)) end
This is giving me an error on the WHEN statement. The error is "Incorrect syntax near '='" Have no idea how to fix this. But the select statement seems to work, and as far as I can tell, that is how you write a CASE statement.
Also, this CASE statement is inside the HAVING clause - is that going to be a problem?
Hi all, I am not sure this is the right place to post my question, if not please let me know where I should post it. I have been struggling with it for 2 hours:(. I have a query that works fine in the sql query analyzer. I need to put it in the asp.net page using vb.net. When I cancatenate all the pieces together with the 'case when', it fails. Is it because of the single quotes or what? Can someone please take a look at the simple query? New System.Data.SqlClient.SqlDataAdapter("SELECT T1.C1 as D#," & "(case When T1.Production_Date is null then " & "' " & "'" & " else T1.Production_Date end)" & ", T1.Production_Plant as Location, convert(int,T1.Inv_Level) as Inv_Level, T1.Container_Size, T1.Level_ID, T1.Unit_of_Measure, T1.Performance_Comments, T1.Product_Number, T1.Unit_ID,Products.P_Name FROM T1 INNER JOIN Products ON T1.Product_Number = Products.Product_Number and T1.Unit_ID = Products.Unit_ID WHERE ((T1.Product_Number)=" & "'" & strPID & "'" & ") AND (( T1.U_ID=" & "'" & strUID & "'" & "))", con) Your help is highly appreciated,
I have a select statement and I would like to know which when clause(1st set of whens) was fired and the value of the then for further use in my query. I am wondering if this is possibly, and how it would be done. If i try and set a @variable at the beginning of the case statament i get an error, here is my query SELECT PIE.productID, PIE.quantity, CEILING((PIE.width/12.0)/0.5)*.5 as width, CEILING((PIE.length/12.0)/0.5)*.5 as length, ***throws an error**** @sqft = CASE --- but the then clause of this CASE is the value i want for later use in this same query WHEN CEILING((PIE.width/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.length/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.width/12.0)/0.5)*.5) * 1.5) * CEILING((PIE.length/12.0)/0.5)*.5)/1.0)*1.0 WHEN CEILING((PIE.length/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.width/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.length/12.0)/0.5)*.5) * 1.5) * CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0 ELSE CEILING((CEILING((PIE.length/12.0)/0.5)*.5) * (CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0 END AS sqft, CASE WHEN @custMarkup = 1 THEN (SELECT PML1 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) WHEN @custMarkup = 2 THEN (SELECT PML2 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) WHEN @custMarkup = 3 THEN (SELECT PML3 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) WHEN @custMarkup = 4 THEN (SELECT PML4 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) WHEN @custMarkup = 5 THEN (SELECT PML5 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) END AS markup FROM ProductsInEstimate PIE WHERE estID = @estid
I need to pull one field from one table and one field from another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement??? is this correct ?? its showing me errors Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ??? (case when 'DataSource =1' then 'ABC' then 'BCD' endcase) Where ..... Pls correct me Thanks
Hello everyone, is there anyway to use an update sub query in a case expression , something like this for exampleselect username,(case when password ='606' then (update users set username = 'me' where id= '3') else password end) from users i have been googling this issue just with no usefull resultsthanks for any replyMahmoud Manasrah
I am using a CASE statement within a SELECT query to sum up values for different customers.
SELECT CR_CUST.Customer_Code, 'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END FROM CR_CUST INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code WHERE (CR_PROD.Part_Class_Code = 1 OR CR_PROD.Part_Class_Code = 7 OR CR_PROD.Part_Class_Code = 8) GROUP BY CR_CUST.Customer_Code, CR_PROD.Part_Class_Code
My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so...
I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table.
SELECT Customer_Code, SUM(General_01), SUM(General_07), SUM(General_08) FROM #MyTempTable GROUP BY Customer_Code
This is my first post and I am doing my best to follow all of the rules that have been stated in the sticky thread within this forum. I have included my code and I am sure that it is not the best as I am just really starting to get into SQL as I am more profecient with Access. I believe what I am attempting to do can be done, it is just I am not sure how to currently achieve it.
When I run my code this is the output that I receive: Region Quantity Americas43325 Americas303138 Americas 28440 Americas 211996 Asia29294 Asia 45763 Asia 2811 Asia 213363 EMEA 290782 EMEA 217134
What I would like the code to be able to do is to consolidate any of the duplicate regions that are created. I believe the reason why it is creating duplicates is that I tell it to order by the Region though I am sure that it orders it by the original value for the Region and not the value that I tell it to convert to. So a perfect scenario would be that the information would display out like this:
Region Quantity Americas346363 Americas 220436 Asia 75057 Asia 214174 EMEA 290782 EMEA 217134
Now if that can be done that would be wonderful, but then my next question is there anyway that I would be able to have the information display like this:
Region Quantity 1 Quantity 2 Americas346363 20436 Asia 75057 14174 EMEA 290782 17134
Again I am sorry if I haven't follow any of the rules that have been put in place if I have missed anything I will add the information when requested.
I believe that I might need to follow something similar to this example and that the union might not even be what I needed. Sorry for the super long post especially seeing how it is my first time. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72743
Although that seems to help me get the Quantity 1 and 2 seperated I am still running into the issue of having the multiple Americas.
Set @StartingDate = '12/03/2006' Set @EndingDate = '01/04/2007'
SELECT Case when ARAFC.Region = 'North America' then 'Americas' when ARAFC.Region = 'Latin America' then 'Americas' when ARAFC.Region = 'APAC' then 'Asia' when ARAFC.Region = 'Far East' then 'Asia' Else 'EMEA' end as 'Region', sum(SV.Quantity) as Quantity
FROM ShipmentView SV (nolock) INNER JOIN ARandAFCSite ARAFC (nolock) ON ARAFC.ARName = SV.FromSiteName Left Join ProductMaster PM (nolock) on SV.PartNumber = PM.PartNumber
where SV.EventDate between @StartingDate and @EndingDate and SV.EventtypeID = 2700 and SV.ordertype = 1 and Left(SV.ProductFamilyName, 3) in ('Win' )
group by ARAFC.Region
Union
SELECT Case when ARAFC.Region = 'North America' then 'Americas 2' when ARAFC.Region = 'Latin America' then 'Americas 2' when ARAFC.Region = 'APAC' then 'Asia 2' when ARAFC.Region = 'Far East' then 'Asia 2' Else 'EMEA 2' end as 'Region', sum(SV.Quantity) as Quantity
FROM ShipmentView SV (nolock) INNER JOIN ARandAFCSite ARAFC (nolock) ON ARAFC.ARName = SV.FromSiteName Left Join ProductMaster PM (nolock) on SV.PartNumber = PM.PartNumber
where SV.EventDate between @StartingDate and @EndingDate and SV.EventtypeID = 2700 and SV.ordertype = 1 and Left(SV.ProductFamilyName, 6) in ('Office' )
I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.
select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages
Basically now, it returns the "OutBoundMessages" column
I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )
I determine if the column is "checked" or "unChecked" by a column in tblMessage
Hi, a query of mine slowed down significantly when this statement wasadded into the where:(DATEDIFF(day, Col_StartDate, GETDATE()) BETWEEN 1 AND(SELECT CASE datepart(dw, getdate())WHEN 1 THEN 2WHEN 2 THEN 3ELSE 1END) )What it is supposed to do is get Friday, Saturday and Sunday's data iftoday is Monday -- in addition if the day is Sunday get Friday andSaturdays data. Otherwise, just get yesterdays data. This works,however it slowed down the query by 12X. I think it may be the use ofa "case" because if I hard code it there isn't a problem. Anysuggestions for alternatives?
I have a query which spends a lot of time calculating my CASE WHEN -statements.
My query looks like this
SELECT TOP 250
UserId, CASE WHEN
(someCol*0.4+someOtherCol*0.3) > 99 THEN 99 ELSE
(someCol*0.4+someOtherCol*0.3) END FROM
(
SELECT
UserId,
CASE WHEN @myparam > 50 THEN
CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END ELSE
CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol, CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol FROM
SomeTable ) t1
This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.
I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.
How do you do a case sensitive searching without having the database case sensitive or is this even possible. There are times we want to perform case sensitive searching and case insensitive searching.
I found this article, but is that the suggested way. seems a bit bad, but if it is the way I am willing to use it.
Name Qty Location ----------------------------------- Item A 2 Warehouse 1 Item A 5 Warehouse 2 Item B 3 Warehouse 1 Item C 1 Warehouse 1 Item B 6 Warehouse 2
How is the sql command/query/store procedure to generate result like this?
Name Qty ----------------- Item A 7 Item B 9 Item C 1
Can i use a CASE statement in a prediction query. the following query is throwing me an error
SELECT CASE [Sales Forecast Time Series].[City Code] when 'LA' then 'Los Angeles' WHEN 'CA' THEN 'California' ELSE 'OTHERS' END, PredictTimeSeries([Sales Forecast Time Series].[Sales Value],5) From [Sales Forecast Time Series]
ERROR: Parser: The statement dialect could not be resolved due to ambiguity.
Also
Is it possible to discretize the Sales Value column using a the CASE statement, the output column of PredictTimeSeries function.
Is there a link that can give me a comprehensive info on what can be achieved and what cant be using DMX queries
Hi I have a stored procedure and i am trying to add case statements to them.. but i am getting an Error. which is Msg 125, Level 15, State 3, Procedure udf_EndDate, Line 34 Case expressions may only be nested to level 10.
And This is my sproc-- ================================================ -- Template generated from Template Explorer using: -- Create Scalar Function (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the function. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date, ,> -- Description:<Description, ,> -- ============================================= Create FUNCTION [dbo].[udf_EndDate] (@PeriodId int) RETURNS datetime AS
BEGIN DECLARE @Month int, @Year char(4)
SELECT @Month = [Month], @Year = Cast([Year] as char(4)) FROM Period WHERE PeriodId = @PeriodId
RETURN CASE @Month WHEN 1 THEN '1/31/' + @Year ELSE CASE @Month WHEN 2 THEN '2/28/' + @Year ELSE CASE @Month WHEN 3 THEN '3/31/' + @Year ELSE CASE @Month WHEN 4 THEN '4/30/' + @Year ELSE CASE @Month When 5 Then '5/31/' + @Year ELSE CASE @Month When 6 Then '6/30/' + @Year ELSE CASE @Month When 7 Then '7/31/' + @Year ELSE CASE @Month When 8 Then '8/31/' + @Year ELSE CASE @Month When 9 Then '9/30/' + @Year ELSE CASE @Month When 10 Then '10/31/' + @Year ELSE CASE @Month When 11 Then '11/30/' + @Year ELSE CASE @Month When 12 Then '12/31/' + @Year ELSE null END END END END END END END END END END END END END
I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP: Select Case tCOTSSoftware("Type") Case 1 execute an SQL Update Command Case 2 execute a different SQL Update Command End Select What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:
SELECT A.COMPANYCODE, A.INVOICENUMBER, A.LINENUMBER, A.SONUMBER, A.CUSTOMERCODE, A.SHIPPERNUMBER, A.INVOICEDATE, A.ITEMCODE, A.QUANTITYINVOICED, A.UNITPRICE AS 'InvPrice', A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice', INVENTORY.UNITPRICE AS 'StdPrice', INVENTORY.STANDARDCOST, A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost', (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$', (INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff' FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE AND INVENTORY.ITEMCODE = A.ITEMCODE AND ((A.COMPANYCODE = 'csp') AND (A.QUANTITYINVOICED <>$ 0) AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08')) ORDER BY A.INVOICEDATE, A.ITEMCODE
They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:
SELECT NewColumn = CASE WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice ELSE 'null' END, FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY
I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.
Any help would be greatly appreciated to modify the current query to display the new column.
I have a SQL7 database that was installed as case-insensitive./* Sort Order = 52, Case-insensitive dictionary sort order. */This database contains a table that has a varchar column which containsdata such as:'JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary''Subcommittee on Justice and Judiciary; TRANSPORTATION''Subcommittee on Cities; JUDICIARY; TRANSPORTATION'I want to write a SELECT statement that gives me only those rows (1stand 3rd) that have JUDICIARY (not Judiciary) in the varchar column.This is SQL7 so I can't use COLLATE.I triedSELECT mycolFROM mytableWHERE mycol LIKE '%JUDICIARY%'AND CAST(SUBSTRING(mycol ,PATINDEX('%JUDICIARY%',mycol),LEN('JUDICIARY')) AS VARBINARY) = CAST('JUDICIARY' AS VARBINARY)But this leaves out the row with JUDICIARY and Judiciary in it (onlyreturns 3rd row).Any suggestions?
I have a question about what is possible with a prediction query against a nested table. Say I have a basic customer-product case and nested table mining model like so:
Mining Model DT_CustProd ( [Id] , [Gender] , [Age] [Products] Predict ( [ProductName] , [Quantity] ) ) Using Microsoft_Decision_Trees
I can write a query to find the probability of product (and quantity) A like so:
SELECT (select * from Predict(Products,INCLUDE_STATISTICS) where ProductName = 'A' )
FROM DT_CustProd
NATURAL PREDICTION JOIN
(SELECT 'M' AS [Gender], 27 AS [AGE] ) AS t
What if I know that the query customer (M,27) in question has purchased product B, how can I use that in the prediction join to predict product A? The fact that product B was purchased might influence the prediction, right?
In SS 2000 I want to create a sproc that returns the correct address block for a contact. I want to concatenate AddressLine1 and AddressLine2 if AddressLine2 has a value. When I run the following query I get an error (below):
SELECT vwICPContacts.PersonID, vwPersons.PreferredAddress, vwPersons.Email1,vwPersons.email2, vwPersons.email3, CASE WHEN vwPersons.preferredAddress='Home Address' then case when (isnull([vwpersons].[HomeAddressLine2],'') = '') then [vwPersons].[HomeAddressLine1] when (isnull([vwpersons].[HomeAddressLine2],'') <> '') then [vwPersons].[HomeAddressLine1] + Char(13) & Char(10) & [vwPersons].[HomeAddressLine2] END ELSE case when (isnull([vwpersons].[AddressLine2],'') = '') then [vwPersons].[AddressLine1] when (isnull([vwpersons].[AddressLine2],'') <> '') then [vwPersons].[AddressLine1] + Char(13) & Char(10) & [vwPersons].[AddressLine2] END END AS MailingAddress FROM (vwPersons INNER JOIN vwICPContacts ON vwPersons.ID = vwICPContacts.PersonID) LEFT JOIN vwCompanies ON vwPersons.CompanyID = vwCompanies.ID
Error Message: Server: Msg 403, Level 16, State 1, Line 1 Invalid operator for data type. Operator equals boolean AND, type equals nvarchar.
I've done this before but am completely stumped. Any ideas?
