Alter Multiple Columns In SQL

Mar 20, 2008

This does not work, incorrect syntax... Please help: ALTER TABLE dbo.classifieds_HotelAds ALTER COLUMN (

HasValetParking varchar(1) NULL,

HasContinentalBreakfast varchar(1) NULL,

HasInRoomMovies varchar(1) NULL,

HasSauna varchar(1) NULL,

HasWhirlpool varchar(1) NULL,

HasVoiceMail varchar(1) NULL,

Has24HourSecurity varchar(1) NULL,

HasParkingGarage varchar(1) NULL,

HasElectronicRoomKeys varchar(1) NULL,

HasCoffeeTeaMaker varchar(1) NULL,

HasSafe varchar(1) NULL,

HasVideoCheckOut varchar(1) NULL,

HasRestrictedAccess varchar(1) NULL,

HasInteriorRoomEntrance varchar(1) NULL,

HasExteriorRoomEntrance varchar(1) NULL,

HasCombination varchar(1) NULL,

HasFitnessFacility varchar(1) NULL,

HasGameRoom varchar(1) NULL,

HasTennisCourt varchar(1) NULL,

HasGolfCourse varchar(1) NULL,

HasInHouseDining varchar(1) NULL,

HasInHouseBar varchar(1) NULL,

HasHandicapAccessible varchar(1) NULL,

HasChildrenAllowed varchar(1) NULL,

HasPetsAllowed varchar(1) NULL,

HasTVInRoom varchar(1) NULL,

HasDataPorts varchar(1) NULL,

HasMeetingRooms varchar(1) NULL,

HasBusinessCenter varchar(1) NULL,

HasDryCleaning varchar(1) NULL,

HasIndoorPool varchar(1) NULL,

HasOutdoorPool varchar(1) NULL,

HasNonSmokingRooms varchar(1) NULL,

HasAirportTransportation varchar(1) NULL,

HasAirconditioning varchar(1) NULL,

HasClothingAiron varchar(1) NULL,

HasWakeupService varchar(1) NULL,

HasMiniBarInRoom varchar(1) NULL,

HasRoomService varchar(1) NULL,

HasHairDryer varchar(1) NULL,

HasCarRentDesk varchar(1) NULL,

HasFamilyRooms varchar(1) NULL,

HasKitchen varchar(1) NULL,

HasMap varchar(1) NULL,

HasWiFi varchar(1) NULL,

GDSChainCode nvarvarchar(1) NULL,

GDSChainCodeName nvarchar(50) NULL,

DestinationID nvarchar(50) NULL,

DrivingDirections nvarchar(MAX) NULL,

NearbyAttractions nvarchar(MAX) NULL,

IANHotelID int NULL,HasClothingIron bit NULL

)

GO

COMMIT

View 8 Replies


ADVERTISEMENT

How To Alter Multiple Columns At Time

Dec 4, 2006

Hi all

ALTER TABLE <Table_Name> ALTER COLUMN <column_name> <Data_type>

above query alters the sigle column but how to alter multiple columns at time

saivyshnav
Junior DBA

View 7 Replies View Related

Alter Table Multiple Columns?

Jan 3, 2007

How do I alter multiple columns with one SQL statement?

I've tried :

ALTER TABLE epcs_benefit_plan ALTER COLUMN

abc1 varchar(3) not null,

abc2 varchar(3) not null

View 3 Replies View Related

Alter Columns?

Apr 1, 2004

Hello,

I am trying to edit several tables that were imported in tab-delimited format from text files. I am trying to generate a script that will alter the data type for several different columns.

I have succesfully edited a single column with the following code:
USE THCIC
ALTER TABLE PudfTest
ALTER COLUMN
DISCHARGE VARCHAR(6) NULL

However, I have need to create a script that will change the data type for over 100 columns. So far, everything I've read tells me that multiple 'alter column' statements cannot be run in a single query. I'm hoping someone can shed some light on this, or at least point me in another direction so that I won't have to manually change the data type for every column in each of the tables.

Any help would be greatly appreciated.
Thanks!

View 1 Replies View Related

Using Alter Table To Drop Columns

Nov 5, 2000

