I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS
Select * from don0001
left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
I'm converting a View from access to Sql and I'm stuck on this IF statement.
IIf([FG_Qtys_1].[CoreQty]=0 Or [FG_Qtys_1].[CoreQty] Is Null,[KitCoreOnHand],[FG_Qtys_1].[CoreQty]);
I know that I could use CASE statement but i keep on getting errors.
My Case Statement: case when [dbo.FG_Qtys_view].[CoreQty]=0 then dbo.FG_Qtys_Kits.KitCoreOnHand when [dbo.FG_Qtys_view].[CoreQty] ISNULL then [dbo.FG_Qtys_view].[CoreQty] end as CoreQty
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
i have a query which goes like this:-select a.col1,b.col2,c.col2from tab1 a,tab2 b,tab3 cwherea.col1 *= b.col1 anda.col2 *= c.col2 andb.col3 = c.col3how do I write this query in SQL 92?also can we replace *= with =* by interchanging the participating tables as in"a.col1 *= b.col1" is this same as "b.col1 =* a.col1"
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
SELECT distinct whse_zone_id, bay_id From prod_geneology WHERE whse_zone_id in ('SH','CU','SG') START WITH unit_id = 'BL7B230811' CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
I am extremely new with .asp. We have the .net framework 1.1 installed on our server and the following query works in Crystal Reports (older version). We would like to have this converted to a .asp page with two inputs. One for the date range and one for the state. Can someone please tell me how to go about this. I do have downloaded as test. Visual Studio 2003.net and Web Matrix. I really am looking for (if there is any) a way to pretty quickly convert query to web pages so users can use them without a huge expense. IF someone already has something like this done. That would be great. The following query is for Visual Enterprise (Manufacturing software). Thanks
SELECT SHIPPER."CUST_ORDER_ID", SHIPPER."SHIPPED_DATE", SHIPPER."INVOICE_ID", SHIPPER_LINE."USER_SHIPPED_QTY", SHIPPER_LINE."SHIPPED_QTY", SHIPPER_LINE."UNIT_PRICE", RECEIVABLE_CURR."CURRENCY_ID", RECEIVABLE_CURR."SELL_RATE", CUST_ORDER_LINE."PART_ID", CUST_ORDER_LINE."ORDER_QTY", CUST_ORDER_LINE."USER_ORDER_QTY", INVENTORY_TRANS."TYPE", INVENTORY_TRANS."ACT_MATERIAL_COST", INVENTORY_TRANS."ACT_LABOR_COST", INVENTORY_TRANS."ACT_BURDEN_COST", INVENTORY_TRANS."ACT_SERVICE_COST", CUSTOMER_ORDER."CUSTOMER_ID", CUSTOMER_ORDER."SHIP_TO_ADDR_NO", CUSTOMER_ORDER."CURRENCY_ID", PART."DESCRIPTION", PART."STOCK_UM", CUSTOMER."STATE", CUST_ADDRESS."ADDR_NO", CUST_ADDRESS."NAME", CUST_ADDRESS."STATE" FROM { oj ((((((("VMFG"."dbo"."SHIPPER" SHIPPER INNER JOIN "VMFG"."dbo"."RECEIVABLE_CURR" RECEIVABLE_CURR ON SHIPPER."INVOICE_ID" = RECEIVABLE_CURR."INVOICE_ID") INNER JOIN "VMFG"."dbo"."SHIPPER_LINE" SHIPPER_LINE ON SHIPPER."PACKLIST_ID" = SHIPPER_LINE."PACKLIST_ID" AND SHIPPER."CUST_ORDER_ID" = SHIPPER_LINE."CUST_ORDER_ID") INNER JOIN "VMFG"."dbo"."INVENTORY_TRANS" INVENTORY_TRANS ON SHIPPER_LINE."TRANSACTION_ID" = INVENTORY_TRANS."TRANSACTION_ID") INNER JOIN "VMFG"."dbo"."CUSTOMER_ORDER" CUSTOMER_ORDER ON SHIPPER_LINE."CUST_ORDER_ID" = CUSTOMER_ORDER."ID") INNER JOIN "VMFG"."dbo"."CUST_ORDER_LINE" CUST_ORDER_LINE ON SHIPPER_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND SHIPPER_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO") INNER JOIN "VMFG"."dbo"."CUSTOMER" CUSTOMER ON CUSTOMER_ORDER."CUSTOMER_ID" = CUSTOMER."ID") INNER JOIN "VMFG"."dbo"."PART" PART ON CUST_ORDER_LINE."PART_ID" = PART."ID") LEFT OUTER JOIN "VMFG"."dbo"."CUST_ADDRESS" CUST_ADDRESS ON CUSTOMER_ORDER."CUSTOMER_ID" = CUST_ADDRESS."CUSTOMER_ID" AND CUSTOMER_ORDER."SHIP_TO_ADDR_NO" = CUST_ADDRESS."ADDR_NO"} WHERE SHIPPER."SHIPPED_DATE" >= {ts '2002-08-01 00:00:00.00'} AND SHIPPER."SHIPPED_DATE" < {ts '2004-08-25 00:00:00.00'} ORDER BY SHIPPER."SHIPPED_DATE" ASC
It works fine if I use the MS Access version of the database through ODBC, but if I try using the SQL version, I get the following error for this query:
Line 1: Incorrect syntax near '.'. Statement(s) could not be prepared.
Why on earth is this happenning? I'm completely at my wits end...and pointers would be wonderful.
I am getting a slightly different result set after converting my Oracle query to MS SQL. I am only getting those with invm_cost_code = 'A' and not those that are null as I do when I run the query on Oracle. Note, if I change to left outer join, still get wrogn values. But, if I change to full outer join I get those = 'A' and those that are either null or 'C' as nulls (the 'c' come through as null. Any ideas?
Oracle Query
SELECT CITM_PROPRD, CITM_INV_SEQ, CITM_ITEM_SEQ, INVM_COST_FLAG, (CITM_QTY * CITM_PRICE) FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR WHERE CSUM_COMP_CODE = 'A' AND CSUM_EMPL_CODE = SUBSTR('85',1,2) AND TRIM(CSUM_TERR_CODE) = TRIM('MB2') AND CSUM_PROPRD BETWEEN '200610' AND '200611' AND CSUM_PROPRD = CITM_PROPRD AND CSUM_INV_SEQ = CITM_INV_SEQ AND CITM_COMP_CODE = INVM_COMP_CODE(+) AND CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND (INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL)
Converted MS SQL Query
SELECT CITM.CITM_PROPRD, CITM.CITM_INV_SEQ, CITM.CITM_ITEM_SEQ ,INVM.INVM_COST_FLAG,(CITM.CITM_QTY * CITM.CITM_PRICE) FROM ACC_CUST_SUM as CSUM INNER JOIN ACC_CUST_ITEMS as CITM ON CSUM.CSUM_PROPRD = CITM.CITM_PROPRD AND CSUM.CSUM_INV_SEQ = CITM.CITM_INV_SEQ
RIGHT OUTER JOIN ACC_INV_MSTR as INVM ON CITM.CITM_COMP_CODE = INVM.INVM_COMP_CODE AND CITM.CITM_ITEM_CODE = INVM.INVM_ITEM_CODE AND (INVM.INVM_COST_FLAG = 'A' OR INVM.INVM_COST_FLAG IS NULL)
WHERE CSUM.CSUM_COMP_CODE = 'A' AND CSUM.CSUM_EMPL_CODE = SUBSTRING('85',1,2) AND LTRIM(RTRIM(CSUM.CSUM_TERR_CODE)) = LTRIM(RTRIM('MB2')) AND CSUM.CSUM_PROPRD BETWEEN '200610' AND '200611'
I'm trying to create an Insert query and I'm having difficulty in 2 areas:
First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.
INSERT INTO ITEM_MASTER SELECT * FROM ITEM_MASTER_TEMP
Second, I've tried the following"conversions" in the SELECT statement, to no avail:
CONVERT(Money, MILL_COST) As MILL_COST CONVERT(Money, CONVERT(Varchar(50), MILL_COST) CAST(MILL_COST AS Money)
I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com
I am struggling rewriting my query from MS Access' IIF, Then to SQLServers TSQL language. I am hoping some one can give me someguidance. I believe I have the first portion of the query correct butdo believe this requires a "NESTED" argument. This is where I amlost.My Original MS ACCESS Query reads--SELECT DISTINCTROW REGION_TRAFIC.*,IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTRA_VENDOR1])))),IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],IIf([Carrier]="VENDOR1",[DOMESTIC LDRATES]![INTER_VENDOR1]))))) AS CPM,[CPM]*[MOU] AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESC;I have tried to re-write this in SQL SERVER as --SELET DISTINCT REGION TRAFIC.*,CASEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTRA_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTRA_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTRA_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTRA_VENDOR1ELSEWHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LDRATES].INTER_VENDOR4WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LDRATES].INTER_VENDOR3WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LDRATES].INTER_VENDOR2WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LDRATES].INTER_VENDOR1ENDAS CPMCPM*MOU AS COSTINTO INTRALATA_LDFROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ONREGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEYWHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))ORDER BY REGION_TRAFIC.[TERM LATA] DESCMy challenge is the Case portion of the query and the nesting! I amnot sure if I have the correct syntax or even chose the correctargument for my purpose.Any guidance is appreciated.
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
How would I convert this query to ANSI 92 standard?
select p.InternalID, n.LastName from tblPatient p, tblName n where p.PatientID = n.EntID and n.EffDt = ( select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate() )
the question is : i have a query in Oracle like this select count(*), to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy') from ISL_USERS group by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy') order by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy');
i want to convert this into SQLserver but in SQL server equlent function for TO_CHAR is Datename ..when i have given this i am getting error . please give me the tip on this issue .
I have a field that was text YESNONull. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to YesNo inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return YesNoNull or should I just keep the field as a 3 byte text?
Code: SELECT Jobs_Table.JobNum, [Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces, The_Big_One1.HT_Mail_STId, IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC,
[Code] ....
or
Code: SELECT Jobs_Table.JobNum, [Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces, The_Big_One1.HT_Mail_STId, THE_Big_One1.STC_SCAN,
I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:
Original query:
SELECT distinct (select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:
I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Table2HTML] ( @TABLENAME NVARCHAR(500), @OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98 Unclosed quotation mark after the character string '</table></body><'. Msg 102, Level 15, State 1, Line 98 Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
I have a stored proc that is being executed from an OLE DB Source component in my Data Flow. Takes one input parm, has several variables declared within, a derived table which is used in a join and all within a try catch block with transaction handling. No updates, just returning data, works great, except now I have been asked to replace these stored procs with inline queries.
ALTER PROCEDURE [dbo].[usp_Get_Test] ( @numberOfMonthsint ) AS BEGIN SET NOCOUNT ON set transaction isolation level read uncommitted
[Code] ....
The problems I have run into so far are...
SQL command text in OLE DB Source Editor does not like: - TRY/CATCH block - Will not let me use my input parm (@numberOfMonths int) - When I hard code in my input parm (select @BeginDate = dateadd(MONTH, -1, GETDATE())) I can parse query and run the step but no results are returned. So I am let to assume that it does not like the @TESTY derived table.
The query here as a sample has had pivots removed as well, but research suggests this should be an issue in the SQL command text.
Also, I know not even to try the Build Query... cause it will complain about any variable declarations (i.e., declare @BeginDate datetime).
For instance, can I pull this off with an Execute SQL Task? The problem is I don't see this available in the toolbox for the Data Flow.
Also, would my error handling be done in the Event Handlers tab now and if so, is there a good example of this?
Hello,I have a View called View1 with the field ID, F1, F2, F3. Now I need to check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records (so if condition matches, I need to bring rows, not only totals) how can I write my view query to handle this?Thanks,
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255) SET @myvendedor = '87,30' print @myvendedor SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor) Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6 Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to numeric.
Is there a way to access your views in x database from the master database?
I'm getting this error
Invalid object name 'v_StatisticsScalars'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.
