A Trick With SQL
Sep 8, 2007
Hi people - need help with a little thing on SQL...
I have a function that returns a table of values where one row is called 'UserID' - lets call this table 'x'
I have a another table with a row called 'UserID' in it - lets call this table 'y'
I want an SQL statement that achieves:
Select everything from table x (the table that was generated by the function) where the UserID is not in any row of table y.
Anyone think they can help?
Regards,
Will
View 3 Replies
Feb 17, 2007
Hi all...I would like to know how it is possible to make my problem below all in ONEquery or stored procedure.I select some rows from a table where the resultset is one column with somevalues. Lets say 1, 4 and 7:row val1 12 43 7Then I would like these results to manipulate another table together withanother value (lets say some 'b' with value 5)Lets say the other table looks like this:id a b text1 1 1 'Some text'2 1 3 'Some text'3 1 5 'Some text'4 4 5 'Some text'5 7 4 'Some text'6 2 5 'Some text'in the above example the rows with id 3 and 4 match my criteria because 1and 4 (and not 7) was in the column 'a' together with the value 5 in column'b'.Here comes the tricky part (at least for me):Now because a row without the 'a' value 7 and the 'b' value 5 existed in thetable I would like to create one row with those values.Also because the 'b' column did have a the value 5 (the row with id 6)without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row shallbe deleted.Then at last I would like the resultset matching 'a' column of 1, 4 and 7AND 'b' column 5 as a resultset.I hope that it is understandable and someone can help.- rick -
View 1 Replies
View Related
May 13, 2008
Hi everyone...
I have the following records...
Delivery_Table
ID Description PartNumber
1 one 123
Parts_Table
ID PartNumber Margin Unit_Price
1 123 2.3 0.0
2 123 0.0 63.69
If I join these two tables based on PartNumber, it returns me two records. What I want is that I want to select one record only (IF Margin > 0, than it should take the Margin Record, Otherwise, it should take Unit_Price Record)
For example,
ID Description PartNumber Margin Unit_Price
1 one 123 2.3 0.0
OR
ID Description PartNumber Margin Unit_Price
1 one 123 0.0 63.69
Any clues?
View 1 Replies
View Related
Jul 2, 2007
Hi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.
I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)
The problem is that the function should return Integer and if the value is zero I need to return empty string.
It does't say any thing about returning the string but when it trying to use the format on a string it give me a worning and print #ERORR in the cell.
is there a solution for this?
Do you know the syntax to format the number in the function before the return?
(Sorry for the dumb question but I know C# not VB.net and there is no intelisance in that editor).
Thanks a lot!
View 3 Replies
View Related
Aug 11, 2002
Hi all. Here is a little trick I came up with the other day. Although a bunch of yu may have already thought of it, I thought I might share it.
I had a directory with about 30 .sql files in it (each one a Create Procedure statement). I needed to deploy all the files to a live database. Being the incredibly lazy person I am I didn't want to open up each file and run it.
So, I dropped into DOS, went to that directory and typed
type *.sql > newfile.sql
This created a new file that contained all of the other files, I could just run that file in query analyzer and I was done.
Give it a try!
Damian
View 6 Replies
View Related
Mar 6, 2008
I have a table that looks like this:
RecordId
PictureId
KeywordId
111
212
313
421
522
623
725
817
932
1044
I need to run a query where I pass in an unknown number of KeywordIds that returns the PictureId. The 'IN' clause will not work because if a KeyWordId gets passed into the Stored Procudure the PictureId must have a record with each KeyWordId being passed in. For example, lets say you need to see the result of all PictureIds that have both 1 and 2, the correct result set should only be PictureId 1 and PictureId 2.
Im going crazy trying to find a simple solution for this. Please advise.
View 7 Replies
View Related
Apr 16, 2007
I have a custom folder layout for reporting services on our test environment so each tester can test the reports against the data in their own instances. The developers have reports in a different layout that's more in line with what will be in production. So I figured some simple RS scripting would handle things.
It was easy to create the folder structure, easy to create the shared data sources in each testers reports folders, and easy to deploy the report from the developers folder (source) to the testers report folders. However, when I try to use SetItemDataSources to change the data source in the newly duplicated reports, I keep getting an error telling me that the data source cannot be found.
Here's the relevant code fragment:
'Set report DataSource references
Dim DataSources(0) As DataSource
DataSources(0) = New DataSource
' update with new data source info
DataSources(0).Name = strDSName
Dim Item1 as DataSourceReference = New DataSourceReference
Item1.Reference = strRef
DataSources(0).Item = Item1
Console.WriteLine( "Setting report {0}, data source to {1}", strTargetReport, strRef)
RS.SetItemDataSources( strTargetReport, DataSources)
strDSName is "OLTP", strRef is the path to the shared data source, "/Reports/Tester1/Data Sources/OLTP". strTargetReport is the name of the report itself, full path name to "/Reports/Tester1/Report1".
Each tester has their own folder off the main reports folder "Reports", with an incremental number, as in "Tester1". Each testers folder has it's own Data Sources folder. There is only one data source for all reports, "OLTP".
/Reports
/Reports/Tester1
/Reports/Tester1/Data Sources
/Reports/Tester1/Data Sources/OLTP
/Reports/Tester1/Report1
/Reports/Tester1/Report2
I even went into SQL Server Management Studio and tried changing the (now broken) data source setting for a report to point to the correct data source (OLTP), then copied the generated script, then ran it - and still got the data source cannot be found error. That's what my latest code shown above was based on.
Anyone got any clues?
Thanks,
--Stan
View 3 Replies
View Related
Sep 25, 2007
I'm a bit stumped at this. I have done this before a bunch of times. The difference is that this time I have two rectangles in the footer. For the each, the visibility is set to an expression which checks whether the page is page 1 or 2. If it is 1, the first rectangle is visible, if 2, the second. When I reference the hidden fields on the main area of the report via ReportItems, they come through fine on the first rectangle, but are invisible on the second. I've tried the same exact fields and every time they are visible in rectangle1, and invisible in rectangle2. What is going on here???
View 2 Replies
View Related
