SQL Server 2014 :: Overriding Data Precedence In COALESCE Expression

Aug 5, 2015

I want to return a BIT from an outer join. I want the result type to be BIT. However, the usage of ISNULL is forbidden by code standards. The first expression returns an INT. Which of the bottom expressions do you think would work best in a query?

And why would ISNULL be absolutely forbidden? I was told that Microsoft deprecated it along with TINYINT, which I'm often prevented from using even when it's the appropriate data type.

DECLARE @x BIT;
DECLARE @default BIT = 1;
DECLARE @something sql_variant;

SELECT @something = COALESCE(@x, 1);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');

SELECT @something = COALESCE(@x, @default);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');

SELECT @something = COALESCE(@x, CAST(1 AS BIT));
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');

View 4 Replies


ADVERTISEMENT

SQL Server 2014 :: COALESCE To Replace Multiple CASE Statements

May 27, 2014

I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.

Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().

SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A

View 9 Replies View Related

SQL Server 2014 :: SSAS Stored Procs (CLR) - Identify Real Data Type Of MDX Value Returned From Expression

Feb 13, 2015

I have a SSAS stored procedure with a signature:

public Set DoSomthing(Set toBeProcessed, Set measuresToWorkWith)The set measurseToWorkWith is passed as {[Measures].[Measure1], [Measures].[Measure2] ...}

with the measures being real or query-scoped calculated members.

To get the value of the measure for each tuple in the set toBeProcessed, I create an Expression for each tuple (measure) in the set measuresToWorkWith then for each tuple in toBeProcessed call expression.Calculate(tuple) which returns a MDXValue.

My problem is that in order to make the code generic I need to get the real (.NET) data type of the MDXValue. The class only has explicit conversion methods ToInt16() etc which implies that the data type is known at design time.

However, if one of the measures is a query-scoped calculation then it could return a .NET double, int, bool or string.

If the measure is real then I can look up its metadata. However, it appears that if it is a formula (scoped member) then are all bets are off?

View 0 Replies View Related

Problem With Precedence Expression

Aug 21, 2007

Hello,
I have a SQL Task the executes the following statement;

SELECT COUNT(SettleDate) AS CountResult
FROM SettleDateCount

I have the SQL Task Editor configured for a Single Row result set. The Result Name is CountResult, and the variable it populates is RowCountRes. RowCountRes is Int32 with EvaluateAsExpression set to False.

I have a boolean variable called RowCountStatus. It evaluates as the following expression;

@[User::RowCountRes] > 1

From the SQL Task, I have two different constraints, both of which evaluate with an expression. One constraint evaluates this way; @[User::RowCountStatus] = True
The other evaluates this way; @[User::RowCountStatus] = False

The idea is that if the count comes back from the sql statement greater than 1 then execution continues down one path, else it goes down the other path. Currently, the result comes back = 1. I expect that the 'False' path will execute, but the 'True' path executes instead.

What am I doing wrong?

Thank you for your help!

cdun2

View 5 Replies View Related

Division In A Precedence Constraint Expression Does Not Work Properly - Huh?

Dec 13, 2006

Greetings,

I have an expression in a precedence constraint that is returning false when it should return true. This is the expression that returns false:

((5500 / 9990) * 100) > 10

The following expression returns true. I did the division (5500 / 9990) myself and substituted the resulting value:

((0.55055055055055055055055055055055) * 100) > 10

Why is the first expression returning false? I'm stuck in the mud up to my axles on this and I know I'll probably feel like a fool when I learn the answer...

Thanks,

BCB

View 3 Replies View Related

Using COALESCE And Retrieving Data

May 16, 2012

I'm preparing a report that will display provider number, provider name, and a single field that will show all the counties that specific provider serves. I realize from researching the coalesce posts that this can be done. However, when I try to retrieve data in the same select statement as my coalesce, I get the error: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."The listing of the counties must be specific to the provider, so my original code was:

DECLARE @Counties varchar(1000)
SET @Counties = ''
SELECT a.PROV_ID, a.PROV_NAME,
@Counties=coalesce(@Counties,'') + b.COUNTY + ','
from ECBH.dbo.tbl_PROVIDERS a inner join ECBH.dbo.tbl_Provider_Serv_Regions c
on a.PROV_ID = c.PROV_ID
inner join ECBH.dbo.tbl_Regions b
on b.REGION_ID = c.REGION_ID
where c.PROV_ID = a.PROV_ID
and a.MASTER = 1