I am dropping 60 out of 133 columns on a user table, but am finding that the spaceused by the table ( as shown by sysindexes.dpages ) is not being freed up.
I have dropped and recreated all indexes on the table, with no effect on the dpages value, as well as running dbcc updateusage for the table.
Is it not possible to free up the space used by using this method ?
Is recreating and repopulating the table with only the required columns the correct / only way to do this ?

thanks,
manoj

View 1 Replies View Related

Alter Table Columns Automatically

Feb 19, 2015

I need to perform this task. say I have a table that the columns are dates.

Table months:
[02-2015],[03-2015], [04-2015],[04-2015]....[02-2016].

When the new month comes I want the table to alter the columns automatically instead of manual how we do it now. The new Month table should delete [02-2015] and replace it with [03-2015].

table months:
[03-2015],[04-2015],[05-2015],[06-2015]....[03-2016].

View 3 Replies View Related

DB Design :: Alter View To Add Columns

Jun 9, 2015

I would like to alter view to add columns. My base table is populated by application and the base table name is always change. Is there any way to add some extra columns to this view.

One way is to create a sp to add columns and use the view inside the sp. But I would like to know is there any way to alter the view. 

View 4 Replies View Related

SQL 2012 :: How To Drop Columns From Alter View

Mar 20, 2015

I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.

Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .

The view is called TEMP_EXPORT_1

I can either use the code below to return a list of columns that I want removed:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'

Or I can use the code below to return the list of columns that I want to keep:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'

Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...

Therefore I tried the following but I'm not sure of the syntax:

ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1

Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.

View 2 Replies View Related

Help With ALTER TABLE SWITCH (IDENTITY COLUMNS)

Feb 13, 2008

I am having problem when performing the alter table switch. Both tables are identical and have pk.

ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresults' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part' .



CONSTRAINT [PKIDX_testSummary] PRIMARY KEY CLUSTERED

( [testresult_id] ASC )

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON TResultsScheme (testresult_id) ) ON TResultsScheme (testresult_id)


I have performed the function on other tables successfully, but this is the first that has an identity column involved. Is there anything special that needs to be done?



[testresult_id] [int] IDENTITY(1,1) NOT NULL,

View 11 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

Aug 27, 2014

I'd like to first figure out the count of how many rows are not the Current Edition have the following:

Second I'd like to be able to select the primary key of all the rows involved

Third I'd like to select all the primary keys of just the rows not in the current edition

Not really sure how to describe this without making a dataset

CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,

[Code] .....

Group by fails me because I only want the groups where the Edition_fk don't match...

View 4 Replies View Related

Alter Multiple Data Type

Apr 20, 2006

how do you alter multiple data type in a table?

in oracle we have "modify"
is there something similar in sql server?

View 7 Replies View Related

To Alter Multiple Column With Single Statement

May 5, 2008

It is possible to alter multiple columns within a single alter table statement?
I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement.
http://www.blogcoward.com/archive/2005/05/09/234.aspx[^]
Does anyone know about that?


Thanks,
Mushq

View 4 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID  key1  key2  key3  key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

T-SQL (SS2K8) :: Selecting Data From Table With Multiple Conditions On Multiple Columns

Apr 15, 2014

I am facing a problem in writing the stored procedure for multiple search criteria.

I am trying to write the query in the Procedure as follows

Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3

I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.

View 4 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008



Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

View 8 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

Aug 5, 2014

I concatenate multiple rows from one table in multiple columns like this:

--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED

[Code] ....

This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?

View 1 Replies View Related

Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows

Apr 23, 2008



Hello All,

I am rather new to reporting on SQL Server 2005 so please be patient with me.

I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.

For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report

The table (System Info) looks like:-

ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |

Which


So basically I need it to look like this.

Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy

So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1

How would I go about displaying the other system parts as columns with info

Any help is much appreciated!


View 3 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Calculated Columns Based On Multiple Columns?

Aug 12, 2014

MS SQL 2008 R2

I have the following effectively random numbers in a table:

n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i

I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.

if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1

So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.

declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers

View 5 Replies View Related

Search In Fulltextindexes For Multiple Searchterms In Multiple Columns

Mar 23, 2007

