DTS Query Builder - Using Nulls
Jan 20, 2004
I am trying to emulate the "Find unmatched Records" Access Query using DTS. I have a small table with a list of codes that I need to use to exclude records in my text export.
I have created a left outer join and set the criteria for the joined field in the 'right' table to be null. I am repeatedly getting no records as a result.
Wouldn't the joined field in the right table for unmatched records by default be null? I'm stumped as to why this seamingly simple process won't work for me. I have attached the select statement for your review.
Any help would be greatly appreciated.
View 2 Replies
ADVERTISEMENT
Mar 16, 2007
Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together:
Col A Col B Col C
Row1 1 1 2
Row2 2 3 5
Here is the query
declare @t table( player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test int)
INSERT INTO @t (player_name, TopUp)
SELECT Player_name, SUM([Top-ups]) AS TOPUPS
FROM (SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,
Events.Top_up * Event_data.Transaction_value AS [Top-ups]
FROM Event_data INNER JOIN
Events ON Event_data.Event_id = Events.id INNER JOIN
Players ON Event_data.Player_id = Players.Player_id
WHERE (Event_data.Transaction_type = 2)) AS Topups
GROUP BY player_name
INSERT INTO @t (player_name, ReBuy )
SELECT Player_name, SUM([Re-buys]) AS REBUYS
FROM (SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value * Events.Rebuys AS [Re-buys]
FROM Event_data INNER JOIN
Events ON Event_data.Event_id = Events.id INNER JOIN
Players ON Event_data.Player_id = Players.Player_id
WHERE (Event_data.Transaction_type = 3)) AS REBUYS
GROUP BY Player_name
Insert into @t (player_name, BuyIn)
SELECT dbo.Players.Player_name, SUM(dbo.Events.Buy_in) AS BuyIn
FROM dbo.Players INNER JOIN
dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type
HAVING (dbo.Event_data.Transaction_type = 1)
ORDER BY SUM(dbo.Events.Buy_in) DESC
Insert into @t (player_name, Winnings)
SELECT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings
FROM dbo.Players INNER JOIN
dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type
HAVING (dbo.Event_data.Transaction_type = 1)
insert into @t (player_name, Events)
SELECT dbo.Players.Player_name, COUNT(dbo.Event_data.Place) AS Expr1
FROM dbo.Players INNER JOIN
dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name
HAVING (NOT (COUNT(dbo.Event_data.Place) IS NULL))
insert into @t (player_name, test)
select player_name, ((TopUp) + (Rebuy)) as Test
from @t
SELECT player_name, min (BuyIn) as BuyIn, min(TopUp) as TopUps, min(ReBuy)as ReBuy, min(Winnings) as Winnings, min(Events) as Events, min(test) as test
FROM @t
GROUP BY player_name
ORDER BY BuyIn DESC
--ORDER BY TOPUPS DESC
END
THis is where I attempt to add the coloms but I get a null result
insert into @t (player_name, test)
select player_name, ((TopUp) + (Rebuy)) as Test
from @t
any help would be great.
View 5 Replies
View Related
Dec 1, 2014
I have a table called "alert" and in this table, for each columns there are data as 1 , or 0 or NULL. how do I write a query to just give me data's with "1" in them not the NULLS or 0?
View 3 Replies
View Related
Mar 1, 2008
This is my problem, i have 2 tables: one table that hold data, having id as identity, say table (id, content), the other table have 2 columns: (id, number). The second table number column refer to the id in the first table. i want to build a query that get the data from the first table that correspond to a specific id in the second table, not only this i want to get the previous, the current and the next item.
For example: if table_data, table_info is first and second tables, something that can do it is :
DECLARE @i int
SELECT * FROM table_data WHERE id in (@i -1, @i +1, @i)
The problem here if @i, @i+1 or @i-1 doesn't exist the column will not be returned, i want to get a result similar to
id, content
25 null
26 null
27 #some content#
or
34 #content1#
35 #content2#
36 #content3#
so my problem is that nulls doesn't appear, i thought about using OUTER JOIN, but the problem is that outer join take tables, not (@i -1, @i +1, @i), so if only i can make somehow the outer join use these values, i think it works.
Any help please, and thanks in advance
View 6 Replies
View Related
Sep 21, 2007
Is there a way to set SQL Server 2005 Express so that I can return null values? For example, the following query will not return any values:
SELECT *
FROM tbl_form_values
where fldVALUE IS NULL;
it does return values with:
SELECT *
FROM tbl_form_values
where fldVALUE = '';
I have an Oracle background and all null values are true nulls not empty strings. I would like to be able to use the first query. Also other functions such as COALESCE work very nice with nulls. I can do the following in Oracle but not in SQL Server:
SELECT fldID, fldMID, fldFID,
COALESCE(fldVALUE, 'n/a');
FROM tbl_form_values
This will return the values in fldVALUE if they are available and n/a for all NULL values.
Any help would be appreciated.
Thanks,
E
View 1 Replies
View Related
May 16, 2006
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This sux
View 4 Replies
View Related
Apr 30, 2008
Hello ALl, can anyone please tell me how to search for NULLS.
like i know one method....
select * from table
where col = 'NULL'
View 1 Replies
View Related
Feb 17, 2008
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
View 4 Replies
View Related
May 15, 2008
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
View 2 Replies
View Related
Apr 13, 2007
Good day,
I would like to know if there are any good MDX query builder tools out there. The one that is shipped with AS does not suffice in that it does not support more than 2 axes. Also if there are any are there any that make life a little easier in that they are drag and drop and all but create the statement for you?
I need to create a matrix in SSRS using cubes and in doing so need to write MDX queries, not my strong point. Are there any tools that can help me "auto create" the statement with drag and drop functionality?
Thanks in advance
View 1 Replies
View Related
Jul 23, 2005
Hi people!I'm looking for an SQL query builder application that helps me constructSELECT queries with full respect of underlaying relational model. It wouldbe nice if the app is database independant because my work covers MSSQL,mySQL and Oracle DB.OpenSource and/or comercial suggestions are welcome!*** thanx ***
View 1 Replies
View Related
Sep 12, 2006
HI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.
When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.
Am I missing something?
Thank you for your help,
Cctoe
View 1 Replies
View Related
Nov 2, 2006
hi,i can do"select * from products where name = @name" kind of statements in query builder but"select * from products where name LIKE @name" dosen't work!" any ideas? i'm using sql server express. thanks
View 3 Replies
View Related
Apr 29, 2007
Hi All,
For one of our projects, we are supposed to write a query builder to display reports in tabular format. For the filter criteria ( i.e where clause ) user can select from a list of predefined columns and assign conditions to it. For example, user can select 'CustomerName' as the column and specify condition where name begines with 'A'. Similarly he can also select ouput columns
Problem
The columns that user selects can come from different tables. We are planning to store mapping between tables and columns and also their primary and foreign keys in an xml file. But I would want to understand how to build the join clause.
For example, say there are 4 tables customer, customer address, order, and items. Say user wants the number of items purchased by each customer who stays in 'Delhi'.
1. In output column he selects customer.name and count(items.id)
2. In filter criteria he selects address.city = 'delhi'
How would the query builder know that to join customer and items, order also has to be joined. Any pointers would be helpful.
View 2 Replies
View Related
Jun 10, 2002
Hello everyone,
I hope you mind a questions from an inexperienced SQL 2000 user.
I don't have much experience with building queries for SQL2000. So I am lookinf for a software tool that will help me create simple queries. I'm also looking for an online (either on the web or a downloadable program) query reference to help remine me how to make these queries.
Thank you,
mike
View 2 Replies
View Related
Feb 15, 2007
Can we view the query that is fired on the Report builder to debug as i am finding some error in data...
View 3 Replies
View Related
Jun 19, 2006
On pages 123, 124 of Data Mining with SQL Server 2005, I can't finish the example. When I add 'Monthly' to the end of the expression "[Home Owners].[Theater Freq]" in the Criteria/Argument column, I usually get an error message saying the syntax is incorrect when I switch to the Results view. If I place a dot after the original expression, like this: [Home Owners].[Theater Freq].Monthly, I get an error message that "The dot expression is not allowed in the context at line 4, column 23. Use sub-SELECT instead." What am I doing wrong?
Thanks!
View 1 Replies
View Related
Feb 23, 2007
Either edit the query from Dataset edit dialog or in the query builder (switch the design mode to show the query string), I can't save the query and always get the following error:
TITLE: Microsoft Visual Studio
------------------------------
The query cannot be retrieved from the query builder.
Check the query for syntax errors.
Reporting Services will continue to use the most recent valid query.
------------------------------
ADDITIONAL INFORMATION:
Query preparation failed. (Microsoft.AnalysisServices.Controls)
------------------------------
I am sure there isn't syntax error in the query string because I only add a space after a comma.
View 3 Replies
View Related
Jan 3, 2008
NOTE: I am using Brian Larsons book Delivering Business Intelligence with SQL Server 2005. I am stuck on page 588 - step 20. I am using Windows Vista Ultimate
Once all the Dimensions & attributes I have selected are dragged into the query area in MDX query builder no data is displayed. There is a message: "No rows found. Click to execute the query." - clicking does nothing.
How can I resolve this problem ???
View 2 Replies
View Related
Jan 7, 1999
We have SQL Server 6.5 with an Access 8.0 front-end. We are about to re-write our front-end in VB6 using RDO. Obviously Access has a nice fast and easy to use query builder. My question therefore is - is there any such query builder that can be used within VB6 to simplify query writing ? Or do I have to write all query's and and stored procedures using straight SQL and/or stored procedures. I do not want to use Visdata as it doesn't appear up to the task.
Thankyou whoever helps
Paul
View 1 Replies
View Related
Jun 6, 2008
Hi, I am trying to generate a report and I am using the query builder feature in SQL Server Reporting Services.
I just entered a simple query (actually the query builder generated it for me):
SELECT ID, pname
FROM jira393.project
and it seems to run fine but when I click "OK" to go to the next step, I keep getting an invalid object name error: 'jira393.project'
I don't know what is wrong right now since when I run it, it seems to display the correct information but trying to go to the next step displays that invalid object error.
Does anyone have any idea what is wrong?
View 1 Replies
View Related
Oct 15, 2007
Hi,
I have created a model from the underlying database. Now I want the capability to write SQL Queries when creating reports instead of drag and drop the reports. Is there anyway we can achieve it in MS Report Builder. I would like to know the options available in MS world on this.
Thanks,
S Suresh
View 3 Replies
View Related
Dec 29, 2005
Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.
My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.
View 6 Replies
View Related
May 15, 2008
In my reports I want Date attribute to be added only in the data set and not in the query builder, As if I am adding it in the query builder report is taking very long time to generate and I am getting around 7000 pages of data which is absurd.So, if I can add it only in the data set then I can use filters for my reports and get only the required data. So, is there any way where in which I can add the Date attribute only in the dataset and not in the query builder????
View 1 Replies
View Related
Sep 18, 2007
Hello,
I have a question about using the query builder, or just straight sql, to create expressions. My first example caused the query builder to generate a query configuration error:
Expression: EIM_REQUISITION_DETAILS.EST_UNIT_COST * EIM_REQUISITION_DETAILS.REQRD_QTY AS COST
Syntax error: Expecting "," or "FROM" after column alias or "*"
Initially I thought I was dead in the water but then I decided to just run it anyway and it worked! I got my cost. OK so fine the query tool doesn't recognize some sql expressions. Because I am trying to make a large flat file I thought I'd concatenate 2 fields to generate a recognizable requisition, which everyone knows is the year followed by a hyphen then the req number:
My old Access query had this: Req: Right([REQ_YR],2) & "-" & [REQ_NO]
and I tried to change it to this: substring (EIM_REQUISITIONS.REQ_YR from 2 for 2) || '-' || EIM_REQUISITIONS.REQ_NO AS REQ
So apparently my older sql book doesn't have the syntax right. Since I plan to do more of this could someone point me to a reliable book or site that could help me with sql syntax?
Thanks,
Dana
View 3 Replies
View Related
Jun 6, 2006
Hi
i have a database with several tables and i want to create a Insert query. and i do this by adding the tables to a dataset and create the relations between them and now i click on the table i want to create a query for and select Add Query. and then the TableAdapter Query configuration Wizard pops-up. but the problem is that in the query builder i can add ONLY ONE table, why? i need to add more than one table in order to make my query.
This is how this problem accured: At first i had to two databases, the first database i created myself and there i had all my tables and the second database was the one that contained all the membership info(this database is the one that visual studio creates automaticly for you when u are using the membership thing ASPNETDB.MDF). so i decided to only have one database and i re-created all my tables in the aspnetdb.mfd database, that is where this problem accured, that i can add only one table in the query builder.
appreciate any help :) tnx
View 2 Replies
View Related
Jul 30, 2007
Hello,
I am sure there will be a simple answer to this but it has got me stumped.
Having to move over to Vista with my new machine so I am having to switch to 2005 version for my development but still upload to a 2000 server.
I have had a look at 2005, like the new Management Studio, however I ahve a couple of problems which I can not find the answer to.
Firstly, the SQL Query Builder, where has it gone? I often have to import/export data from Excel files and used to use the SQL query builder to create my queries. If I want to copy all columns it is fine but if I want to import select columns I find it easier to view a list and then just add the ones I want.
Am I missing something here?
Secondly, copying stored procedures, before when running DTS ther were three options, Copy Tables/Views, Data Using Query and Copy Objects.
I used the copy opbjects a lot as it was a very quick way of transfering a group of tables and stored prcoedures that I had created. This appears to have now been replaced with Copy Database, which copies everthing, can can not be used to copy from SQL2005 to SQL2000.
If I want to copy multiple stored procedures from SQL2005 to SQL2000 how is it done now? I have tried finding out but have not been sucessful.
Any help would be greatly appreciated,
Regards,
Lee
View 4 Replies
View Related
Aug 20, 2007
I'm having a heck of a time with named queries. At this point I just want a proof of concept in my DSV. I have a bunch of tables added to the DSV, and I would like to add a named query to it so the user can pick from the name query in report builder. What has to be done to make it appear in the report builder?
View 1 Replies
View Related
May 22, 2007
Does anyone know if there is a limit on characters or lines of code that can be used to create your dataset?
I cut and pasted some sql code of a report in there and it's cutting off the last 100 lines or so.
View 1 Replies
View Related
Aug 19, 2007
I want to make a sqldatasource to insert data ito a table with values from textfields page. I want to use the sqldatasource programically (not bind it to a formcontrol).
I drag a sqldatasource from the toolbox to the design surface and start configuring the datasource. I spesify a custom sql statement, select the "Insert" tab and insert the table I want to store into the builder. Then I select the fields and the values (parameters from tue textfields. I test it with the "Execute query" button and the results is stored in the table. (everything seems ok) I press the "OK" button but both the "Next" and "Finish" buttons are dissabled, o I can not store the query.
What is going wrong. Can someone please help me ?
Tom Knardahl
View 2 Replies
View Related
Oct 17, 2007
Good afternoon all,
I have created a cube in Analysis Services with a time dimension named Time. The data is only needed at the month level, so the fields in the table that the dimension is based on are [DTE MM] and [DTE YR]. In Visual Studio 2005 or in SQL Server Mgmt Studio I can browse the dimension and I see that Time has the following attributes: Year and Month both Regular attributes and Time (Key attribute). There is also a hierarchy named [Year - Month] and if I browse that it looks good dril down from All to Year to Month.
However, when I point my Reporting Services Datasource to the cube, and start Query Builder, I see two dimensions, [DTE MM] and [DTE YR] and no Time dimension.
[DTE MM] has attributes [DTE MM].[Month], [DTE MM].[Time], [DTE MM].[Year], [DTE MM].[Year - Month].
[DTE YR] has attributes [DTE YR].[Month], [DTE YR].[Time], [DTE YR].[Year], [DTE YR].[Year - Month]
This is causing me huge problems in my report. I need to use a date range in the query. To do this, I created Query Parameters and refer to Report Parameters that will be passed in. For example, I use ="[DTE YR].[Year - Month].[Year].&[" + Parameters!StartYear.Value + "].&[" + Parameters!StartMonth.Value +"]" (thanks to Simon Philips) as the query parameter for the Start Year Month, but if I use the [DTE YR].[Year - Month] in my SELECT, the data is not sliced in my query results. That is to say that the Year and Month show correctly, according to the date range, but the Measure is equal for each month and is equal to the total for the cube. To slice the data, I have to use [DTE YR].[Year].[Year] and [DTE MM].[Month].[Month] in my select, but then the data is shown for every month, i.e. the date range is ignored.
What am I doing wrong, or is this a quirk of RS that I can work around?
Thanks,
Kathryn
View 3 Replies
View Related
Feb 6, 2008
I was using the MDX Query Builder to create MDX queries for a SSRS report. I'm not sure what happened, but when I tried to create another dataset against the cube, the "Drop Column Fields Here" and "Drop Row Fields Here" areas were no longer available for me to drop attributes onto.
I have restarted VS, rebooted, you name it, I've tried it (short of re-installing). Has anyone encountered this and how did you "fix" it.
BTW: In order to continue working, I decided to use ProClarity to build the MDX for me and when I tried to paste it into the MDX editor, I get the following error: "The query cannot be prepared: The query must have at least one axis. ..". So, as I've seen from other posts, you can't use "any" MDX in the MDX Query Builder.
Malinda
View 1 Replies
View Related
Mar 12, 2007
Hello,
I wrote a data mining plug-in algorithm in SSAS and now I am trying to view the lift chart in Management Studio. The studio gives the following error:
"The Data Mining Query builder control cannot be initialized due to the following problem:
Deserialization failed: Multiple definition of element 'Column12' causes the content model to become ambigious. A content model must be formed such that during validation of an element information item sequence, the particle contained directly, indirectly or implicitly therein with which to attempt to validate each item in the sequence in turn can be uniquely determined without examining the content or attributes of that item, and without any information about the items in the remainder of the sequence."
I really do not understand what i did wrong. Can anybody help me?
Thanks, Joris
View 4 Replies
View Related
