I have a problem with a union all that seems to be changing the results of a column based on using the union, when I run the querys separatly I get the expected results in the field!
Basically 3 queries pulled together for reporting purposes.
Orders (which can include a gift so the gifts are separated in the second query).
OrderGifts (pulled out of the orders).
Gifts (straight gifts with no associated orders).
The problem is showing up in the first ORDERS query. I can pull a specific order and the calculation for the AMOUNT(eg:$15.20) is correct, but when I combine the tables I get a different AMOUNT(eg:$15.199999999999999).
Here are the values for the fields in the amount calc:
totitems=12.9500
totcredits=.0000
discount=.0000
tax=.0000
refund=.0000
convrate=1.0
postage=2.2500
giftamt=15.0000
I tried converting each of the fields to an decimal befre the calc and same results
I tried ROUND and same thing!
I have been trying to chase this down all day, cant figure out what the problem is or how to get around it...whats going on here I am missing?
Here is the query, if you need any other info just let me know, I would GREATLY appreciate ANY direction of figuring this out!
select * from
(
--orders
select
acctnbr as PartnerId,
'O' as TrnType,
ordnbr as TrnId,
0 as FundId,
odate as WorkDate,
adate as PostDate,
sdate as ShipDate,
cast(
(
IsNull(totitems, 0) -
IsNull(totcredits, 0) -
IsNull(discount, 0) -
IsNull(tax, 0) -
IsNull(refund, 0) +
IsNull(postage, 0)
) * IsNull(convrate, 1)
as Decimal(10,2))
as Amount,
batchnbr as BatchId,
motvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(0,motvcode) as ProjMotvName
from kmai.dbo.o01omst
UNION all
--orders gifts
select
acctnbr as PartnerId,
'G' as TrnType,
giftref as TrnId,
giftfundid as FundId,
odate as WorkDate,
null as PostDate,
null as ShipDate,
ROUND((giftamt * IsNull(convrate, 1)),2) as Amount,
batchnbr as BatchId,
giftmotvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(giftfundid,giftmotvcode) as ProjMotvName
from kmai.dbo.o01omst
where adate is null
and giftfundid <> ''
UNION ALL
--gifts
select
acctnbr as PartnerId,
'G' as TrnType,
gh.giftref as TrnId,
convert(int, fundid) as FundId,
gdate as WorkDate,
applydate as PostDate,
null as ShipDate,
ROUND((IsNull(gd.amt, 0) * IsNull(convrate, 1)),2) AS Amount,
batchnbr as BatchId,
motvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(fundid,motvcode) as ProjMotvName
from kmai.dbo.g03ghdr gh
inner join kmai..g04gdtl gd
on gh.giftref = gd.giftref
Code: SELECT DISTINCT LEFT([REPORTING_MONTH], 4)+'-'+SUBSTRING([REPORTING_MONTH],5,6) as REPORTING_MONTH, t.EMPLOYEE, '' as COUNT_FTP, CASE WHEN [MEDIUM_RCVD] = 'EMAIL' THEN COUNT(MEDIUM_RCVD) ELSE '' END AS COUNT_EMAIL FROM [GPO].[dbo].[DW_SUBMISSION] as s JOIN #TEMPActivity as t
[Code] ....
I'm trying to get the set to come out all on one line. REPORTING_MONTH, EMPLOYEE, COUNT_FTP, COUNT_EMAIL
But when I try null or '' it creates a second record and doesn't merge the two results.
This may be a dumb question, but I can't seem to get the syntax right. I have two temp tables that have the same columns, I want to do a union on them and store the results in a temp table. Any ideas?
Ie.
select * from #tmpTable1 union select * from #tmpTable2 into #tmpTable3
I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows. thanks for any suggestions...
~ select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nowo where nowo.wasrst <='40' union select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nopo where nopo.wasrst <='499'
Hi, I have follwing union query. I want to put this all in a temp table.
select Store_Id,batchnumber From Adjustments where updatedDt between '10/30/2007' and '11/20/2007' and Store_id in(8637 ,8641) group by Store_Id, batchnumber Union select DestinationId,b.batchNumber from batch b inner join Carton C on C.Carton_Id = b.General_ID inner join Document d on d.Document_Id = c.Document_Id where b.BatchType = 'Warehouse' and b.TranTable = 'Carton' and (d.DestinationId in (8637 ,8641) ) and c.UpdatedDt Between '10/30/2007' and '11/20/2007' Union select d.DestinationId,b.Batchnumber From batch b inner join Document d on d.Document_Id = b.General_Id where b.BatchType = 'TransferIn' and b.TranTable = 'Document' and (d.DestinationId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007' Union select d.SourceId,b.batchNumber From batch b inner join Document d on d.Document_Id = b.General_Id where b.BatchType = 'TransferOut' and b.TranTable = 'Document' and (d.SourceId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007' order by batchnumber
How can I UNION two MDX query results which are deriving from 2 cubes?MDX queries will return same ROW information and only Measure names will be different.
I ran this query against the pubs database and it runs successfully
ALTER TABLE publishers ALTER COLUMN state CHAR(25)
I change the table & field names for my db as follows: ALTER TABLE customquery ALTER COLUMN toclause CHAR(25)
and run against my database and I get the following error - Incorrect syntax near 'COLUMN'.
My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated. Thanks.
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table] UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....
I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I have a table that has integrity constraints in place and it is populated with the data. I need to rename one of the columns in the table. I am hesitant to use sp_rename procedure because when I used it before I get a warning message that says "changing any part of an object name could break scriptd and stored procedure." how big of a problem is it? Is there any other way to do it without hearting anything? Thanks.
ie if i havex 1 2 3 4 5------------1| a b c d e2| f g h i j3| k l m n o4| p q r s t5| u v w x yand i want to change it tox 1 2 3 4 5------------1| a b c d e2| a b c d e3| a b c d e4| a b c d e5| a b c d eany ideas on how to do that?
Does anybody know how I can change the default value for a column?I was trying to remove the default value in order to add the new oneafterwards. This is what I tried:alter table /table-name/ drop default for /column-name/alter table /table-name/ alter column /column-name/(/new-decl-without-default/)It did not work. I cannot find a solution in the documentation. Maybeyou can help me out?Thank you,Johan
Hi All,I am trying to change column name on an existing table. I am using SQLServer 7.As the table is quite big, it is taking quite long time to do it.By the way I could change the column name only through the EnterprizeManager.Is it possible to change the column name using SQL script?Why the change of column name will depend on the size of the table?Thanks for your answer.-Mokles
Hi. Is there a way to remove a gap between identity column values? Some records were deleted and now we have identity column values that jump all over the place.
I want to change a column's data type from bit to int. There are data in the table already. I'm wondering if it is save/correct way to issue the following command to change the data type for that column.ALTER TABLE database_tableALTER COLUMN my_bit_column INT; Thanks.
Is there any way or method to CHANGE the DATATYPE of a column in a published table being used for transactional replication (MSSQL 2000), WITHOUT DROPPING THE SUBSCRIPTION ????
Im stuck in this mess and do have the option to drop the subscription, alter the table, create the subscription and rerun the snapshot or to recreate it by Manual Synchronisation either.
Can anyone help? Has anyone been across this dilemma before and have troubleshooted the problem? If yes, help is much appreciated.
MY PROBLEM: ~~~~~~~~~~~~~ 'MyTable' is currently being published and has subscriptions to it. The PRIMARY KEY column 'id' has an Identity property as well. 'id' is of datatype smallint, however because of bad planning, i now need to change that datatype to an integer to support a larger range WITHOUT DROPPING SUBSCRIPTIONS. I CANT DROP THE COLUMN EITHER AS IT IS BEING THE PRIMARY KEY COLUMN.
IS THERE ANY OTHER WAY I CAN DO TO ARCHIEVE MY GOAL? THANKYOU.
Dear All,I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.trying to use:exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'I get:Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.There is no mistype, the table name and column name are correct.Can temp table's column names not be altered?If yes, how?Thanks in advance!
I need to change the size of a column of a table from char(255) to char(500). I used the line:
alter table table_name alter column column_name char(500)
When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.
What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?
I am trying to make the following update: All the columns are fine except for the 'name' column. datatype for 'name' column in the target table is varchar(30) and the datatype for 'name' in the sourcetable is varchar(40), I cannot change the datatype of the column 'name' to varchar(40) because I am told it may affect performance. what I want to do is just update the first 'name' column of the target table by the first 30 characters of the source table column 'name'
I am using the following query, is it possible to do it or are there any other ways I can update the column without changing the datatype?
MERGE INTO [S].[dbo].[AF_Copy] AS TargetTable USING (SELECT source_code, name, addr1, city, zip FROM [D].[D_TEST].[dbo].[SO_Copy]) AS SourceTable ON ([TargetTable].[Code] = [SourceTable].[source_code])
I was wondering how to modify a column's properties, like its datatype or length.
I know I'm supposed to do something like this: ALTER TABLE MyTable ALTER COLUMN MyColumn int(20)
My main concern is, what will the database do to all the data in the column, if the column used to be, say nvarchar(50)? Will I lose the data in the column if I change the datatype? And what if I had data in this column that was longer than 20 characters? Will the data now be truncated? What can I do to make sure that nothing happens to the data once I change the datatype?
Is it possible to change the format of the identity column? i.e. I have and integer value that will be the ID for the records to follow in the database. Instead of the values being 1,2,3,4,5,6,7,8,9 etc I want them still to be in sequence but I want the format to be 001,002,003,004,005,006,007,008,009 etc.
Is it possible to do that in SQL when you're creating the table?
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