I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.

For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.

I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms

I managed to find a workaround:

SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.

Does anyone know a better solution?

Thanks in advance Bart Rouw

View 2 Replies View Related

SQL Server 2012 :: Multiple Rows Into Multiple Columns?

Mar 2, 2015

I have the following results:

ID, Office1
1, Testing
1, Hello World

What i am trying to do is to get this result:

ID, Office1, Office2
1, Testing, Hello World

how i can accomplish this task.

View 3 Replies View Related

Parent Table - (multiple) Rows Into (multiple) Columns

Feb 12, 2015

I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:

I need results for each parent like this

ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]

View 1 Replies View Related

Processing Multiple Rows And Multiple Columns In SSIS

Sep 26, 2007

I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!

View 1 Replies View Related

ERROR - The Row Value(s) Updated Or Deleted Either Do Not Make The Row Unique Or They Alter Multiple Rows.

Sep 8, 2006

i am getting the above error on my database i have 2 rows with the same info on and another 2 with the same info on.  example:

ID    username   password

1            bob       bob

1            bob       bob

1           john       john

1           john        john

 

I know this is a fault with ms sql 2005 however how do i fix it?

Ive found this link which explains everything but how do i start a query.  I tried clicking on new query and copying the code.  What is table1 meant to be?  the database is dbl.tbl_admin.  It wont find my database.

Im not sure how to do it anyway.

I need to change it though as its my admin password and Ive given it out to web design companys

http://geekswithblogs.net/allensb/archive/2006/07/27/86484.aspx

Can some 1 read the above page and give me full instructions, I dont know what im doing thanks

info@uktattoostudios.co.uk

View 7 Replies View Related

Deleting Multiple Columns From Multiple Objects

Apr 2, 2008

Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.

View 2 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

Multiple Rows To One Row And Multiple Columns

Sep 7, 2007



Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:


This is the how the table is:
EmpID Designation




678
CFA

679
CFA

680
CFA

685
CFP

685
CIMA

685
IMCA


I want it to display as:
EmpID Designation1 Designation2 Designation3
678 CFA
679 CFA
680 CFA
685 CFP CIMA IMCA

could anyone provide some help on this?

Thanks

View 1 Replies View Related

SQL 2012 :: Calculated Columns Conditional On Calculated Columns Multiple Tables

Apr 20, 2014

I have 4 tables involved here. The priority table is TABLE1:

NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500

TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE

TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15

[code]...

Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.

View 0 Replies View Related

Alter Table Alter Column In MSACCESS. How Can I Do It For A Decimal Field?

Jul 23, 2005

Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

SQL 'Having' On Multiple Columns?

Mar 12, 2008

Can I write a having statement for multiple columns? Here's my situation: I want to select duplicates from a table based off of 3 fields:Normally one would use HAVING COUNT(*) > 1however, I need to INSERT INTO my table based on duplicates of the 3 fields but also insert the key from the first table, ie:insert into #TempTable (key, field1, field2, field3)select key, field1, field2, field3 from Table1 order by field1, field2, field3 Having COUNT(field1)>1 and COUNT(field2)>1 and COUNT(field3)>1 My question is this: Will this having statement compare all three fields of this row to all three fields of the other rows, or does it do each column independantly?for example:row 1: a   b   g                  row 2: a   c   k                  row 3: j    c   k These rows aren't the same, even though there are 2 a's, I want it to look at the entire row, and not return all three of these. 

View 1 Replies View Related

Multiple Columns From WHERE?

Aug 10, 2004

Hey all,
I was curious if this was possible...
I basically have 2 queries I'd like to combine into one. The only difference in the queries is one clause in the WHERE statement

so here is an idea of what I'm talking about

SELECT COUNT(*) as HighStock FROM products WHERE qty > 100

now lets say I needed to do one for low I would have to run that query 2 times with different alias's and change the qty...

is there a way to get all that in one result set? Something like

SELECT COUNT(*) as HighStock, COUNT(*) as LowStock FROM products WHERE qty > 100 AND LowStock = qty < 20

so then my result would be
HighStock LowStock
50 10

anyone have any clues on that? thanks! :)

View 11 Replies View Related







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