Conditional Expression Quirk?

Aug 8, 2007

When using the conditional expression in a derived column transformation, I found that the following expression:

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : [F1Name]

is invalid while


[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : "" + [F1Name]

is valid.

In both cases, the output type is set to Unicode String (DT_WSTR) with 4000 characters. The error in the first case is:

Error at Data Flow Task [Derived Column (2784)]: Failed to set property "Expression"on "output column" "FactorName" (2918).

Would this be considered a bug, or is there a reasonable explanation?

Thanks,
Anna.

View 7 Replies


ADVERTISEMENT

Conditional Expression

Feb 5, 2008

In the conditionnal transformation, I have an expression like below..


ISNULL( [Last_Updated] ) ? (DT_DBTIMESTAMP) "01/22/1990 " : [Last_Updated] >= @[User::VdtLastUpdatedDatetime_Visit]

I keep gettiing an error: DT_DBTIMESTAMP and DT_BOOL are incompatible for the conditional operation....

How can I fix this error?

View 5 Replies View Related

Conditional Expression - I.e., IIF In Access

Feb 9, 2004

I have a query with a conditional expression that I can do just fine in Access but I am having a bear of a time trying to create a similar SQL View. Baiscally I want to say, if column A is null, use value B else use value C.

In Access the SQL is this:

SELECT IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]) AS Casnumber, Sum(qryweldingrod3a.CFume) AS CFume, Sum(qryweldingrod3a.cslag) AS cSlag
FROM qryweldingrod3a INNER JOIN tblconstituents ON qryweldingrod3a.CASNumber = tblconstituents.CASNumber
GROUP BY IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]);


But I know you can't use the IIF statement in SQL so I was trying CASE and was still coming up empty handed. Here is what I produced in SQL but it didn't work:

SELECT SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CFume) AS CFume, SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CSlag) AS cSlag,

CASNumber = CASE Type
WHEN categoryCode IS NULL THEN dbo.tblConstituents.CASNumber ELSE CategoryCode
END,
FROM dbo.tblConstituents INNER JOIN
dbo.RecycleWR_qryWeldingRod3a_LBS ON dbo.tblConstituents.CASNumber = dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber
GROUP BY dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber

Any ideas would be greatly appreciated.

View 8 Replies View Related

Expression In Conditional Split

Jan 24, 2008

In the conditional split transformation,
I am trying to pass the expression like below.


ISNULL(DT_STR(10,1252) [Visit_Date] ) ? "01/01/1990" : [Visit_Date] ! = ISNULL( DT_STR(10,1252)[Visit_Date_Original] ) ? "01/01/1990" : [Visit_Date_original]

but it keeps giving me a syntax error.. what am i doing wrong here?

thanks,

View 2 Replies View Related

Look For Null Value In A Conditional Expression

Mar 9, 2008

Hi,
I need to create a conditional expresssion where if a null value is found, then a letter "N" must be inserted.
I create this expression but didnt work: (COL1 == NULL) ? "N" : COL1
So, if COL1 is null, then N .. but if it is nt null tehn COL1 = COL1
I think i', having a problem expressing the null.
Any idea for this?
Thanks!

View 4 Replies View Related

Using Regular Expression In Conditional Split?

Jun 29, 2007

I have as csv-file wich I import into an SQL Server table. Now I want to do some checks on it. I use a conditional split to direct data to the other tables (1 table for the correct data, 1 table for the rejected data).



Is it possible to use a regular expression in a case in a conditional split to check if a columns has the right format?

If yes? How do I do that?

If no? What is the alternative?



Thanks!

View 1 Replies View Related

Conditional Split - Expression Evaluates To Null

Sep 5, 2007



Hi everyone!


I'm using a conditional split to discriminate modified records. My expression looks like this:
col1_source != col1_dest || col2_source != col2_des.....and so on. I use OLE DB Command afterward to update modified records.

It all works fine if no columns evaluate to null. If any (source or dest.) evaluates to null, component fails.

Any tips how to solve a problem?

It has to work like this:

If colX_source is null and colX_dest is not null --> Update
If colX_source is not null and colX_dest is null --> Update
If both colX_source and colX_dest are null --> No update

p.s. i apologize if a similar thread exists, I haven't found something of use to me.

View 13 Replies View Related

