Referencing Column List For Foreign Key No Match

Oct 26, 2013

I'm encountering this very weird problem, so I create a staff table:

CREATE TABLE Staff (
staffNo numeric(10),
venueNo numeric(10),
name nvarchar(20),
DOB datetime,
position nvarchar(20),
salary numeric(8,2)
CONSTRAINT staff_PK PRIMARY KEY(staffNo, venueNo),
CONSTRAINT venue_FK FOREIGN KEY(venueNo) REFERENCES Venue
);

and then when I create a professional therapist table

CREATE TABLE Professional_Therapist (
staffNo numeric(10),
specialization nvarchar(20),
bonus numeric(8,2),
CONSTRAINT professional_PK PRIMARY KEY(staffNo),
CONSTRAINT staff_FK FOREIGN KEY(staffNo) REFERENCES Staff
);

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'.

View 3 Replies


ADVERTISEMENT

Insert Value List Doest Not Match Column List

Apr 18, 2007

HI...



I need to do a simple task but it's difficult to a newbie on ssis..



i have two tables...



first one has an identity column and the second has fk to the first...



to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!



i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"



here is the script



INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)



what's the problem ??

View 7 Replies View Related

Mind Boggling / How Can You Query Self Referencing Tables? (self Referencing Foreign Keys)

Jun 15, 2006

For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID  . . . . . . . .  .  .  int
Name  .  .  .  .  .  .  .  .  .  .  .  nvarchar(50)
ManagerID  . . . . . . . .  .  .  .  int
 
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?

View 6 Replies View Related

Foreign Key NOT Referencing PK

Sep 18, 2006

This book 'MS SQL Server 2000 Bible' says the following:

'The foreign key can reference primary keys, unique constraints, or unique indexes of any table'

I've never heard of this before, I thought that the FK always established referential integrity by referencing the PK in the parent table. Does anyone have a further explanation of this?

thx,

Kat

View 5 Replies View Related

UniqueIdentifier Self Referencing Foreign Key

May 16, 2008

I have a UniqueIdentifier as a self referencing foreign key. The pk gets set by default using the newid(). I need the foreign key to default to that same value. how can i do that? @@identity doesnt work, $rowguid doesnt work, column name doesnt work. Any ideas?

View 9 Replies View Related

DB Design :: Foreign Key Referencing Non Unique Field In Server

Oct 12, 2012

I ran into an interesting situation.  I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem.  I found FK's that are referencing columns that do not have a unique constraint or a unique index. 

I don't know the history of the database but was there a time in SQL Server history where this would have been possible?  I scripted out the tables and created it in a test database.  When I run the script to create the FK I get the following message. 

I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2.  Currently the database is running on SQL Server 2008 Ent.

ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1])
REFERENCES [dbo].[table2] ([col2])

There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.

View 4 Replies View Related

How To Refer A Column When The Referencing Column Is An Identity Column

Oct 16, 2006

Hi all,

The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..

i cannot implement this constraint, it throws the error when i execute the below Alter query,

ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE

the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.

any workarounds for this ?

View 3 Replies View Related

SQL 2012 :: Number Of Variables Declared In INTO List Must Match That Of Selected Columns

Apr 28, 2015

I am getting error [[Msg 16924, Level 16, State 1, Line 13

Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.

Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)

DECLARE Dropuser_Cursor CURSOR FOR

[Code] ....

View 9 Replies View Related

List Primary And Foreign Keys

Jan 10, 2006

I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.

Your help would make my life a lot easier

 

thanks

 

View 1 Replies View Related

Referencing A Custom Column

Feb 27, 2008

Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.

I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement

for example

select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null

This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.

Is this possible and what would be the best way of doing this?

I do not seem to be about to reference JobType in the WHERE statement.

View 10 Replies View Related

Referencing A Custom Column

Feb 27, 2008

Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.

I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement

for example

select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null

This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.

Is this possible and what would be the best way of doing this?

I do not seem to be about to reference JobType in the WHERE statement.

View 1 Replies View Related

CHECK Constraint - Referencing Another Column

Aug 31, 2000

I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???

Thanks in advance!

Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'end_date' references another column, table 'Session'.

Here's an example of the script that I'm using:
CREATE TABLE Session (
session_key char(18) NOT NULL,
course_key char(18) NOT NULL,
site_key char(18) NOT NULL,
instructor_key char(18) NOT NULL,
start_date smalldatetime NULL,
end_date smalldatetime NULL
CHECK (end_date >= start_date)
)


.

View 1 Replies View Related

Creating A Form With A Drop Down List Of The Foreign Key Values

Jun 19, 2002

I have the following tables in an SQL Server database.

Contact
=======
NameID
TitleID
Name

JobTitle
========
TitleID
Title

I'm trying to make a form to add entries to the Contact table that lets me associate a job title to a name by choosing it from a drop down list. I have been able to make it so I get a drop down list of TitleID but I need the Title field displayed. I haven't been able to do it. Does anyone have any tips on how to do this?

View 1 Replies View Related

SQL Server Table's Column Referencing Two Parent Tables.

Feb 15, 2008

Hi everybody, 
I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?
 
 

View 2 Replies View Related

Problem Referencing A Column In A Table Variable In A Query.

Apr 23, 2006

Hello All

I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.

declare @THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable

insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable

UPDATE @THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable

Update @THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable

- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt
FROM @THeader, mtblTransactionHeader
WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table


Thanks in advance

smHaig









View 1 Replies View Related

Correlated Subquery Column Referencing Outer Date Range

Aug 25, 2006







Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?

Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)