I thought about creating a table to hold the coalesced values (need to coalesce two other fields as well), but wouldn't an insert to a table fail for the same reason?The counties table does not relate to the provider table, but does relate to a provider_county table (which in turn relates to the provider table).

View 1 Replies View Related

SQL 2012 :: Coalesce WHERE Clause With XML Data

Oct 9, 2014

Looking to coalesce a WHERE clause with XML data. I cannot supply test data.

If 472 is null, check the NEXT XML field at grab the 150, if both 472 AND 150 do NOT exist let it be NULL.

set @Date = (select top 1 XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)') from #src
where coalesce(XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472', '(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150')

View 1 Replies View Related

How To Enforce Precedence At Data Flow Level?

Aug 6, 2007

I wish to use the same data to update 2 different tables.

There is no green arrow output from the OLE DB data destination, so I can't have another component following on from the first insert.

This means I have to use the Multicast to 'copy' the data prior to the first table insert.

I can then use the data to perform inserts to both tables.

However, there is an FK constraint between these two tables, so I need to wait until the first table insert has finished before performing the second table insert.

How can I do this? How can I make the second insert dependent on the first?

View 9 Replies View Related

SQL Server Admin 2014 :: Change Data Capture(CDC) For Data Warehouse / Reporting?

Aug 12, 2015

I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.

The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?

Is there any performance impact if we enable CDC on OLTP db?

Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?

What is the best way to implement CDC to take incremental changes for reporting.

View 0 Replies View Related

SQL Server 2014 :: How To Insert CSV Data Into DB Where Some Data Don't Have Double Quotes

Aug 11, 2015

Example of data in CSV are as follows:

"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS

[Code] ....

View 5 Replies View Related

SQL Server 2014 :: Update Old Data With New Data

Jun 5, 2014

I got this script but all I get are the errors:

Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PartNrFabrikant'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'omschrijving'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'verkoopprijs'.
Msg 116, Level 16, State 1, Line 13

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

script:
UPDATE artikelen
SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,
verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,
gewijzigd = getDate()

[code]...

View 8 Replies View Related

SQL Server 2008 :: Aggregating Data From Common Table Expression

Apr 2, 2015

I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.

********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';

WITH CTE AS (
SELECT bkg_nbr

[Code] ....

The raw data from the CTE is:

Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1

The result of the query above was:

Category RecCount
Non-Recidivists3
Recidivists 1

The outcome I was looking for was:

Category RecCount
Non-Recidivists2
Recidivists 1

What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.

View 2 Replies View Related

Transfer SQL Server Object Task: Can We Cleanse Data By Using The Expression?

May 16, 2006

Hi all,

I have a task as creating a SSIS package to transfer all the 10 tables from a database to another. And I have used the Transfer SQL Objects Task to select a table list and let the component do the transfering.

But my problem is that the source database have bad data and some null data, so I have to find a way to transfer only 'NOT BAD' data, and remove the bad data (lost relationship) and change NULL to "N/A".

And I can't find a way to do this. Is there anyone have experiences with this problem?

Can anyone help me?

I will very appreciate you help?

View 1 Replies View Related

SQL Server 2012 :: Using COALESCE And Also Trying To Multiply And Divide

Sep 3, 2014

Need to multiply the following by 12 then divide this by 52.

,COALESCE(CTAX.[NET-AMT],0)+COALESCE(HEAT.[NET-AMT],0)+COALESCE(FURNIT.[NET-AMT],0)+COALESCE(SERVCH.[NET-AMT],0)+COALESCE(SEWER.[NET-AMT],0)+COALESCE(WATER.[NET-AMT],0) as 'weekly rent'Everywhere

I try and put the *12/52, I get errors.

View 9 Replies View Related

SQL Server 2014 :: How To Sum And Group Data

Jan 20, 2015

I want to group the following data attached by CUSTACCT, YEAR, PERIOD(month). As you can see in the attached example, I see duplicates for late fees and Exchange fees but none for FLIGHT_HRS. I want to see the data grouped by CUSTACCT by YEAR then by PERIOD for each of these: FLIGHTHRSSUM, LATEFEESUM, EXCHFEESUM without duplicates.

Is this possible?

This is my current SQL

SELECT dbo.VIEW_FLIGHT_HRS_TOT.YEAR AS FLTHRSYEAR, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD AS FLTHRSPERIOD, dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM,
dbo.VIEW_LATE_FEES_TOT.PERIOD AS LATEFEEPERIOD, dbo.VIEW_LATE_FEES_TOT.YEAR AS LATEFEEYEAR, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM,

[Code] ....

View 9 Replies View Related

Overriding Defaults

Sep 26, 2007

I have a stored procedure that has @BeginDate and @EndDate as parameters. I created a report with a default for both. They run just fine. After I deployed, I created Linked Reports and wanted to override the defaults. In the defaults, I tried to put in GetDate() for @BeginDate and GetDate()+10 for the @EndDate so this can be passed in the where statement of the stored procedure. I get 'Syntax error converting datetime from character string.'

What I assume is that if I override the default, the stored procedure will process what I put in the @BeginDate and @EndDate parameters.

The where statement looks like:

and (m.BeginDate >= @BeginDate) and (m.EndDate <= @EndDate)

I'm using Reporting Services 2005 and SQL Server 2005.

Thanks, Iris

View 1 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.

Below error occurred during process .

OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".

View 7 Replies View Related

SQL Server 2014 :: How To Copy Data Rows From Hosting To Local Server

Mar 11, 2015

I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:

We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.

Here is what I want to do:

I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.

View 2 Replies View Related

SQL Server 2014 :: How To Import Data To Server From Access Database Using Script

Sep 22, 2015

I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO

[code]...

The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.

Secondly I need to make 2 more changes to the code posted above.

1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.

2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')

View 7 Replies View Related

SQL Server 2014 :: Cursor To Delete Data?

Nov 5, 2013

i have to delete data from a table which is older than 2 weeks, how can i use a cursor to do it.

I will have to place the query in a SQL job and run that weekly once

View 9 Replies View Related

SQL Server 2014 :: Transform Data Into Column

Jan 8, 2014

I have data like this in the table :

IntRecpieID strName intMealtypeID Total
100 ‘A’ 1 20
101 'B' 2 30
100 'A' 3 40

Desired Output required:

IntRecpieID StrName 1 2 3

100 'A' 20 Null 40

101 'B' Null 30 Null

View 5 Replies View Related

SQL Server 2014 :: Insert Data From PDF Files

Mar 23, 2014

We are facing a problem with loading data from .pdf files from vendor..pdf files have data in tabular format and we would like to insert those fields into a SQL table.We do not want to insert the physical location of the file but, we need to insert the data within the file.How can we read a pdf file?

View 6 Replies View Related

SQL Server 2014 :: One To Many Relationship In Data Processing

Jun 24, 2014

I have a scenario where one partner can have multiple competency like Grade A,Grade B or Grade C and Grade AB,Grade BC etc.

I need to pull the records of competency of higher level also there is a chance of changing this competency level in feature like Grade A of Grade AA like this.

What would be the best option to select this kind of records.

View 9 Replies View Related

SQL Server 2014 :: Un-Group Data From Report

May 28, 2015

We have an application that can spit out our Facility Structure into the following format. However, we have a need to take that data and feed it into another system. However, as you can see it is organized in a PARENT / CHILD structure.

Indent Level Key:
1System
2Facility
3Service Line
4Division
5Department

If you notice, each additional row is the child row to its parent above it as long as the Indent Level Key continues to increment. Once we start going back up the structure we essentially have a new Division/Service Line/or Facility depending on how far back we jump on the Indent Level for that next row. Here is some sample data.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL
DROP TABLE #Test_Data

--===== Create the test table with
SET ANSI_NULLS ON

[Code] .....

View 3 Replies View Related

SQL Server 2014 :: Extracting Data From Database?

Nov 5, 2015

I am running an hotel application for all the bookings.

I would like to get all the data of all the guests that stayed in the hotel (name, stay duration and dates, payment, etc).However, the GUI would make me go over one booking at a time, which would take me forever.

The application has an SQL database locally on my computer.

Will I be able to use SQL server and extract all the guests data from the last year and export it into a file, e.g. A CSV file?

View 8 Replies View Related

SQL Server Admin 2014 :: Setup Performance Monitor Collector On Workstation Collecting Remote Server Data?

Mar 31, 2015

I set up the collector, and specify the Run As as my AD account in the Collector Set - Properties - General screen. My AD account is the local admin of the remote server.

However, the collector does not seem to work. Although the collecting set is shown as running, the The blg file stays at 64K. If I open it, there is nothing inside (no counter at the bottom). What did I miss?

View 1 Replies View Related

SQL Server 2014 :: Data Profiling Tables Within Server

Jun 18, 2015

I have been giving the task of profiling databases and all the tables within it. Right now I'm collecting the following:

Null_Count
Null_Percentage
Total_Record_Count

I'm looking to capture Mean_value, Min_value, and Max_value and the unique_count.

View 2 Replies View Related

SQL Server Admin 2014 :: Cannot Export All Data From Database

Dec 4, 2013

And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql

View 4 Replies View Related

SQL Server 2014 :: Enquiry On Retrieving Data From Database

Jul 18, 2014

I have a drop down menu for transmission. Inside the drop down menu, there are 1.Any 2.Auto 3.Manual options

Now I want to retrieve from my database, if the client select '1.Any' option. Meaning the output I want it to display all the products which have both auto and manual for transmission.

I have no problem retrieving all the products which either only have auto option or manual option.

View 2 Replies View Related

SQL Server 2014 :: Bulk Insert Data Into Table

Jul 29, 2014

I need to load the following data into a SQL table. This is how the vendor is able to provide it to us.

CRCorp Daily Report,,,,,,
,,,,,,
Facility,Location,Purchase Order #,Vendor,Inventory #,Date Ordered,Extended Cost
09-Mowtown 495 CRST,09-402A Women's Imaging,327937,"BARD PERIPHERAL VASCULAR, INC.",113989,7/25/2014,650
09-Mowtown 495 CRST,09-402A Women's Imaging,327936,"WB MASON CO., INC.",112664,7/25/2014,8.64
01-Mowtown 499 CRST,01-302B Oncology,327947,McKesson General Medical,n/a,7/25/2014,129.02

[Code] ....

I have attempted to bulk insert it into this table with no luck.

CREATE TABLE POMaster
(Facility VARCHAR(75),
Location VARCHAR(75),
PONum INT,
VendorNm INT,
INVENTORYNUM VARCHAR(25),
orderDte DATE,
ExtendedPrice NUMERIC(10,2)
)
GO

It does not like the double quotes. How to make this format work? Do I need a format file?

View 2 Replies View Related

SQL Server 2014 :: Combine Data From Historical Table?

Aug 13, 2014

Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.

View 9 Replies View Related

SQL Server 2014 :: Using Data From Two Tables In A Single Script?

Jan 20, 2015

Basically I've got two tables with a common column (in this case it's UserID)

The structure of the table Users is basically:

UserID FirstName LastName
1 Jim Smith
2 Nancy Orr
3 Ken Stephenson

And the structure of the classes table is

UserID ClassID
1 AAA
1 BBB
2 AAA
3 BBB

What I'd like to do is select the FirstName and LastName the users table who have a UserID in both Classes and Users.

The tricky part of it is that I only want to return the FirstName and LastName of userID who have multiple ClassID in the classes table. (hopefully I'm making sense when I explain this)

In this case I'd like it to return just Jim Smith's name because he's the only userID who has two ClassID entries.

So far I've come up with

select UserId, count(*) as MultipleClasses
from classes
group by UserID
having count(*) > 1;

which outputs the UserID and a colum called MultipleClasses with how many classes each ID returned has.

how would I do something like

Select firtname, lastname
from users
where Userid=

The output from that initial bit finding users with multiple classes.

View 3 Replies View Related

SQL Server 2014 :: Encryption And Data Length Limitations?

Feb 2, 2015

I'm having an issue in encrypting large documents. I know that previous editions ENCRYPTBYKEY had a maximum size of 8,000 byte limitation. Does SQL Server 2014 have any new features that overcomes this limitation?

Using
SQL Server 2014 Std
Symmetric key with AES_256

View 2 Replies View Related







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