Conditional Processing From A Common Table Expression (CTE)

Sep 5, 2007

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:


WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */
END
ELSE
BEGIN
/* Do something else here */
END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

View 3 Replies View Related

Expression: Conditional Formatting Of Field Size!

Apr 11, 2008

Can I build an expression that allows me to change the field size of a column or row in SSRS2005?

View 5 Replies View Related

Short Circuit Evaluation In Conditional Split Expression

Jun 23, 2006

I think I know the answer to this but thought I'd ask anyway. 

I have a conditional split to check a column for null values or empty string values.  It looks like this:


(!ISNULL(Ballot)) || (LEN(TRIM(Ballot)) > 0)
My question is:  Are both sides of the expression evaluated?  My testing says yes, because a Null value causes an error.  Is there a way to short circuit the evaluation like the || operator in C# or the (less than elegant, and seemingly threatening) OrElse operator in VB?  Whats the best alternative:


A slightly more complex expression that turns a null value into an empty string

A script component

Two conditional splits

Two paths out of one condtional split

I went with the first option, here is the expression I came up with:


LEN(ISNULL(Ballot) ? "" : TRIM(Ballot)) > 0

View 3 Replies View Related

Conditional Formatting Expression - Evaluate First 3 Characters Of String As Numbers

Mar 2, 2012

I'm trying to put conditional formatting on a field, that behaves as follows:

The data in the field is varchar, and sample data is either:

NULL
3.0 :0
11.7 :1 (these are ratios of a sort)

I want to evaluate the first 3 characters of the string as numbers.

Example:
Mid(fieldvalue,1,3) = "3.0" or "11."

Any data that is greater than 1.99, I want to make the background dark red, anything else including nulls, zebra formatting. I have the following expression built so far and it appears to work, except when the value is null. If the value is null, it leaves the background color white.

This is the warning: [rsRuntimeErrorInExpression] The BackgroundColor expression for the text box "Asthma" contains an error: Input string was not in a correct format.

=iif(
isnothing(Fields!Asthma.Value)
,(IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))
,(iif(mid(Fields!Asthma.Value,1,3)>1.99
,"DarkRed"
,IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))))

My logic is, if the field is null, zebra format, if mid of the value is > 1.99, dark red, everything else zebra formatting. As I said, this seems to work except for nulls.

View 2 Replies View Related

Integration Services :: Conditional Split Expression For Boolean Values

May 22, 2015

I have a field 'IsActive' which is bit type either 1 or 0. And in my package, the conditional split must be set something like

IsActive==0

And throws exception that condition evaluated as NULL where Boolean was expected. How i can make this work?

View 8 Replies View Related

Order By Quirk

Mar 22, 2005

Hello all,

I have a varchar field in my table that stores our project numbers as such: 05-001-1, 05-001-2, 05-001-3, 05-002-1, 05-003-1, 05-003-2, etc. I am doing an order by desc on this column and it is returning something like this:

05-001-1
05-001-10
05-001-11
05-001-12
05-001-2
05-001-3
05-001-4
05-001-5
05-001-6
05-001-7
05-001-8
05-001-9

So it sorts all the 1's first, then the 2's then the 3's etc. If the numbers went up to 20 then 05-001-20 would appear after 05-001-2 and before 05-001-3. How can I get it to sort numberically i.e. 1-12?

Thanks for any help.
mkm

View 8 Replies View Related

Conditional Subscription / Conditional Execution Of Report

Mar 7, 2008



Hello everyone,

Is there a way in order to execute a subscribed report based on a certain criteria?

For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.

My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.


Any help or suggestions will be much appreciated.

Thanks,
Larry

View 6 Replies View Related

URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But

Feb 8, 2007

Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1


"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"

Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works

However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur

(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)

View 4 Replies View Related

Reporting Services :: Running Value Expression Within Lookup Expression In SSRS?

Oct 28, 2015

I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".

I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.

Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.

This is the lookup expression that I added for the 2nd column.

=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").

I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.

(processing): (SortExpression ++ m_context.ExpressionType)

View 7 Replies View Related

Conditional Formatting - Not So Conditional??

Dec 15, 2006

I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.

=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")

I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.

Is it because this is a matrix report? What am I doing wrong?

Thanks in advance . . .

View 4 Replies View Related

Conditional FROM/WHERE

