Database

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.

D Hoffman wrote:

The problem is the stored procedure has IF statements

Bastard Programmer from Hell

BEGIN TRAN
DELETE tblResultNotes FROM tblResultNotes 
INNER JOIN tblResults 
ON tblResultNotes.ResultID = tblResults.ResultID
ROLLBACK TRAN

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 ?

Hum Dum wrote:

Any comments !!

Shameel wrote:

In this case, CASE is an overkill and an IF statement
works perfectly well.

Shameel wrote:

If you see the example, each nested CASE statement..,

jschell wrote:

The correct solution is to use it correctly.

SilimSayo wrote:

Most of our imports come from abroad!

nallelcm wrote:

here is my problem.

-- Just to be on the safe side
BEGIN TRANSACTION

-- The sample table
CREATE TABLE Employees
(
     id       INT
    ,[name]   VARCHAR(50)  
    ,position VARCHAR(50)
);

-- The sample data
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'

-- Making a list of unique positions
SELECT DISTINCT position 
           INTO #Positions 
           FROM Employees;

-- Select all positions, using a FOR XML subquery to fetch the members
-- The REPLACE and SUBSTRING functions are used to remove the XML-tags
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;  

-- Undo everything we just did
ROLLBACK

position                       names
---------------                ------------------
it                             julie
management                     mark, jane
sales                          bob, joe, donald

Bastard Programmer from Hell

ID       Name
-----------------
1        Party
2        Meeting

ID     Name
------------
1      Bob
2      Joe
3      Suzie

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

EventID     EventTypeName      People
-------------------------------------------------------
1           Party              Bob, Joe, Suzie
2           Party              Bob, Suzie
3           Meeting            Bob, Joe

nallelcm wrote:

here is a better example

nallelcm wrote:

Want the output to look like

Bastard Programmer from Hell

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

EventID     EventTypeName      People
-------------------------------------------------------
1           Party              Bob, Joe, Suzie
2           Party              Bob, Suzie
3           Meeting            Bob, Joe

EventId  Name                                       People
-------  ---------------------------------------    -----------------
1        Party                                      Bob, Joe, Suzie
2        Meeting                                    Bob, Suzie
3        Something else that wasn't mentioned       Bob, Joe

Bastard Programmer from Hell