|
I am using Visual Studio 2010 Pro to make a report. I am working off someone else's stored procedure that I am not allowed to modify.
The problem is the stored procedure has IF statements, by way of example:
IF (criteria_1 = 'A')
BEGIN
SELECT
col_for_a_is_varchar100
FROM
some_table_1
END
IF (criteria_1 = 'B')
BEGIN
SELECT
col_for_b_is_varchar50
FROM
some_table_1
END
My problem is that in VS I have to use the name of column in the table but depending on what criteria_1 is equal to I could have a different column name: col_for_a_is_varchar100 vs. col_for_b_is_varchar50. I don't think there will be a problem with one being a VARCHAR(50) and the other a VARCHAR(100).
I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working.
Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work.
If anyone could help me out it would save me from this grunt work and let me get back to coding in TeraData SQL and SAS again.
|
|
|
|
|
I would suggest writing your own stored procedure.
|
|
|
|
|
D Hoffman wrote: I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working.
Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work.
There would be a problem for taking two different name for two different conditions... problem is if you want to show both values in one report it will show only one label field. For this you can produce another label field and suppress it depending upon the condition the query returning. The best i would suggest you to produce same name in both cases and you can write convert() for converting to Varchar(100) and Varchar(50).
|
|
|
|
|
D Hoffman wrote: The problem is the stored procedure has IF statements
Having an IF isn't a problem, returning different views based on the parameters is.
Adding another SP sounds like the best way to go. What's there prohibiting the creation of a new stored procedure?
Bastard Programmer from Hell
|
|
|
|
|
Solved the issue.
Parameters can be specified that don't show up in the list of preview items. If these parameters are returned (the IF conditions are met and the different view is instead displayed) then these parameters have the requisite value, otherwise they don't and aren't applicable.
Thank you for the attempts to answer my question but they were all incorrect.
|
|
|
|
|
I am using a windows 2000 operating system (not by choice). A VB6 program uses an access database. I am converting access db to MSDE 2000 db. I need to write a query that Deletes all of the records in a table called ResultNotes. Below is a brief schema of the db with relationships.
tblResultNotes - this is the table I want to delete from
ResultID - Links to ResultID in teh tblResults table
Notes
tblResults
startTime
JobID - Links to JobID in tblJobs
ResultID
tblJobs
CreateDate
JobID
I need to delete all records from tblResultNotes that have a ResultID corresponding to the tblResults table. The records that have the ResultID in the tblResults need to correspond to the tblJobs JobID. The actual criteria for deletion is that startTime and CreateDate are older than 12/1/2000.
Any help is appreciated!
|
|
|
|
|
Since you did not include what you have tried.
Try using an inner join
BEGIN TRAN
DELETE tblResultNotes FROM tblResultNotes
INNER JOIN tblResults
ON tblResultNotes.ResultID = tblResults.ResultID
ROLLBACK TRAN
Look up BEGIN TRAN to see what to replace ROLLBACK TRAN with to commit the delete.
|
|
|
|
|
What is equivalent of MS Access IFF in MS Sql for this expression
IIf([ID_SECTION]=1,1,IIf([ID_SECTION]=2,2,IIf([ID_SECTION]=3,[ID_Tariff_Gender],0)))
Will it be like
CASE WHEN [ID_SECTION]=1 THEN 1
ELSE
CASE WHEN [ID_SECTION]=2 then 2
ELSE
CASE WHEN [ID_SECTION] = 3 then [ID_Tariff_Gender] ELSE 0 END
END
END
Is it correct ?
Any comments !!
|
|
|
|
|
If you want to use a SQL CASE statement, then try the following:
CASE [ID_SECTION]
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN [ID_Tarriff_Gender]
ELSE 0
END
|
|
|
|
|
Hum Dum wrote: Is it correct ?
Yes, it works.
Hum Dum wrote: Any comments !!
In this case, CASE is an overkill and an IF statement works perfectly well.
|
|
|
|
|
I think it isn't overkill you're working on a select statement. [ID_SECTION] is probably a column name ---hint that a select statement is being used.
|
|
|
|
|
Shameel wrote: In this case, CASE is an overkill and an IF statement
works perfectly well.
As a general statement I disgree with that.
The IF is an acceptable alternative if CASE didn't exist but CASE exists specifically to deal with situations exactly like that.
Given your statement what situation would be correct for CASE then?
|
|
|
|
|
If you see the example, each nested CASE statement has only one WHEN clause, and this is where I recommended an IF clause which works exactly the same way and is more readable.
There are, of course, situations where only CASE can be used and an IF cannot be used, like for example, in a SELECT statement.
|
|
|
|
|
The question refers to a column [ID_SECTION] which implies a select/insert/update/delete statement. [ID_SECTION] couldn't possibly be a variablle since it is not prefixed by '@'.
|
|
|
|
|
Shameel wrote: If you see the example, each nested CASE statement..,
Your statement wasn't clear but is still incorrect.
For the example given it is misusing CASE. The correct solution is to use it correctly.
|
|
|
|
|
jschell wrote: The correct solution is to use it correctly.
Most of our imports come from abroad!
|
|
|
|
|
SilimSayo wrote: Most of our imports come from abroad!
Imports become necessary when locals lack quality and become unaffordable!
|
|
|
|
|
here is my problem.
table: Employees
id name position
----------------------------------
1 bob sales
2 joe sales
3 mark management
4 jane management
5 julie it
6 donald sales
what I want to do is write a query that would spit out
position people
------------------------------
sales bob, joe, donald
management mark, jane
it julie
so something like this
SELECT position, somefunction(names) AS people FROM Employees GROUP BY position
does that 'somefuction' exist? if not how would i emulate it.
I'm doing this for reporting purposes.
|
|
|
|
|
nallelcm wrote: here is my problem.
I included a script that generates your demo-data; just copy and paste to give it a try.
BEGIN TRANSACTION
CREATE TABLE Employees
(
id INT
,[name] VARCHAR(50)
,position VARCHAR(50)
);
INSERT INTO Employees (id, [name], position)
SELECT 1, 'bob', 'sales'
UNION SELECT 2, 'joe', 'sales'
UNION SELECT 3, 'mark', 'management'
UNION SELECT 4, 'jane', 'management'
UNION SELECT 5, 'julie', 'it'
UNION SELECT 6, 'donald', 'sales'
SELECT DISTINCT position
INTO #Positions
FROM Employees;
SELECT position,
SUBSTRING(names, 0, LEN(names) - LEN(', '))
FROM (SELECT position,
REPLACE(REPLACE((SELECT [name]
FROM employees e
WHERE e.position = p.position
FOR XML AUTO), '<E name="', ''), '"/>', ', ') AS
names
FROM #positions p) AS tmp_cte;
ROLLBACK
Output on my machine;
position names
--------------- ------------------
it julie
management mark, jane
sales bob, joe, donald
Bastard Programmer from Hell
|
|
|
|
|
Ok, well how well would this translate into this situation. I never really did explain myself very well..
here is a better example
EventType
ID Name
-----------------
1 Party
2 Meeting
Person
ID Name
------------
1 Bob
2 Joe
3 Suzie
Event
UID EventID EventType Person
-------------------------------------------------------
1 1 1 1
2 1 1 2
3 1 1 3
4 2 1 1
5 2 1 3
6 3 2 1
7 3 2 2
Want the output to look like
EventID EventTypeName People
-------------------------------------------------------
1 Party Bob, Joe, Suzie
2 Party Bob, Suzie
3 Meeting Bob, Joe
|
|
|
|
|
nallelcm wrote: here is a better example
That's not a better example, but a different one. It would have been better if it included a script to generate the sample data
nallelcm wrote:
Want the output to look like
I want food.
Back in a bit
Bastard Programmer from Hell
|
|
|
|
|
The same trick, basically;
BEGIN TRANSACTION
CREATE TABLE #EventType
(
ID INT
,[Name] VARCHAR(50)
)
INSERT INTO #EventType
SELECT 1 ,'Party'
UNION SELECT 2 ,'Meeting'
UNION SELECT 3 ,'Something else that wasn''t mentioned'
CREATE TABLE #Person
(
ID INT
,[Name] VARCHAR(20)
)
INSERT INTO #Person
SELECT 1 ,'Bob'
UNION SELECT 2 ,'Joe'
UNION SELECT 3 ,'Suzie'
CREATE TABLE [#Event]
(
UID INT
,EventID INT
,EventType INT
,Person INT
)
INSERT INTO [#Event]
SELECT 1, 1, 1, 1
UNION SELECT 2, 1, 1, 2
UNION SELECT 3, 1, 1, 3
UNION SELECT 4, 2, 1, 1
UNION SELECT 5, 2, 1, 3
UNION SELECT 6, 3, 2, 1
UNION SELECT 7, 3, 2, 2
SELECT DISTINCT EventId
INTO #SomeTable
FROM [#Event]
SELECT EventId
,[Name]
,SUBSTRING(CompoundColumn, 0, LEN(CompoundColumn) - LEN(', '))
AS People
FROM (
SELECT ST.EventId
,ET.[Name]
,REPLACE(REPLACE(
(SELECT P.[Name]
FROM #Event E
JOIN #Person P ON E.Person = P.ID
WHERE E.EventID = ST.EventID
FOR XML AUTO),
'<P Name="', ''), '"/>', ', '
) AS CompoundColumn
FROM #SomeTable ST
LEFT JOIN #EventType ET ON ST.EventId = ET.ID
) AS tmp_cte
ROLLBACK
Desired result
EventID EventTypeName People
-------------------------------------------------------
1 Party Bob, Joe, Suzie
2 Party Bob, Suzie
3 Meeting Bob, Joe
Result on my machine
EventId Name People
------- --------------------------------------- -----------------
1 Party Bob, Joe, Suzie
2 Meeting Bob, Suzie
3 Something else that wasn't mentioned Bob, Joe
Are you sure that your sample output is correct?
Bastard Programmer from Hell
|
|
|
|
|
Thank you!!
in the Event the EventID is a UID for the specific event. EventType is a FK to the EventType table.
So there should be 2 parties (event id 1 and 2) and 1 meeting (event id 3)
I need to look up how this XML stuff works :/
|
|
|
|
|
My pleasure
|
|
|
|
|
Hi All,
table
-----
NAme Age Total
---- --- ------
Ram 26 800
kumar 36 300
Sam 34 200
output should be
------
Name:Ram Age:26 Total:800 ;Name:Kumar Age:36 Total:300;Name.....
Please help me with this .tried using COALESCE
but I could not achieve..
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|