Apr 13, 2001

SQL 7

Hi All !!

I want to know how I can create conditional FROM WHERE clauses like below ..

SELECT X,X,X
FROM
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
WHERE
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END

Thoughts ?

Thanks

View 4 Replies View Related

Conditional When

Jun 23, 2008

Is anything similar to this possible without writing whole query in every begin block or without dynamic sql:

proc(@arg1 bit)

select * from table where x=y
if @arg1 = 1 begin
and when a=b
end
order by date

View 6 Replies View Related

Conditional -- ?

Jul 23, 2005

What does "conditional" mean as a command? sp_who2 reports this forsome sessions, along with "insert" etc.Thanks,Jim Geissman

View 2 Replies View Related

Conditional Sum?

Apr 23, 2007

Hi,



Is it possible to have a conditional sum based on an item type existance in a set of values?



Example if i have the following set:

A

A

A

A

B



I just wanna sum B else if B doens't exist sum A





Best Regards,

Luis Simoes

View 7 Replies View Related

Conditional Inner Join?

Jan 30, 2007

Im faced with the following design issue..
on my site there are different profiles: a city profile, a restaurant profile and a user profile.
in my DB:City profiles are stored in tbCities cityID int PK shortname nvarchar(50) forumID int FK (...)
Restaurant profiles are stored in tbRests restID int PK shortname nvarchar(50) forumID int FK (...)
User profiles are stored in tbUsers userID int PK shortname nvarchar(50) forumID int FK (...)
as you can see a single ID value (for CityID,restID or userid) might occur in multiple tables (e.g. ID 12 may exist in tbRests and in tbUsers)Each of these profile owners can start a forum on their profile.
forumID in each of the above tables is a FK to the PK in tbForums:forumID  intforumname nvarchar(50) (...)
Now imagine the following:
a site visitor searches ALL forums...say he finds the following forums:ForumID Forumname1 you opinion on politics2 is there life in space?3 who should be the next president of the USA?
a user may want to click on the forum name to go to the profile the forum belongs to.And then there's a problem, because I dont know in which table I should look for the forum ID...OR I would have to scan all tables (tbCities,tbRests and tbUsers) for that specific forumid,which is time-consuming and I dont want that!
so if a user would click on forumID 2 (is there life in space?)
I want to do a conditional inner join for the tablecontainingforumID (which may be tbCities,tbRests or tbUsers)
select tablecontainingforumID.shortname FROM tablecontainingforumID tINNER JOIN tbForums f ON t.ForumID=f.ForumIDwhere f.ForumID=2
I hope my problem is clear..any suggestions are welcome (im even willing to change my DB design if that would increase effectivity)
 

View 6 Replies View Related

If Conditional Problem In T-Sql

Jan 31, 2007

I encounter a T-Sql problem related to if conditional processing:The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.Could anyone offer some advice?  IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0) BeginINSERT INTO dbo.DefaultsSELECT FinancialPlannerID,ReportTitleFROM dbo.ReportPreferencesendGO 

View 6 Replies View Related

Conditional Where Statement

Jul 23, 2007

I have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!

View 6 Replies View Related

SQL Conditional WHERE Statement

Feb 4, 2008

Hi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement?

Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test]

(
@startRef int,
@endRef int
)

AS

BEGIN

SELECT Count(*) FROM myTable
WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef)))

END
RETURN
 

View 2 Replies View Related

Conditional SQL Question

Feb 23, 2008

I have an SqlDataSource that uses a value from the query string in the WHERE clause of the sql statement.
The sql is something like this:
SELECT * FROM myTable WHERE myfield = @myfield
and I have the QueryStringParameter setup like this:
<asp:QueryStringParameter Name="myfield" QueryStringField="myfield" />
What I need is for the sql statement to return all records in the case that "myfield" is not defined in the query string.
How would I implement this?
Thanks,
Joshua Foulk

View 6 Replies View Related

Conditional Update

Feb 21, 2006

Hello all, my update statement works as expected, but lacks some conditional logic. How can I change the statement to not decrement qtyonhand if the quantity is 0? Additionally, I would need to return to the calling application something that would allow me to populate a label with a message to the user.. How can that be accomplished?
Here is my sproc:CREATE PROCEDURE [webuser].[cssp_removeItem]
@lblID int
AS
Update cstb_inventoryset qtyonhand = qtyonhand -1where Id = @lblIDGO
Here is my app code:
Try
Dim cmd As SqlCommand = cn.CreateCommand
cmd = New SqlCommand("cssp_removeItem", cn)
cmd.CommandType = CommandType.StoredProcedure
With cmd
cmd.Parameters.Add("@lblId", SqlDbType.Int).Value = lblId.Text
End With
If Not cn.State = ConnectionState.Open Then
cn.Open()
End If
cmd.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
If Not cn.State = ConnectionState.Closed Then
cn.Close()
cn = Nothing
End If
 

View 6 Replies View Related

Conditional WHERE Clause

May 8, 2006

Hi,
[SQL 2005 Express]
I would like a DropDownList to be populated differently depending on the selected value in a FormView.
If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company.  Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.
Here is the SQL for case 1:
SELECT    AdviserID,    AdviserName FROM    Advisers WHERE    (CompanyID = @CompanyID).
Here's the SQL for case 2:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    (CompanyID = @CompanyID) AND    (TypeID = 3).
Here's my best (failed) attempt to get what I want:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    IF @CompanyID = 2 THEN      BEGIN         (CompanyID = @CompanyID)      END   ELSE      BEGIN         (CompanyID = @CompanyID) AND          (TypeID = 3)      END
I've also tried:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE @CompanyID       WHEN 2 THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND          (TypeID = 3)   END
and 
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND (TypeID = 3)   END
I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.
Thanks very much.
Regards
Gary

View 7 Replies View Related

Conditional Foreign Key - Can It Be Done???

May 17, 2001

Hello Folks,

Well I've hit the wall on this one:

I'm wondering if one can designate a conditional foreign key that relates to one of many different tables depending on the "type" column in the foreign key's. My goal is to come up with some SQL code that will allow for this in a Create table statement. (By the way I'm using MS SQL-7 but I'm wondering if this can be done in general.)

I have two simple cases below that show illustrate what I'm trying to do:
Thanks in advance, -JerryZZ


---------------------------------------------------------
Case 1: The foreign key relation is to a primary key

Table: Invoices
-Fields:
---InvoiceID ..... (primary key)
---BilleeID ...... (fkey to Manfacturers.ManfID IF BilleeType=M)
...................(fkey to Distibutors.DistID IF BilleeType=D)
---BilleeType .....(constraint = M or D)

Table: Manufacturers
-Fields:
---ManfID ........ (primary key)
---CompanyName.. (not null)

Table: Distributors
-Fields:
---DistID ........ (primary key)
---CompanyName .. (not null)



---------------------------------------------------------
Case 2: The foreign key relation is to a unique "not null" non-primary key

Table: InvoicesEmail
-Fields:
---InvoiceID ..... (primary key)
---EmailAddress .. (fkey to Manfacturers.EmailAddress IF BilleeType=M)
...................(fkey to Distibutors.EmailAddress IF BilleeType=D)
---BilleeType .....(constraint = M or D)

Table: Manufacturers
-Fields:
---ManfID ........ (primary key)
---EmailAddress .. (unique, not null)

Table: Distributors
-Fields:
---DistID ........ (primary key)
---EmailAddress .. (unique, not null)

---------------End-O-Message-------------------------------------

View 2 Replies View Related

Conditional Delete

Nov 15, 2000

Hi, ladies and gentelmen!
Can you help me with following trouble:
I got a table (let it be called SomeTable) in which there's one nullable field (let's call it SomeField) among many others. PRIMARY KEY for SomeTable is of INT IDENTITY type.
Business rules are following: only records that have SomeField IS NULL can be deleted, so I need to perform conditional delete (for cases like DELETE SomeTable). I don't like idea about using SP here, so I tried to solve the task by means of trigger. However, when DELETE clause is used within a transaction and there're records affected by the trigger that don't match business rule, trigger uses ROLLBACK TRAN and then deletes only matching rows from target table. Everything works fine with our SomeTable, but not so fine with the transaction, because due to ROLLBACK TRAN statement in trigger body this transaction is rolled back (as it is described in documentation). But I don't wanna ALL my transaction rolled back! So, is there in SQL Server 7.0 any way to rollback only changes that caused trigger to fire? Something like ROLLBACK TRIGGER?

