Define Value Labels In A Result Set?
Jun 20, 2006
Is there a SQL statement that I can use which allows me to set value lables in a result set? For example, if a table has a gender variable with values of 'M' & 'F', can I write a sql statement which creates a result set that changes these to 'Male' & 'Female' so that everything I do with the result set shows the new labels (but does not change the underlying M/F values in the orginal table?
I do not want to create a new table, but rather display the M/F values as Male/Female when I access the result set in an application (such as Report Services).
Thanks for any advice you can give.
Ed Sloat
View 3 Replies
ADVERTISEMENT
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Jun 27, 2006
Dear fellows,
LOP_BEGIN_XACT, LOP_COMMIT_XACT,
LOP_INSERT_ROWS
Labels as the ones are easy recognizable when you explore LDFS by mean DBCC or whatever but on the contrary the following ones are tricky to reach the meaning:
LOP_IDENTITY_TYPE
LOP_DELTA_SYSIND
LOP_SET_BITS
Does anyone have any link related with this? I tried hard to search by Goog or something like that but unsuccessfully at all.
View 3 Replies
View Related
Mar 27, 2007
I have a simple bar graph. All I am looking to do is display the values on each bar. Can someone please help me. Thanks
View 1 Replies
View Related
Apr 22, 2008
I have a page that is part of a 5 page wizard. The wizard gathers data for a claim filter. The second page is laid out with four labels at the top
Filter Name: <filterName> -- filterName_LFilter Description: <filterDescription> -- filterDescription_LFilter Data Source: <filterDataSource> -- filterDataSource_LFilter Purpose: <filterPurpose> -- filterPurpose_L
--I have written an sp to populate these four label.Text values. I want to populate them on page load.
Two questions. How do I get four output values out of the sp? Is there a better way to do this than an sp?
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
-- =============================================-- Author: <Author,,Name>-- ALTER date: <ALTER Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[a_spNewFilter_Step1_Summary] -- Add the parameters for the stored procedure here @filterID int ,
@filtername varchar(100) OUTPUT, @filterdescription varchar(250) OUTPUT, @filterOwnerID int OUTPUT, @filterDataSourceID INT OUTPUT, @filterPurposeID int OUTPUT
ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;
--DECLARE @FilterID int
--SET @filterID = 14
SELECT filterIsComplete, filterCreateDate, filterStep1Complete, filterName, filterDescription, das.AuditorFirstName + ' ' + das.AuditorLastName as FilterOwner, dfds.filterDataSource, dfp.filterPurposeFROM a_factSamplingFilter_2 fsfINNER JOIN dbo.a_dimFilterDataSource dfdsON dfds.filterDataSourceID = fsf.filterDataSourceIDINNER JOIN dbo.a_dimAuditStaff dasON das.AuditorID = fsf.filterOwnerIDINNER JOIN dbo.a_dimfilterPurpose dfpON dfp.filterPurposeID = fsf.filterPurposeIDWHERE filterID = @filterIDGROUP BY filterIsComplete, filterCreateDate, filterStep1Complete, filterName, filterDescription, das.AuditorFirstName, das.AuditorLastName, dfds.filterDataSource, dfp.filterPurpose
END
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
----
protected void Page_Load(object sender, EventArgs e) {
int filterID = int.Parse(Request.QueryString["filterID"]); /// grab the filterID from the URL query string
string connectionString = WebConfigurationManager.ConnectionStrings["DDT"].ConnectionString; SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("a_spNewFilter_Step1_Summary", con); // get the values from filter setup step1 cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FilterID", SqlDbType.Int)); // using the filterID created in step1 cmd.Parameters["@FilterID"].Value = filterID;
cmd.Parameters.Add(new SqlParameter("@FilterName", SqlDbType.VarChar, 100)); cmd.Parameters["@FilterName"].Direction = FilterName_TB.Text;
cmd.Parameters.Add(new SqlParameter("@FilterDescription", SqlDbType.VarChar, 255)); cmd.Parameters["@FilterDescription"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@FilterOwner", SqlDbType.VarChar,50)); cmd.Parameters["@FilterOwner"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@FilterDataSource", SqlDbType.VarChar,50)); cmd.Parameters["@FilterDataSource"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@FilterPurpose", SqlDbType.VarChar,100)); cmd.Parameters["@FilterPurpose"].Direction = ParameterDirection.Output;
con.Open();
try { cmd.ExecuteNonQuery(); } finally { con.Close(); }
///get name, description, owner, datasource, purpose
}
View 1 Replies
View Related
Dec 14, 2005
Hi!
I have these tables:
DB1
MID
IIN
Title
FullName
Address
Apt
City
Province
PostalCode
DB2
MID
Lang
Version
How can I output a text file formatted like the one below?
(05) 01046 (F 05)
Mr. Sylvain Cote
123 Sesame Street
Apt 6
Charny, BC
L8T 5G6
where
(05)-MID
01046-IIN
(F 05) - Lang
$3.99/yr .COM!
http://www.greatdomains4less.com
View 3 Replies
View Related
Oct 25, 2007
Hi,
We are having trouble getting labels to print correctly when there is a lot of data close together on the labels. These are more than just address labels, so there are many lines, graphics, and borders. Are there additional tools or suggestions for keeping labels consistant?
Thanks
View 1 Replies
View Related
Mar 16, 2007
Ok, I'm an idiot. Does anyone out there have some step by step instructions for creating a report that will do mailing labels. I've got multiple columns on my report but I end up with the contents of each column being the same across each grouping/row. In other words for each record read, since I have three columns, I get three occurances of each customer and address until all the data selected for that row is presented, then I go to the next customer which again is repeated across all three columns.
I'm sorry I'm so stupid, but I've looked at other entries on the forum, and I just can't figure it out. We are trying to get out from underneith Business objects thumb and any help would be greatly appreciated. Thanks! - Eric -
View 4 Replies
View Related
Aug 31, 2007
I have a pie chart and have tried every option I could find on the Data tab for the point labels. They bunch up all over the place. What am I missing?
Does Dundas do this better?
Jerry
View 4 Replies
View Related
Mar 2, 2006
I have a line chart that has several series, and it routinely leaves off 1 or 2 point labels on the last X-axis.
Is anyone else experiencing this? If so, does anyone know what the cause is, and is there a fix?
Thanks!
BobP
View 15 Replies
View Related
Aug 24, 2006
Hi..
Just want to ask if anyone has encountered the same problem..
I have used the Simple Bar Graph to display the list of employees with the most number of emails/chats and sorted it so that the highest number of emails will appear at the bottom of the graph. The problem is that the graph doesnt display a label on the highest number of email/chats. It displays the number of email/chats that the other employee has and its only the employee with the highest number of email/chats that has no number label. It just wont show and I have no idea now on what to do about it.
Does anybody have a solution for this one?
View 4 Replies
View Related
Jul 28, 2007
hi there, i have a payment page which uses javascript to calculate payments and what remains etc. That all works fine, the problem i have is that when you are finished and you go to the next page it should save all of the records to the order table where the order_id = a querystring. Now i have tried using an insert method and an update method, not really sure whats the difference when putting data into an existing record. What happens though is that i can save the contents of labels etc but it wont save the content of dropdown lists (of which there are 2) i just cant work it out, i have posted my code below
string strOrderID = Convert.ToString(Request.QueryString["OrderID"]);
string strDiscountPercent = Convert.ToString(5656);double dblDiscountMoney = Convert.ToDouble(txtDiscountMoney.Text);
string strPaymentMethod1 = ddlPaymentMethod1.SelectedValue;double dblPaymentAmount1 = Convert.ToDouble(txtPaymentAmount1.Text);
string strPaymentRecipt1 = txtPaymentRecipt1.Text;string strPaymentMethod2 = ddlPaymentMethod2.SelectedValue;
double dblPaymentAmount2 = Convert.ToDouble(txtPaymentAmount2.Text);string strPaymentRecipt2 = txtPaymentRecipt2.Text;
string sConnectionStringCustInfo = "my connection string";
SqlConnection objConnOID = new SqlConnection(sConnectionStringCustInfo);
//This is the sql statement.using (objConnOID)
{
objConnOID.Open();
string sqlUpDate = "UPDATE tbl_order SET discount_Percent = " + txtDiscountPercent.Text +
", discount_money = " + dblDiscountMoney + ", payment_method1 = " + strPaymentMethod1 + =========== this is the problem line here!
", payment_amount1 = " + dblPaymentAmount1 + ", payment_ref1 = " + strPaymentRecipt1 +
", payment_amount2 = " + dblPaymentAmount2 +", payment_ref2 = " + strPaymentRecipt2 +
"WHERE order_ID = " + strOrderID;
SqlCommand objCmd1 = new SqlCommand(sqlUpDate, objConnOID);
try
{
objCmd1.ExecuteNonQuery();
}
and because it throws an error it wont update the database with any other record. does anyone have any ideas, i have spent hours last night trying to figure this one out and im sure its simple! but i need some help!
Cheers
Jez
View 5 Replies
View Related
Apr 1, 2008
i have an sqldatasource that has a 1 row dataset
how can i read these values to labels?
thanks
View 1 Replies
View Related
Oct 10, 2007
I addressed this situation in another thread, but since the thread had not been updated for some time I felt a new thread may have been warranted to included uptodate information and requests for further updates.
I will refresh the topic's primary issue:
When using a matrix, a developer wanted to provide column headers atop the columns to help users distinquish between multiple columns of the same relative data type(columns of digits, etc.)
The space in the upper left corner of the matrix will create a void that cannot be broken down or divided so that a column header label can be applied. It's either one label for the whole void or nothing at all.
VOID
Upper Group Column
Sub Group Column
Row Column 1
Row Column 2
Row Column 3
Row Column 4
Data
A poster suggested placing a rectangle inside the voided area above.
Rectangle
Upper Group Column
Sub Group Column
Row Column 1
Row Column 2
Row Column 3
Row Column 4
Data
Then placing a series of textboxes within the rectangle area aligned to the width of the row columns
Rectangle
Upper Group Column
rectangle txtbox
rectangle txtbox
rectangle txtbox
rectangle txtbox
Sub Group Column
Row Column 1
Row Column 2
Row Column 3
Row Column 4
Data
The problem is that when the report is run and rendered the rectangle txboxes are no longer aligned at the bottom. Even though the text within the textboxes is set to the bottom.
Instead the rendered version appears like this.
rectangle txtbox
rectangle txtbox
rectangle txtbox
rectangle txtbox
Upper Group Column
Sub Group Column
Row Column 1
Row Column 2
Row Column 3
Row Column 4
Data
Thus rather than having the labels inside the rectangle textboxes aligned at the bottom so that they appear more in-line with their assigned columns, they are automatically top-justified and this makes the labels look awkward.
So does anyone have any suggestions on how I can get the rectangle textboxes to be aligned adjacent to the tops of the row columns so that their labels appear in-line with their appropriate columns?
Thanks in advance.
View 1 Replies
View Related
Jul 5, 2006
I have a horizontal barchart with integer values, both positive and negative. Major gridlines are shown and I have not specified the interval or the label format. Sometimes the gridline labels (Y axis) show decimal values. Is there anyway to format these? I have tried to format the labels as "#0" but then I see labels occuring twice. I have also played with the intervals, but then sometimes, depending on the values, the zero line is not being shown.
Does anybody have an idea?
Thanks in advance!
Joos
View 4 Replies
View Related
Jan 11, 2007
Hi People,
I'm with a new problem.
I creted a simple bar type and sometimes the values of point labels are showing inside of bar, This is a problema, because if the length of bar will be short, the value doesn't shows. How can I make to force the point label value outside of bar ??
Thanks
View 1 Replies
View Related
Aug 1, 2013
I want to line up across the top of a cross tab Jan-12 Feb-12 ... July-13 Aug-13 up to and including the latest month in the db. What's the best way to achieve this?
I currently create crosstabs with case statements as follows:
select
sum(case month(date) when 1 then value) as Jan,
sum(case month(date) when 2 then value) as Feb
from db
I figured you could create some sort of while loop for the case statements but I can't get the syntax right.
Or another strategy would be to use the Pivot method and build [Jan], [Feb], [Mar] etc through a loop?
View 3 Replies
View Related
Jul 9, 2007
I am creating a report to be used to print mailing labels. Size 1" x 2 5/8" (3 columns of 10 labels). I seem to have everything but the vertical spacing working properly. I have used a rectangle set to the size of the labels which contains a list box containing text boxes containing expressions to pull in the fields. Some of the street addresses are two lines with the majority being only one line.
I have joined the two fields containing the street addresses and inserted a new line command so each field will print on its own line but yet be contained within the same text box. Everything except the text box containing the street addresses are set to not increase or decrease. My problem is that on the pages of the report where labels vary between the one and two line street addresses the labels tend to creep down which in some cases causes data to be printed over two labels. Any ideas on what I can do to format these to print properly?
Thanks
View 44 Replies
View Related
Sep 22, 2007
Hi,
I need to create address labels from report builder. I can create the same from report designer using list box and text boxes. But not able to create the same from report builder as it is supporting only one layout - table (Columnar) for table type. Can any one help me to place the filelds in row wise rather than column wise.
View 3 Replies
View Related
Dec 18, 2006
Hi. I'm currently having some troubles with the Reporting Services charts and I was hoping I could get some help here.
The issue here is that I'm using charts with horizontal bars, and I can't seem to be able to control the alignment of the Point Values. I've read that the alignment can't be set while using stacked bar charts, but I'm not using a stacked bar chart, just a simple horizontal bar chart.
A workaround for this would be to use different colors for the values displayed outside the bars than from those displayed inside the bars (for better visibility). I had expected RS to display the values inside the bars when they are greater than a set percentage of the chart range (e.g. 95%). Alas, this is not the case, as it seems to consider the number of digits the values have AND the grouping. At least.
So, does anybody have an idea about what could be done in this particular situation?
View 1 Replies
View Related
Mar 26, 2008
I've created a chart and I'm using point labels. When I have two series, and the values on one bar of one of the series is zero, the point label for that bar shows up at the same point on the chart as the value for the other series--in other words, it shows up in the middle of the chart rather than at the bottom, where a value of zero should be. Any ideas on how to fix this? It just looks stupid.
Example--sales for customer 1 and 2 for June and July. Sales for cust 1 for June is 5, for cust 2 is 3; point labels show up at the tops of the bars. Sales for cust 1 for July is 0, cust 2 is 6; point label for cust 1 shows up near 5 (where the value for the other month of data shows), and for cust 2 at the top of the bar.
Thanks!
View 1 Replies
View Related
Aug 15, 2007
I am trying to print mailing labels and suppress optional address lines to eliminate white space while maintaining label alignment. This is what I am trying but it does not work.
I create a function that determines the length of a field (i.e. street) and increments a counter if the length is zero. It also takes a second parameter that detremines whether to reset the counter. I then use the function in the visibility each row of the label table. Example iif(Code.LineLen(Fields!Street.Value)=0,True,False). I place this on the visibility of each row except the last as that is City,State,Zip and is required. On this last row the expession I use is
=Fields!city.Value & ", " & Fields!state.Value & " " & Fields!zip.Value & iif(Code.LineCount<5,StrDup(5-Code.LineCount,vbCRLF),""). What this is intended to do is print a carraige return and line feed for every row that did not print. Instead no line feeds occur. I have verified that the Code.LineCount is indeed calculating correctly (I printed the value). I've removed the conditional to make sure it has no issues but again nothing. For clarification the code block I am using is this.
Public LineCount as Integer=0
Function LineLen(byval LineIn as string,byval IsFirst as boolean) as Integer
if IsFirst=True then
LineCount=1
end if
If len(LineIn)>0 then
LineCount=LineCount+1
end if
LineLen=Len(LineIn)
end Function
So if anyone either knows why the line feeds do not occur or a better way to handle this please let me know. Thanks.
View 1 Replies
View Related
Jul 3, 2015
Is it possible to have the value labels always on top of the bars of a bar chart?
On the Chart Series Labels properties under Appearance, I used Top for Position, but if the bar reaches the top of the chart area (and that is determined automaticaly by the Y Axis that and can't be a fixed value).
View 5 Replies
View Related
Sep 11, 2007
I have a chart that presently has two groupings on the x-axis: Year & Qtr. I'd like the Year labels to be one color, and the Qtr labels to be a different color. (All year labels should be Black, all Qtr labels should be Blue). I can see that an expression can be written to handle this dynamically, but I'm banging my head trying to figure it out...
Any help?
Thanks in advance,
Pete
View 1 Replies
View Related
Dec 1, 2007
I am trying to set a variable to be passed to my asp page but the value is not carrying over. I don't even know if this is supposed to work or not below:
<td colspan="3" style="font-size: 10pt; vertical-align: top; color: <%=Div7fontcolor%>; background-color: <%=Div7bgcolor%>; text-align: center; width: 0px;" id="TopBox" runat="server">
The above Div7fontcolor and Div7bgcolor are variables that I set in the VB file as shown below:
Dim obConnection As SqlConnection = New SqlConnection("Data Source=localhostsqlexpress;Initial Catalog=OrgBoard;Integrated Security=True")Dim obCommand As SqlCommand = New SqlCommand("SELECT Divisions.*, Dept19.*, Dept20.*, Dept21.*, ConfigDisplay.* FROM Divisions CROSS JOIN Dept19 CROSS JOIN Dept20 CROSS JOIN Dept21 CROSS JOIN ConfigDisplay", obConnection)
obConnection.Open()Dim dr As SqlDataReader = obCommand.ExecuteReader()
dr.Read()
Dim Div7bgcolor As String = dr("Div7color").ToString().Trim()Dim Div7fontcolor As String = dr("Div7textcolor").ToString().Trim()
dr.Close()
obConnection.Close()
The web page runs fine with no errors but the value does not carry over and change the property correctly.
I am able to set the bgColor value of the TopBox within the VB file if I use "TopBox.BgColor = Div7bgcolor" but I can't set other values on my web page like the font color. If I can do this from the VB file then please correct me. Using variables within the page will allow me to set almost any value but I don't even know if what I want is possible. Any help is greatly apprectiated.
Thank you,
Kris
View 1 Replies
View Related
Jan 30, 2007
Is it possible to repeat the row labels for matrix reports where there are multiple groups. So if the matrix report rows are department,division,employee then have all three labels show up on each row not just at the breaks. Cannot seem to find a way to do this. thanks
View 1 Replies
View Related
Mar 7, 2007
I'm trying to do the equivalent of an Excel chart "Number of categories between tick-mark labels" on the X-Axis of a SSRS chart. Can't see anyway to do it. I can get it to display differently by doing Label = IIF(somethingistrue, onevalue, anothervalue), but can't see anyway to simply not show the label at all.
Any suggestions.
Thanks
Richard
View 7 Replies
View Related
Jul 20, 2005
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies
View Related
Dec 26, 2007
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
View 5 Replies
View Related
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 Replies
View Related
Aug 2, 2007
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View 3 Replies
View Related