select
OrderDate,
ShipCountry,
ShipCity,
Freight,
/* how do I get to the outer query's date range ? */
(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
Northwind.dbo.Orders O
where
ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'


Thanks

View 9 Replies View Related

Match Value From Column In T1 To T2?

Nov 11, 2014

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 .

View 5 Replies View Related

Column Prefix Does Not Match

Oct 2, 2006

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.

Any ideas?
Thanks,
Tom
btw, sql newbie.

View 5 Replies View Related

The Column Prefix 'MS1' Does Not Match With A ...

Aug 29, 2007

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.

And it will give this error 6 times.

I dont know what to do.
Can anybody help me?

View 2 Replies View Related

The Column Prefix 'h' Does Not Match... Parses Ok

Aug 18, 2006

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';


Thanks,
Charles

View 3 Replies View Related

Search Exact Match Within A Column

Apr 15, 2008

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

View 2 Replies View Related

Column Prefix 'a' Does Not Match With A Table Name

Aug 10, 2007

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



=============================
http://www.sqlserverstudy.com

View 5 Replies View Related

Transact SQL :: Match Column Value In Where Clause

Jun 4, 2015

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.

SerNo.
9ad88929-f32d-459e-96c4-8d6c3d61e9d9
0a1d8b8f-7f5f-4a01-8e63-64579213afef
9342f8d7-dfdd-4535-8f01-5301bde669aa

[code]...

So basically i want to join these two tables and ignore the first part before "-" from SerNo column in customer table.

View 5 Replies View Related

Transact SQL :: Get Row If All Column Values Match

Nov 10, 2015

Please don’t look for table design satisfies NF, this is just for example

Student table
Id            StudentId
1                    10
2                    20
3                    30

Student_Class
Id            ClassId
1              100
1              101
1              102
1              103
2              100
2              101

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.

View 11 Replies View Related

The Column Prefix 'MS1' Does Not Match With A Table Name Or Alias Name Used In The...

Aug 29, 2007

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.

And it will give this error 6 times.

I dont know what to do.
Can anybody help me?

View 4 Replies View Related

Finding Matching Data In One Column That Does Not Match In Another

Nov 18, 2005

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

Any ideas?

View 2 Replies View Related

The Column Prefix 'h' Does Not Match With A Table Name Or Alias Name Used In The Que

Mar 17, 2004

Hi Guys,

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


Any help is greatly appreciated..

thanx in-advance,

TNT:)

View 5 Replies View Related

SQL Msg 107 Error... The Column Prefix Does Not Match With A Table Name Or Alias Name Used In The Query.

Nov 3, 2005

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

View 1 Replies View Related

Column Prefix Contacts Does Not Match With A Table Name Or Alias Used In Query

Apr 22, 2014

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.

SELECT organisations.organisation_number, organisations.contact_number, organisations.name, organisations.address_number,
organisations.std_code, organisations.telephone, organisations.status, contacts.title, contacts.initials, contacts.forenames,
contacts.surname, contacts.contact_number, contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes

[code]....

View 9 Replies View Related

Correlated Subquery - Column Prefix 'OJ' Does Not Match With A Table Name Or Alias Name Used In The Query.

Feb 2, 2007

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
)

INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2

DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)

INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3

INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5

INSERT @Inner VALUES (4, 2, 1) -- InnerID 6


-- 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.

View 6 Replies View Related

Error - Column Name Or Number Of Supplied Values Does Not Match Table Definition

Oct 17, 2013

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

[code]....

View 3 Replies View Related

Miss Match Between Column Type In A Cursor. Msg 8114, Level 16, State 5

Nov 16, 2006

hi

I have a float column . only in cursor I get a type missmatch.

does any one knows it ?

the error

Msg 8114, Level 16, State 5, Line 14

Error converting data type varchar to float.

the code

DECLARE @RON_FLOAT FLOAT

DECLARE RON_CURSOR CURSOR FOR

SELECT RON_FLOAT

FROM RON_TABLE1

OPEN RON_CURSOR

FETCH NEXT FROM RON_CURSOR

INTO @RON_FLOAT

WHILE @@FETCH_STATUS = 0

BEGIN



PRINT 'VALUE IS ' + @RON_FLOAT

FETCH NEXT FROM RON_CURSOR

INTO @RON_FLOAT

END

CLOSE RON_CURSOR

DEALLOCATE RON_CURSOR



the code for the table

CREATE TABLE [dbo].[RON_TABLE1](

[RON_FLOAT] [float] NULL,

[RON_CHAR] [nchar](10) COLLATE Hebrew_BIN NULL

)

View 1 Replies View Related

Transact SQL :: Column Name Or Number Of Supplied Values Does Not Match Table Definition

Sep 15, 2015

I have two tables (i.e. Table1 and Table2).

SELECT
* FROM [dbo].[Table1]

 Date
id
9/15/2015

[code]...

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:-

declare @Startdate
datetime
DEclare @enddate
datetime

[code]...

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? 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved