SP: Number Of Params Does Not Match Table Definition

Jun 20, 2000

I am getting an insert error with the following SP. I don't have to pass the CampID because it is an IDENTITY field. The error says "number of supplied values does not match table definition."

Do I pass in the CampID to the SP and allow nulls? Thanks in advance

Nathan


CREATE PROCEDURE sp_CampReg1
@UserNamevarchar(15),
@Passwordvarchar(15),
@CampNamevarchar(50),
@Hostvarchar(50),
@Directorvarchar(25),
@Contactvarchar(25),
@Addressvarchar(30),
@Cityvarchar(25),
@Statevarchar(20),
@Zipvarchar(15),
@Countryvarchar(20) = NULL,
@Phonevarchar(20) = NULL,
@AlternatePhonevarchar(20) = NULL,
@Faxvarchar(20) = NULL,
@ContactEmailvarchar(20),
@AdminEmailvarchar(20),
@URLvarchar(50) = NULL,
@CampTypeint,
@CampProfileText =NULL,
@CampIDintOUTPUT

AS

INSERT INTO TempCampSignup
VALUES
(
@UserName,
@Password,
@CampName,
@Host,
@Director,
@Contact,
@Address,
@City,
@State,
@Zip,
@Country,
@Phone,
@AlternatePhone,
@Fax,
@ContactEmail,
@AdminEmail,
@URL,
@CampType,
@CampProfile
)

SELECT @CampID = @@IDENTITY

View 1 Replies


ADVERTISEMENT

Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table

Jun 6, 2005

Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

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.

Thanks,
Greg.

View 2 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

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

Data Access :: Column Name Or Number Of Supplied Values Does Not Match Table Definition

Jun 22, 2015

I'm executing a stored procedure but got error :

Msg 213, Level 16, State 1, Procedure ExtSales, Line 182
Column name or number of supplied values does not match table definition.

View 5 Replies View Related

Transact SQL :: Insert Using Select Intersect / Except - Number Of Supplied Values Not Match Table Definition

May 12, 2015

I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created

CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)

The following stmt won't work...

INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C

... as the number of supplied values does not match table definition.

View 2 Replies View Related

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

Mar 24, 2008

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 + ' ')   
 
 

View 4 Replies View Related

Report Run OK W/ Default Params But Not Changed Params

Mar 25, 2008

I have a set of reports that run just fine with the default parameters (Country = US). The report returns data within 60 seconds. However, if I change the default parameters, say to Country = UK, the report will run and won't seem to stop. The user will be prompted every few minutes to relogin to the domain (which they are not prompted when they first run the report). On the server, the report is taking up 1 of the four CPU's and is using a huge amount of disk paging.

Here's the kicker. If I go in, change the default parameters to Country = UK and deploy the report, it will run in 60 seconds with the new default parameters. Now I try to run the report by changing the country = US and it locks up when it is executed.


Anybody run into something like this?

Rob

View 5 Replies View Related

String Match Number Of Different Columns

Apr 24, 2008

Hi,

In short:
I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s)

Problem:
I got a set of rows with varchar(50), spread out over multiple tables.
All those tables relate to a central Colour table.
For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id

E.g: input: 'BLACK', 'MERCEDES', '1984'
Would return colour ids "025864", 45987632", "65489" and "63249"
Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'.

Current Situation:
I) Create a table containing all possible values
CREATE TABLE dbo.CommonSearch(
id int IDENTITY (1, 1) NOT NULL,
clr_id int,
keyWord varchar(40),
fieldType varchar(25)
And fill it with all the values (671694 rows)
)
II) Stored Procedure to cut a string up into a table:
CREATE FUNCTION dbo.SplitString
(
@param varchar(50),
@splitChar char = ''
)
RETURNS
@T TABLE (keyWord varchar(50))
AS
BEGIN
WHILE LEN( @param ) > 0 BEGIN
declare @val varchar(50)
IF CHARINDEX( @splitChar, @param ) > 0
SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) ,
@param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) )
ELSE
SELECT @val = @param, @param = SPACE(0)
INSERT INTO @T values (@val)
END
RETURN
END
III)Stored Procedure to query the first table with the second one
CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings]
@keyWords varchar(255) = ''
AS
BEGIN
SET NOCOUNT ON;
select clr_id, keyWord, fieldType
from dbo.commonSearch
where keyWord in (select * from splitString(@keyWords, ''))
END

So, how can I use a LIKE statement in the IN statement of the last query.
Furthermore, I was wondering if this is the best sollution to go for.
Are there any better methods? Got any tuning tips to squeeze out an extra second?

View 5 Replies View Related

Length Specified In Network Packet Payload Did Not Match Number Of Bytes Read

Mar 8, 2008

Hello every one
I am getting this in my event log from time to time . not sure what is that ?
Is this a hacker trying to send rubbish data to SQL through the port ?
Any help is appreciated .

Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: someip]

View 2 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

SQL Server 2008 :: Length Specified In Network Packet Payload Did Not Match Number Of Bytes Read

Mar 2, 2015

Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xxx.xx.xxx.xx]

Client IP address is same as the server its producing the error on. I get these messages around 12pm everyday.

View 3 Replies View Related

Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records

Nov 19, 2015

If I just use a simple select statement, I find that I have 8286 records within a specified date range.

If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277

Is there something I am doing incorrectly in the following sql?

DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct

[Code] ....

View 20 Replies View Related

Altering Table Definition On A Linked Server Table.

Aug 11, 2000

SQL Server 7.0 (SP1)
Error:
------
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.

Thanks for any input.

View 1 Replies View Related

How To Get Table Definition

Mar 6, 2008

Hi,
I need a query to get table Definition in sql server 2005.
please help me out.



Raghu sunkara.

View 6 Replies View Related

Reorder The Table Definition Of A SQL 2005 Table

Aug 9, 2007

Hi,Is it possible to reorder the table definition in a SQL 2005 table?Example : i want to put to table definition in alphabetical order.Thanks in advance

View 8 Replies View Related

Allow User To See Table Definition

May 8, 2008

I have set up a couple of views for a user but they want to be able to see the database table columns by doing right click on table and getting the columsn.

I tried to give the permissions to database by right clicking on database and doing the user and giving view defintion.

-- [TABLEA] contains no columns that can be inserted or the current user does not have permissions on that object.

User not allowed to select the data .

Thanks

View 2 Replies View Related

Table Definition Documentation

Dec 1, 2006

Hosam writes "Dear Team

Im new to SQL server 2005, and I wonder if I can create the database documentation, I mean table schema and attributes as word or excel file.

Is there anyone can help me please

Thanks
Hosam"

View 1 Replies View Related

Check Table And STP Definition ?

Feb 7, 2008

Hi SQL Professionals ,


I have a requirement like this,

I have a Live Database as well as the Test database with the same Definition

now i need to write a SQL to check identity of these two database tables,

i mean i need to check if the Test Database has got the Same Table definition as Live Database table definition ?
In the same way how do i check for the Stored peocedures ?




how do i write a SQL for this ?


regardssuis

View 4 Replies View Related

What Is The True Definition Of Catalog Table?

Jul 23, 2005

We hear about catalog table in documentation but is this the same asdatabase schema?What is the definiation of catalog table?What does it pertain to?Thanks--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Best Practices: Table Definition Updates

Feb 5, 2008



Hi. We're using SQL Server 2005 Express Edition for maintaining a relational DB for our soon-to-be released .NET app. The problem is that we expect that the definitions of the tables in the DB to occasionally change over time as we make updates to the software. Thus, during the installation of a software upgrade, we expect to run an SQL script that grabs the data from a table in its "old" format, re-structures the table, and then deposits the data back in the updated table. This seems to require some sort of version stamp on the table definition.

My main question is: What is the conventional way for handling versioning of table definitions?

Another question is: Is there a preferred procedure for handling the updating of the data during the installation of a software update?

Thanks.

Dave

View 3 Replies View Related

SQL Script To Show The Table Definition In MSSQL

Dec 7, 2005

Just like "describe select(*) from table_name" in DB2?

Thanks!

View 6 Replies View Related

