Transact SQL :: How To Select Number For Varchar Column

Sep 15, 2015

I have a table with number and varchar columns. The last insert statement has 1 inserted.

The  select statement should retrieve    

a          b                                                          
1         1

CREATE TABLE [dbo].[test1](
 [a] [int] NULL,
 [b] [varchar](10) NULL
) ON [PRIMARY]
  insert into test1 values (1,'a')
  insert into test1 values (2,'b')
  insert into test1 values (4,'d')
  insert into test1 values (12,'x')
  insert into test1 values (15,NULL)
  insert into test1 values (1,1)

View 5 Replies


ADVERTISEMENT

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

Transact SQL :: Select From A Select Using Row Number With Left Join

Aug 20, 2015

The select command below will output one patient’s information in 1 row:

Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2

[code]...

This works great if there is at least one OP coverage.   There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.   The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.  

select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,

code]...

View 6 Replies View Related

Transact SQL :: Convert Varchar (max) Column Data

May 20, 2015

I have a column in table which stores the actual signature from the singature pad. In the table it is stored as varchar(max) datatype and sample data looks like below. Below data is the actual signature of the person.

CREATE
TABLE #test
([Signature]
VARCHAR(MAX))

[code]...

How do i decrypt this data so i can print actual signature on my report.

View 2 Replies View Related

Transact SQL :: How To Add Column Having Varchar Data In Form Hh:mm:ss

May 25, 2015

There is a column named Timings in HH:MM:SS format. Datatype of this column is varchar(50).

I want to sum the rows in this column and get the output as one single record.

00:01:06
00:01:16
00:01:04
00:01:24
00:01:13
00:01:06
00:02:21
00:01:16

View 4 Replies View Related

Transact SQL :: How To Number Rows In SELECT

Oct 8, 2015

I have a SELECT statement with multiple JOINs. I want to number the rows returned by this SELECT statement. What's the easiest way to do this?

My current SELECT statement returns this:

ProjectId -- TaskId -- TaskName
123  - 111 -- Do something
123  - 222 -- Do something else
123  - 333 -- Do one more thing

I want to return this:

ProjectId -- TaskId -- TaskName -- Sequence
123  - 111 -- Do something  -- 1
123  - 222 -- Do something else  -- 2
123  - 333 -- Do one more thing  -- 3

View 2 Replies View Related

Transact SQL :: Update Column With Row Number

Jul 23, 2015

I have a Users Table. It is full of users already and I would like to start using the UserPIN in the software (this is an nvarchar column).

I would like to update the UserPIN column with the row_number. All of my efforts have resulted in setting the UserPIN to 1 for every record.  I just want an update query that fill the UserPIN column in sequential order.

View 6 Replies View Related

Using Column Number Inplace Of Column Name In SQL Select Statement

Jul 20, 2005

Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep

View 1 Replies View Related

SQL 2012 :: Select And Format A Text In A Varchar Column

May 9, 2014

One purchased app stored user's multiple lines input into a varchar column in which including char(13) and char(10).

My app need to select this value and format to multiple lines text in one text box.

How to code to output it?

View 5 Replies View Related

Transact SQL :: Select All Days In Week Number X Including Last Year If Necessary

May 24, 2015

SQL express 2012. I am trying to case in the where part and having a syntax errors - This is what i am trying to do:

select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)

declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime

[Code] ....

View 2 Replies View Related

Transact SQL :: Select All The Days In Week Number X Including Last Year If Necessary

May 24, 2015

SQL express 2012

I am trying to case in the where part and having a syntax errors - this is what i am trying to do:

Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)

declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime

[Code] ...

View 2 Replies View Related

Transact SQL :: Update Column Based On Row Number For Previous Row

Jul 25, 2015

Below is the resultset I got using the following SQL statement

SELECT   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_date DESC) AS RowNum
,ID
,create_date
,NULL AS end_date
FROM dbo.Table_1

Resultset:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL

[Code] ....

Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18

[Code] ....

View 4 Replies View Related

Transact SQL :: Show N Number Of Column As Per No Of Days In Month

Aug 26, 2015

I want to show this kind of output

UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31

user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.

View 10 Replies View Related

Transact SQL :: Update Table With Max Value And Row Number (based On 2 Column Partitions)

Sep 15, 2015

I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

job_cd permit_nbr reference_nbr

ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008

View 3 Replies View Related

Can You Select A Column By Number Rather Than Name?

Jun 23, 2004

I swear I have done this before and can't remember how.

I want to select columns in a SELECT statement by their number instead of name.

I have looked everywhere for the syntax.

Also, is there a function to 'count' the number of columns in a table?

Thanks.

View 2 Replies View Related

How To SELECT By Column Number ?

Dec 7, 2004

I want to select with column order without field name .
Is it possible ?

SQL2000 db
...

I want to select a field by its order in table, but without naming it to have a constant name of it, like :

select (column01) as L1 from myTable

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

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

Transact SQL :: Select Row With Max (column Value) Group By Another Column

May 19, 2015

i dont't know how to select row with max column value group by another column. I have T-SQL

CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (

[code]....

View 3 Replies View Related

Number De Caracters In Select Column

Apr 4, 2001

Hello, I have a field of type the Var, the stored data makes about 700 characters.
During a select through the query analyser, it tronque in about 200 or 300 characters.
How to explain it?
thank in advance
Pascal

View 2 Replies View Related

Transact SQL :: Using Bulk Insert - Invalid Column Number In Format File Error

Jun 5, 2015

I try to import data with bulk insert. Here is my table:

CREATE TABLE [data].[example](
 col1 [varchar](10) NOT NULL,
 col2 [datetime] NOT NULL,
 col3 [date] NOT NULL,
 col4 [varchar](6) NOT NULL,
 col5 [varchar](3) NOT NULL,

[Code] ....

My format file:

10.0
7
1  SQLCHAR 0  10 "@|@" 2 Col2 ""
1  SQLCHAR 0  10 "@|@" 3 Col3 ""
2  SQLCHAR 0  6 "@|@" 4 Col4 Latin1_General_CI_AS

[Code] .....

The first column should store double (in col2 and col3) in my table

My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS

My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)

Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".

I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter

Removed this line from format file
1  SQLCHAR 0  10 "@|@" 2 Col2 ""
Nothing works ....

View 7 Replies View Related

Transact SQL :: Select Column On Condition

Jul 4, 2015

I have a table called names  (firstname, lastname, number) then i have a DECLARED variable called  @displaynum...If @displaynum id true   my select query should select the number column.  if the variable is false , only the first two columns are selected.

e.g   @displaynum = true                                          
@displaynum = false
Firstname  | Lastname | number                               Firstname  | Lastnam

blah           |   blah        |   12345                             
blah           |  blah

i am not sure if this is possible as a case still selects the column but just changes the values.

View 12 Replies View Related

Add Column With Fixed Number Of Values (text) To The Select Statement

Aug 1, 2007

Hello,

I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.

View 6 Replies View Related

Transact SQL :: Select Column By Ordinal Position

Apr 16, 2012

Is there any way to select columns by ordinal position rather than by name?

Something like
Select t.[1] as col1, t.[2] as col2
FROM table t

The question comes because of a very specific situation where I've got 2 databases that have exactly the same schema at a column/datatype level but the column names are different.   There are a few other ways to skin the cat but I'm interested if it can be done this way - without a join to syscolumns.

View 12 Replies View Related

Transact SQL :: Custom Column In Select Statement

Aug 12, 2015

I want to add a custom column in a select statement that has a value to true or false based on other criteria.

SELECT [ID], [Name], [Description], [EmpID], [Employed] FROM [Employees]

Now, in the above example there is no [Employed] Column in my table but I want it to show true or false based on whether or not [EmpID] equals a certain value.

View 6 Replies View Related

Transact SQL :: SELECT Unique Values In Single Column?

Jun 8, 2015

I have the following two tables...

tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress

I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.

View 4 Replies View Related

Transact SQL :: How To Select Data With Specific Priority Of A Column

Aug 1, 2015

i have table that has rows as below

DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)

[code]...

i want to select the data priority wise. the o/p should look like 

e.g first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22' 
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'

View 6 Replies View Related

Transact SQL :: Select Multiple Columns From Table But Group By One Column

Jun 17, 2015

I have a SQL query like this

select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode

As per this query I got the result like this

CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED     BNT    1     1
AED     BNT     12     1
AED     SCN     1     1
AED     SNT     1     3

[Code] ....

But I wish to grt result as

CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED     BNT   13     2
AED     SCN     1     1
AED     SNT     11     7
AFN     BPC    8     6

[Code] ....

I also tried this

select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode

But of course this codes gives an error, but how can I get my desired result??

View 5 Replies View Related

Transact SQL :: Returning Multiple Values From Column In Select Statement?

Nov 26, 2015

I am writing a query and have the bulk of it already written. 

I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.

I'd like for my query to return all lines that have the value R, F, H.

My where clause is written like this 

WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')

I know I'm missing something....

View 3 Replies View Related

Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS

Nov 20, 2007



I have looked far and wide and have not found anything that works to allow me to resolve this issue.

I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.

I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".

[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".

[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?

View 10 Replies View Related

Transact SQL :: Generate Insert With Column List And Select With Columnlist Statements

Oct 22, 2015

I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?

View 7 Replies View Related

Convert Varchar To Number

Apr 25, 2001

Hi

In my MSSSQL database I have a table that contains "AGE" and its datatype is varchar.

I want to calculate the min(age) and max(age).

But the AGE field's datatype is varchar and I canot do any mathematical functions.



In Oracle There is a code for doing that.I think this is the code


select max(tonumber(age));


what is it's MSSQL equivalent.


thanks

sabu

View 2 Replies View Related

Convert Varchar To Number

Dec 4, 2007

Using SQL 2005. I have a field with dollar amounts but field type is VarChar. Need to convert to number to sum. What's the best way to do this or how can I sum a field type Varchar that has dollar amounts. Thank you, David

View 2 Replies View Related







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