View 1 Replies View Related

Help With IF THEN - Conditional ORDER BY

Dec 2, 1999

I would like to write the following (pseudo) stored procedure, but am having problem with the syntax:

Create Procedure spSort
@IDContract nvarchar(10)
@SortOrder int

SELECT * FROM Contracts
WHERE IDContract = @IDContract
IF @SortOrder = 0
BEGIN
ORDER BY ContractDate
END
IF @SortOrder = 1 THEN
BEGIN
ORDER BY ShippingPeriod
END

The problem is in conditionally setting the sort order. The actual sp is quite complex and I don't really want to have to use two procedures (one for ordering by ShippingPeriod and one for ordering by ContractDate

Any suggestions please?

Many thanks
Jeremy Holt

View 2 Replies View Related

Conditional Insert

Oct 29, 2006

Hi,

Originally had 2 tables, fullsource and ssotarget. I did the following extract on fullsource due to its irregular schema and inserted into ssotarget:

INSERT SSOTARGET (pin, address1, address2, address3, MemberNo, Tel1, Tel2, Tel3, Tel4, DOB, Email, IDNumber, Title, Initials, Firstname, Surname, STATUS)

SELECT
PIN,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 1' THEN VALUE ELSE NULL END) AS address1,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 2' THEN VALUE ELSE NULL END) AS address2,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 3' THEN VALUE ELSE NULL END) AS address3,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN VALUE ELSE NULL END) AS MemberNo,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Preferred method of contact*' THEN VALUE ELSE NULL END) AS Tel1,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Tel number (o/h) e.g. 011 2690000' THEN VALUE ELSE NULL END) AS Tel2,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Mobile number e.g. 0821234567' THEN VALUE ELSE NULL END) AS Tel3,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Fax number e.g. 011 2691000' THEN VALUE ELSE NULL END) AS Tel4,
MAX(CASE WHEN HEADER = 'Date Of Birth' AND PROPERTY = 'eg. 04 Jan 1965' THEN VALUE ELSE NULL END) AS DOB,
MAX(CASE WHEN HEADER = 'Email address' AND PROPERTY = 'Email Address' THEN VALUE ELSE NULL END) AS Email,
MAX(CASE WHEN HEADER = 'ID Number' AND PROPERTY = 'ID Number' THEN VALUE ELSE NULL END) AS IDNumber,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Title' THEN VALUE ELSE NULL END) AS Title,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Initials' THEN VALUE ELSE NULL END) AS Initials,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Firstname' THEN VALUE ELSE NULL END) AS Firstname,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Surname' THEN VALUE ELSE NULL END) AS Surname,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN STATUS ELSE NULL END) AS STATUS

FROM

FULLSOURCE

GROUP BY PIN


Now how can I reverse this to insert all the values back to tbl FULLSOURCE from maybe an updated SSOTARGET Tbl. FULLSOURCE looks like this:

SELECT [PIN]
,[SYSTEM]
,[Header]
,[Property]
,[Value]
,[Status]
,[ID_Header]
,[ID_Property]
FROM [fullsource]

View 1 Replies View Related

Conditional Select

Nov 8, 2007

I am trying to write query that will select calls that only show up when the resolution returned from the case in the sub query is 'y'. When I do the group by some calls will show up twice because of a null and a 'y' resolution return. I want to only select a call if there is a 'y' that shows for all resolutions associated with that call. If there are any null resolutions for the call I don't want it to show up in my returned values. This is what I have so far, but I can't figure out how to make it drop both instances of the callID if one instance is null.


Code:

SELECT C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution, CASE C.CallID WHEN T1.Resolution IS NOT NULL THEN 'Y' ELSE 'N' END
FROM HEAT.dbo.CallLog C,
(SELECT CallID, CASE WHEN A1.Resolution = '' THEN 'Y' ELSE NULL END AS Resolution
FROM HEAT.dbo.Asgnmnt A1) T1, HEAT.dbo.Profile P
WHERE C.CallID = T1.CallID AND C.CustID = P.CustID AND (P.PrimarySupportGroupID = 'ATS') AND (P.PrimaryTeamName = 'Beta')
GROUP BY C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution



If anyone could help me out with this or at least give me a little information and point me in the right direction I would really appreciate it.

Thank you
Court

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved