SQL 2000
I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.*
into #cl_temp
FROM OLT.dbo.NACBTR NA
WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And
NA.completedDate >= '01/01/2006' and
NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
I have an Access database, that is in connection with sql server. On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] ) SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote: ODBC call failed [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1' does not match with a table name or alias name used in the query. The column prefix 'MS2' does not match with a table name or alias name used in the query.
I get the error below from the following SQL. This SQL worked until I tried adding a third table "ship_to_salesrep". If I comment-out the third join and the last condition it works.
I have tried putting the "r" table in the FROM statement and still it does not work. Everything looks right to me -- what am I doing wrong?
Server: Msg 107, Level 16, State 2, Line 2 The column prefix 'h' does not match with a table name or alias name used in the query.
SELECT distinct m.inv_mast_uid, CONVERT(DECIMAL(10,4),0.00) as 'inv_cost', CONVERT(DECIMAL(10,4),0.00) as 'oe_cost', l.invoice_line_type, s.cost, h.invoice_no, h.order_no, h.order_date, h.invoice_date, h.customer_id, h.ship_to_id, h.ship2_name, h.ship2_address2, h.ship2_city, h.ship2_state, h.ship2_postal_code, h.terms_desc, h.po_no, h.salesrep_id, h.salesrep_name, h.period, h.year_for_period, h.ship_date, h.total_amount, h.amount_paid, h.terms_taken, h.allowed, h.paid_in_full_flag, h.last_maintained_by, h.printed, h.printed_date, h.shipping_cost, h.invoice_reference_no, h.invoice_adjustment_type, h.memo_amount, h.bad_debt_amount, h.invoice_class, h.period_fully_paid, h.year_fully_paid, h.approved, h.other_charge_amount, h.tax_amount, h.original_document_type, h.date_paid, h.print_flag, h.print_date, h.customer_id_number, h.date_created, h.date_last_modified, h.consolidated, h.sold_to_ah_uid, h.sold_to_customer_id, h.invoice_batch_uid, h.sales_location_id, h.source_type_cd, l.qty_requested, l.qty_shipped, l.unit_of_measure, l.item_id, l.item_desc, l.unit_price, l.extended_price, l.gl_revenue_account_no, l.gl_salse_tax_account_no, l.pricing_quantity, l.line_no, l.sales_cost, l.commission_cost, l.other_cost, l.other_charge_item, l.exceptional_sales, l.pricing_unit, l.invoice_line_uid, l.invoice_line_uid_parent into jch1.dbo.sales_history_invoices FROM invoice_hdr h, invoice_line l left join inv_mast m on l.item_id = m.item_id left join inventory_supplier s on m.inv_mast_uid = s.inv_mast_uid left join ship_to_salesrep r on h.ship_to_id = r.ship_to_id WHERE l.invoice_no = h.invoice_no and h.invoice_date >= '2006-07-01' and h.invoice_date < '2006-08-01' and l.invoice_line_type = 0 and m.inv_mast_uid is not NULL and r.primary_salesrep_flag = 'Y';
column prefix 'a' does not match with a table name. Any ideas? This happens when i run the DTS package.
SELECT'Dublin' + SPACE(1) + CONVERT(VARCHAR(10), RTRIM(FS6.QB@SCS)) + REPLICATE(SPACE(1), 10 - LEN(CONVERT(VARCHAR(10),RTRIM(FS6.QB@SCS)))) + CONVERT(VARCHAR(10),a.IHIVDT,20) + CASE WHEN RM.RMRSSC IN ('01', '03', '04', '05', '14', '28', '34', '35', '36') AND RS.RSRSCL = 'FG' THEN 'CLOSURE' + SPACE(3) + 'TAPE' + SPACE(21) WHEN RM.RMRSSC IN ('02') AND RS.RSRSCL = 'FG' THEN 'CLOSURE' + SPACE(3) + 'ADHESIVE' + SPACE(17) WHEN RM.RMRSSC IN ('06', '07') AND RS.RSRSCL = 'FG' THEN 'UNITIZING' + SPACE(1) + 'STRETCH' + SPACE(18) WHEN RM.RMRSSC IN ('08') AND RS.RSRSCL = 'FG' THEN 'DISPLAY' + SPACE(3) + 'SHRINK' + SPACE(19) WHEN RM.RMRSSC IN ('09', '10', '15', '39', '40') AND RS.RSRSCL = 'FG' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25) WHEN RM.RMRSSC IN ('19', '22', '29', '30') AND RS.RSRSCL = 'FG' THEN 'SPECIALTY' + SPACE(1) + SPACE(25) WHEN RM.RMRSSC IN ('37') AND RS.RSRSCL = 'FG' THEN 'CUSHIONING' + 'MAILERS' + SPACE(18) WHEN RM.RMRSSC IN ('38') AND RS.RSRSCL = 'FG' THEN 'CUSHIONING' + 'BUBBLE' + SPACE(19) WHEN RM.RMRSSC IN ('11') AND RS.RSRSCL = 'RM' THEN 'SPECIALTY' + SPACE(1) + SPACE(25) WHEN RM.RMRSSC IN ('14', '17') AND RS.RSRSCL = 'RM' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25) WHEN RM.RMRSSC IN ('12', '13', '15', '16', '51') AND RS.RSRSCL = 'RM' THEN 'SPARE PART' + SPACE(25) WHEN RM.RMRSSC IN ('08', '09') AND RS.RSRSCL = 'WP' THEN 'EQUIPMENT' + SPACE(1) + SPACE(25) ELSE SPACE(35) END + CONVERT(VARCHAR(50), RTRIM(RM.RMDESC)) + REPLICATE(SPACE(1), 50 - LEN(CONVERT(VARCHAR(50), RTRIM(RM.RMDESC)))) + CASE WHEN RM.RMRSSC IN ('06','07') AND RS.RSRSCL = 'FG' THEN CASE WHEN RM.RMPRCL IN ('134', '135', '137', '159', '1073') THEN 'STRETCH-MACHINE' + SPACE(10) ELSE 'EXCLUDED' + SPACE(17) END WHEN RM.RMRSSC IN ('01') AND RS.RSRSCL = 'FG' THEN CASE WHEN RM.RMPRCL IN ('173', '175') THEN 'HOT MELT HAND ROLL' + SPACE(7) WHEN RM.RMPRCL IN ('174', '174L') THEN 'CARTON SEALING' + SPACE(11) ELSE 'EXCLUDED' + SPACE(17) END WHEN RM.RMRSSC IN ('05', '28') AND RS.RSRSCL = 'FG' THEN 'CARTON SEALING' + SPACE(11) WHEN RM.RMRSSC IN ('03', '04', '14', '34', '35') AND RS.RSRSCL = 'FG' THEN 'INDUSTRIAL TAPES' + SPACE(9) ELSE 'EXCLUDED' + SPACE(17) END + CASE WHEN RM.RMRSSC IN ('06','07') AND RS.RSRSCL = 'FG' THEN CASE WHEN RM.RMPRCL IN ('134', '135', '137', '159', '1073') THEN 'STRETCH-MACHINE' + SPACE(10) ELSE 'EXCLUDED' + SPACE(17) END WHEN RM.RMRSSC IN ('01') AND RS.RSRSCL = 'FG' THEN CASE WHEN RM.RMPRCL IN ('173', '175') THEN 'HOT MELT HAND ROLL' + SPACE(7) WHEN RM.RMPRCL IN ('174', '174L') THEN 'HOT MELT MACHINE ROLL' + SPACE(4) ELSE 'EXCLUDED' + SPACE(17) END WHEN RM.RMRSSC IN ('03', '04', '14', '34', '35') AND RS.RSRSCL = 'FG' THEN 'INDUSTRIAL TAPES' + SPACE(9) WHEN RM.RMRSSC IN ('05') AND RS.RSRSCL = 'FG' THEN 'WATER ACTIVATED' + SPACE(10) WHEN RM.RMRSSC IN ('28') AND RS.RSRSCL = 'FG' THEN 'NATURAL RUBBER' + SPACE(11) ELSE 'EXCLUDED' + SPACE(17) END + CASE WHEN RM.RMRSSC IN ('06','07') AND RM.RMPRCL IN ('134', '135', '137', '159', '1073') AND RS.RSRSCL = 'FG' THEN CASE WHEN a.IHCRMM = 0 THEN CASE WHEN (IV.IVQYSD * RM.RMNETW)IS NULL THEN '000000000000000' WHEN (IV.IVQYSD * RM.RMNETW) > 0 THEN '+' + REPLICATE('0', 23 - LEN(IV.IVQYSD * RM.RMNETW)) + SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), 1, LEN(IV.IVQYSD * RM.RMNETW) - 13) + SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), LEN(IV.IVQYSD * RM.RMNETW) - 11, 4) WHEN (IV.IVQYSD * RM.RMNETW) = 0 THEN '000000000000000' END WHEN a.IHCRMM IN (1,3) THEN '000000000000000' WHEN a.IHCRMM = 2 THEN CASE WHEN (IV.IVQYSD * RM.RMNETW)IS NULL THEN '000000000000000' WHEN (IV.IVQYSD * RM.RMNETW) > 0 THEN '-' + REPLICATE('0', 23 - LEN(IV.IVQYSD * RM.RMNETW)) + SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), 1, LEN(IV.IVQYSD * RM.RMNETW) - 13) + SUBSTRING((CONVERT(VARCHAR(20), IV.IVQYSD * RM.RMNETW)), LEN(IV.IVQYSD * RM.RMNETW) - 11, 4) WHEN (IV.IVQYSD * RM.RMNETW) = 0 THEN '000000000000000' END ELSE '000000000000000' END WHEN RM.RMRSSC IN ('01','03', '04', '05', '14', '28', '34', '35') AND RS.RSRSCL = 'FG' THEN CASE WHEN a.IHCRMM = 0 THEN CASE WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NULL THEN '000000000000000' WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NOT NULL THEN '+' + REPLICATE('0',17-LEN(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)))) + SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), 1, LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 13) + SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 11, 4) ELSE '000000000000000' END WHEN a.IHCRMM IN (1,3) THEN '000000000000000' WHEN a.IHCRMM = 2 THEN CASE WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NULL THEN '000000000000000' WHEN LTRIM(RTRIM(IV.IVUMSR)) IS NOT NULL THEN '-' + REPLICATE('0',17-LEN(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)))) + SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), 1, LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 13) + SUBSTRING(CONVERT(VARCHAR(15),CONVERT(NUMERIC(13,6),(SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD)), LEN((SELECT UC.UMCONF FROM IPGDB.DBO.UMCONV UC WHERE UC.UMRESR = IV.IVRESR AND UC.UMFR = IV.IVUMSR AND UC.UMTO = 'M2' AND UMWHSE = '') * IV.IVQYSD) - 11, 4) ELSE '000000000000000' END ELSE SPACE(15) END ELSE SPACE(15) END + CASE WHEN a.IHCRMM NOT IN (1,2) THEN CASE WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) IS NULL THEN SPACE(13) WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) > 0 THEN '+' + REPLICATE('0', 13 - LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))) + SUBSTRING((CONVERT(VARCHAR(13), IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))), 1, LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT)) - 3) + RIGHT(CONVERT(VARCHAR(13), (IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))),2) WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) = 0 THEN '0000000000000' ELSE SPACE(13) END ELSE CASE WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) IS NULL THEN SPACE(13) WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) > 0 THEN '-' + REPLICATE('0', 13 - LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))) + SUBSTRING((CONVERT(VARCHAR(13), IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))), 1, LEN(IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT)) - 3) + RIGHT(CONVERT(VARCHAR(13), (IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT))),2) WHEN IVSALT - (IVDIST + IVADIT + IVODIT + IVOADT) = 0 THEN '0000000000000' ELSE SPACE(13) END END + CASE WHEN a.IHCRMM IN (0) THEN CASE WHEN IV.IVQYSD IS NULL THEN '00000000000' WHEN IV.IVQYSD > 0 THEN '+' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD)) ELSE '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD)) END WHEN a.IHCRMM IN (1,3) THEN '00000000000' WHEN a.IHCRMM IN (2) THEN CASE WHEN IV.IVQYSD IS NULL THEN '00000000000' WHEN IV.IVQYSD > 0 THEN '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD)) ELSE '-' + REPLICATE('0', 10 - LEN(CONVERT(INT,IV.IVQYSD))) + CONVERT(VARCHAR(11),CONVERT(INT,IV.IVQYSD)) END ELSE '00000000000' END as 'XPEDXRESULTS' FROMIPGDB.dbo.INVHDR AS a LEFT OUTER JOIN IPGDB.dbo.FSA06 AS FS6 ON a.IHSOCS = FS6.QB@CUS AND a.IHICON = FS6.QB@CMP LEFT OUTER JOIN IPGDB.DBO.INVDET AS IV ON a.IHICON = IV.IVICON AND a.IHINVA = IV.IVINVA LEFT OUTER JOIN IPGDB.DBO.RESMST AS RM ON RM.RMRESC = IV.IVRESR LEFT OUTER JOIN IPGDB.DBO.RESUBC AS RS ON RS.RSRSSC = RM.RMRSSC AND RS.RSRSCL = RM.RMRSCL WHEREFS6.QB@BGP = '133' AND MONTH(a.IHIVDT) = ? AND YEAR(a.IHIVDT) = ? AND IV.IVRESR IS NOT NULL
I have an Access database, that is in connection with sql server. On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] ) SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says: ODBC call failed [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'MS1' does not match with a table name or alias name used in the query. The column prefix 'MS2' does not match with a table name or alias name used in the query.
I have a program that connects to SQLServer 2000 through ADO connection.
the program executes the following query:
SELECT ax.AccNo, (SELECT Accounts.ProductCode FROM Accounts WHERE h.ID=Accounts.ID) As Product FROM dbo.History h LEFT OUTER JOIN dbo.AccXRef ax ON h.ID= ax.ID LEFT OUTER JOIN dbo.States ON h.[HistoryItemsub-Type] = dbo.States.Type LEFT OUTER JOIN dbo.CustXRef cx ON h.CustomerNo = cx.CustomerNo WHERE HistoryItemDate <= getdate() ORDER BY HistoryItemDate ASC
This query works in th program and in Query Analyer on my machine. However, On a different Machine (and different SQLServer) the query works in Query Analyser but does not work in the program, the following exception is thrown:
The column prefix 'h' does not match with a table name or alias name used in the query
Can someone please answer a problem that I've run into. I know that it's probably something stupid. I keep getting this error:Server: Msg 107, Level 16, State 3, Line 1The column prefix 'vFirstTimeEntered' does not match with a table name or alias name used in the query.Here is my query:-----------------------------------------------------------------Update TimeSheetSectionSet TimesheetSection.SECSTARTDT = vFirstTimeEntered.schlstuidWhere timesheetsection.schlstuid = vFirstTimeEntered.schlstuid AND timesheetsection.sectionid = vFirstTimeEntered.sectionid AND Timesheetsection.secstartdt < '2005-08-01'------------------------------------------------------------------vFirstTimeEntered is a view that I created.Do I need a sub query? I know that if this was a select query I'd need to put vFirstTimeEntered in the FROM part but I don't know where it should go here.Thanks for any assistance.Scott
I get the error "The column prefix 'contacts' does not match with a table name or alias used in the query".I am trying to obtain all fields from the communications table whether it is used or NULL.
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE ( OuterID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL, InnerID int NULL )
-- UPDATE Outer Table with best match from Inner table UPDATE @Outer SET InnerID = IJ.InnerID FROM @Outer OJ INNER JOIN ( SELECT TOP 1 I.* FROM @Inner I WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID AND IsNull(I.CompID, OJ.CompID) = OJ.CompID AND IsNull(I.FormID, OJ.FormID) = OJ.FormID ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC ) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID) AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID) AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5. Can anyone help me? Thanks in advance.
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4 View2, Column4 View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/ -- Create 2 test views IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]')) DROP VIEW dbo.[View1] GO CREATE VIEW View1 AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
I have two tables in the same DB, one named “Client” one named “Case”.
-One column inside Client is “ClientID”, another is “ClientCode”.
-One column inside Case is “CaseID” and another is “ClientCode”, which corresponds to the ClientCode column in the previous table.
I need a file with output that looks like this CaseID+ClientID.
So, an example row from the “Client” table” could be ClientID = 123, ClientCode=999. An example row from the “Case table” could be CaseID=555, ClientCode=999. So, I need output of 555123 .
How to search a string from the given values. i want to search a string "Session" from the given column of results.. it is separator by comma. i want only 2 results from the given value... if i'm writing as like keyword it will return 4 but i need only the exact match of string.. _______________________ The Result should be Session,Study Patterns, session, asp.net _______________________ But the Result is coming as Session study, usercontrol Session, study Technical Session, Asp.net Patterns, session, asp.net ________________________ anyone tell the solution
books catalog, education, best books Birthday, Party Gopi Session study, usercontrol Session, study Holiday Technical Session, Asp.net Patterns, session, asp.net day, party events for Lords, daily thing events manager events things meeting, administrator marriage project ,event, demo madurai ,event demo, event calendar rangoli, event Demo Project event project
I have a table Customer with column name "SerNo" the value of SerNo column is like below.
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3 Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3 Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3 Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7 Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7 Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
[code]...
I want to join it with nother table "Order" which has a SerNo column but does not have first part of SerNo.
When I give studentId 10 and class ids = 100, 101, 102, 103 then result should be get row from student table only if all given class ids matched.
Result: Id Student ID ClassId 1 10 100, 101, 102, 103
Case 2: Student Id: 10 class Ids = 100, 101 the no results since all the class ids for student 10 in Student_Class are not matching with the given class Ids parameter.
I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
It says : The number of columns in the referencing column list for foreign key 'staff_fk' does not match those of the primary key in the referenced table 'Staff'.
I have a table names Alert_Event and a new column named BSP_Phone has been added to the table. I am trying to set NULL values to the column and I get the below error. I am setting null values in the bolded text in the query.
Error Message:
Msg 213, Level 16, State 1, Procedure SaveBSPOutageInfo, Line 22 Column name or number of supplied values does not match table definition.USE [gg] GO
/****** Object: StoredProcedure [dbo].[SaveBSPOutageInfo] Script Date: 10/17/2013 19:01:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SaveBSPOutageInfo] @eventCreatedDate DATETIME, @eventOrigin varchar(10),
Table2 has three columns (i.e. Date, Count and Rule Type). Column “Rule Type “has a default value which is “XYZ”..Now I want to insert Data from Table1 to Table2. I am using following query:-
Column name or number of supplied values does not match table definition.I am using SQL 2012. I understand Table1 has 2 Columns but Table2 has 3 Columns. Is there anyway, I can move data from source table to Destination table where Destination Table has more number of Columns?
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
Am I right in saying that the MS Full-text search engine does not support a wildcard at the beginning of the word? Only at the end? E.g. "VB*" works but "*NET" doesn't.
in Access 2000 and cannot find the table on my table list, it's not on my enterprise manager either. My guess is that maybe it has something to do with the prefix #? So I started searching for "Create table #" on google and dbforums and cannot find anything. Can someone tell me what's special about having a #prefix on a table and where can I find more information about it?
What does the ap_ stand for as a prefix to stored procedures? A DBA tried to teach me something about the prefixes etc.. but I can't recall what it meant. In this case ap_ may represent "ONE" persons naming convention? something procedure... other common ones in our database are dt_ and zz_
Suppose there is a table containing these recodes.country-------CON_CHNCON_JAPJAPCON_CHNWhen I use the following sql:select country, count(*) as num from table group by countrythe normal result will be:country num---------------CON_CHN2CON_JAP 1JAP1However, my desired result is as follows:country num-----------------CON_CHN2CON_JAP 2How can I re-write my SQL? Or any other methods to do that?
Hi,I would like to know details about the table name starts with prefixin sql server 2000.Actually i'm working on existing code.The existing code insert a record into a table, but the table name inthe code and table name in database are differentTable name in database : tbl_mmm_oxIn coding they are using table name as mmm only, the records areproperly inserted into mmm tableis it possible?The sample code is like that(using ado object)oCmd.CommandText = "INSERT INTO mmm (no,name) values (1,"mm")"The above code is perfectly working and inserting record intotbl_mmm_ox.Could anybody explain how is it possible?whether we can leave the prefix(tbl) and suffix(ox) and sql servertake care of this?Thanks & Regards,Mani