Indexing View Definition Table Columns

Sep 29, 2007

Hi experts,


Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns


CREATE View hrinu.Parity as
select
T1.Matcle as CorpID,
T2.Nmpres as Name,
T4.DATDEB as LeaveFrom,
T4.TEMDEB as PM,
T4.DATFIN as LeaveTo,
T4.TEMFIN as AM,
T10.LIBLON as LeaveType,
T8.LIBLON as Location,
T12.LIBLON as ParentOrg

from HRINU.zy00 T1,
HRINU.zy3y T2,
HRINU.zy39 T3,
HRINU.zyag T4,
HRINU.zy38 T5,
HRINU.zy1s T6,
HRINU.zd00 T7,
HRINU.zd01 T8,
HRINU.zd00 T9,
HRINU.zd01 T10,
HRINU.zd00 T11,
HRINU.zd01 T12
where T4.Nudoss = T3.nudoss
and T4.Nudoss = T1.Nudoss
and T1.Nudoss = T2.nudoss
and T3.nudoss = T5.nudoss
and T6.nudoss = T1.nudoss
AND T7.NUDOSS = T8.NUDOSS
AND T9.NUDOSS = T10.NUDOSS
AND T11.NUDOSS = T12.NUDOSS
AND T3.IDWKLO = T7.CDCODE
AND T4.MOTIFA = T9.CDCODE
AND T5.IDESTA = T11.CDCODE
and T6.stempl = 'A'
and t7.cdstco = 'z04'
AND T8.CDLANG = 'U'
and t9.cdstco = 'DSJ'
AND T10.CDLANG= 'U'
and t11.cdstco= 'DRE'
AND T12.CDLANG= 'U'
and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00
and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00
and T6.dtef1s <= getdate() and T6.datxxx > getdate()


Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables.
Also throw some light on how to analyse the execution plan for further enhancements.



Thanks in advance


Regrds
Arvind L

View 3 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!



But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Analysis :: Back Color Property - Match Number To Desired Color?

Aug 6, 2015

When using the back color property for SSAS 2008 R2, is there a good way to match the number to the desired color?  I found some color pickers online, but the numbers don't match the same colors in SSAS.  How can I best determine the number needed for the color I want?

View 2 Replies View Related

Transact SQL :: Insert A Record Into Second table Where There Is Not A Match In First Table

Jun 25, 2015

I have a table in different databases with the same name.  My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table.  So far, my query looks like the following:

SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy]
EXCEPT
SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]

This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy].  Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy].  How can I modify the above code to INSERT the missing records?

View 4 Replies View Related

Table Size Definition - Small/medium/large

Jul 18, 2006

Hi,

Please could you tell me how big sql tables are when people refer to them as small, medium and large? Preferably in terms of disk space or rows (each row in my table will contain a standard length job advert and 20 additional columns with an average of 8 characters)

Thanks for your help! :-)

Stu

View 3 Replies View Related

How To Get Information From System Table Or View For Trigger's Definition

May 29, 2008

Such as check all triggers that assign value to some columns ?

Thank you very much.

View 1 Replies View Related

Sql 2005 Updating Table Definition With 'large' Amounts Of Data - Timeout

Feb 4, 2006

I'm trying to move my current use of an sql 2000 db to sql 2005.



I need to update a table definition (to change a field to an Identity)



I'm getting a dialog box (in SQL server management studio) on save saying :



'xxxx' table

- Saving Definition Changes to tables with large amounts of data could
take a considerable amount of time. While changes are being
saved, table data will not be accessible.



I press 'Yes' to the dialog box.



After 35 seconds, I get another dialog box saying:



'xxxx' table

- Unable to modify table.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.



Well, the server is responding and I can query that talbe and other, I
can add/delete rows to other columns. I can modify other
(smaller) tables.



Any ideas where I can change this timeout?



Daniel

View 10 Replies View Related

Find Value Match In SQL Table

Oct 28, 2007

Find value match in SQL table
Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?
 

View 2 Replies View Related

Need To Create Table(s) To Match This Xml

Dec 6, 2005

Hi,

I am not an expert with SQL2000 and need to create table(s) to match this xml format(if possible):

<?xml version="1.0" encoding="UTF-8" ?>
<MenuData xmlns="http://tempuri.org/MenuDataDataSet.xsd">
<Menu Id="RootMenu">
<Item1 Caption="About TTF FIS" Url="" Target="" Id="AboutItem">
<ChildMenu1 Id="AboutMenu">
<ChildItem Caption="FAQs" Url="Faqs.aspx" Target="_self" Id="FaqsItem" />
</ChildMenu1>
</Item1>
<Item2 Caption="Resource Solutions" Url="" Target="" Id="BizSolItem">
<ChildMenu2 Id="ResourceMenu">
<ChildItemMenu2 Caption="Resource Tracking" Url="" Target="" Id="AccItem">
<ChildMenuChild2 Id="AccMenu">
<ChildItemChild2 Caption="Resource_A" Url="Resource_A.aspx" Target="_self" Id="AItem" />
<ChildItemChild2 Caption="Resource_B" Url="Resource_B.aspx" Target="_self" Id="BItem" />
<ChildItemChild2 Caption="Resource_C" Url="Resource_C.aspx" Target="_self" Id="CItem" />
<ChildItemChild2 Caption="Resource_D" Url="Resource_D.aspx" Target="_self" Id="DItem" />
</ChildMenuChild2>
</ChildItemMenu2>
<ChildItemMenu2 Caption="Engineering" Url="Engineering.aspx" Target="_self" Id="EngItem" />
<ChildItemMenu2 Caption="Design" Url="Design.aspx" Target="_self" Id="MarItem" />
<ChildItemMenu2 Caption="Deployment" Url="Fire.aspx" Target="_self" Id="FireItem" />
</ChildMenu2>
</Item2>
<Item3 Caption="MES Solutions" Url="" Target="" Id="PerSolItem">
<ChildMenu3 Id="PerSolMenu">
<ChildItemMenu3 Caption="MES First Solution" Url="Education.aspx" Target="_self" Id="EduItem" />
<ChildItemMenu3 Caption="MES Second Solution" Url="Household.aspx" Target="_self" Id="HousItem" />
<ChildItemMenu3 Caption="MES Third Solution" Url="PersonalAccidents.aspx" Target="_self" Id="PerAcItem" />
<ChildItemMenu3 Caption="MES Fourth Solution" Url="Protection.aspx" Target="_self" Id="ProtItem" />
<ChildItemMenu3 Caption="MES Fifth Solution" Url="Retirement.aspx" Target="_self" Id="RetItem" />
<ChildItemMenu3 Caption="MES Sixth Solution" Url="Small Commercial Business.aspx" Target="_self"
Id="SmallBizItem" />
<ChildItemMenu3 Caption="MES Seventh Solution" Url="Term Life.aspx" Target="_self" Id="TermItem" />
<ChildItemMenu3 Caption="Others" Url="Yachts.aspx" Target="_self" Id="YacItem" />
</ChildMenu3>
</Item3>
<Item4 Caption="Our Company" Target="" Url="" Id="ComItem">
<ChildMenu4 Id="ComMenu">
<ChildItemMenu4 Caption="Identity" Url="Identity.aspx" Target="_self" Id="IdItem" />
<ChildItemMenu4 Caption="Promise" Url="Promise.aspx" Target="_self" Id="PromItem" />
</ChildMenu4>
</Item4>
</Menu>
</MenuData>

any help appriciated

View 2 Replies View Related

Uploading Column Definitions From The Flat File Connection Manager Editor Into A Table Definition

Apr 3, 2008

Hi,


Is there a way of uploading column definitions from the Flat File Connection Manager into a SQL Server table definition. Since I have over a dozen data sources to process each with around 200 columns and of course like many BI techies I have little immediate influence over the structure of these flat files. I just know that these data sources are business critical.

Judging by looking at similar threads I can't be the only one who would greatly benefit from being able to upload column definitions from the Flat File Connection Manager into a SQL Server table definition as opposed to doing this manually.

Thanks in advance,


Kieran.

View 1 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







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