Tuesday, May 28, 2013

[T-SQL] WHERE clause

[T-SQL] WHERE clause

WHERE clause

Posted: 28 May 2013 12:19 AM PDT

I am trying to write a where clause that tests for a string which may or may not contain a specific character. Specifically, I need to check if the string is '%', and if not I need to search for a match to the string that may or may not contain a /. The format of the string will always be AAA(space OR slash)AAAA. I was thinking something like the following, but that obviously doesn't work. Any suggestions?where DIRECTED LIKEcase when @directed = '%' THEN '%'when @directed != '%' THEN (('%' + @directed + '%') OR ('%' + REPLACE(@directed ,'','/') + '%'))end

Text Search for age-group related words

Posted: 28 May 2013 12:20 AM PDT

Hi Everyone,I have an app that creates groups and designates members based on their age. Now I would like to be able to "suggest" a list of possible members by using the name/description of the group.For example, if the group name was "Essex Town FC U8", i'd like to get back a table of results as follows:[code="other"]Operator Value--------------------< 8[/code]I'd also be able to interrogate the description field too, for example: "The group contains members for the Essex Town FC under 12's and under 13's football team". From this, I'd like to retrieve the following resultset.[code="other"]Operator Value--------------------< 12< 13[/code]Once I've got this, I can then perform my own search on the users' ages to get back the "possible" selection.I'll be completely honest, this is a bit of a challenge for me, but having asked some challenging questions before and received some amazing feedback, I thought I'd at least ask the question here first! :-DThanks all,Kev.

Most Efficient Insert query

Posted: 28 May 2013 12:16 AM PDT

Good Morning EveryoneI hope that everyone had a very nice weekend.I am inserting rows from one database to another. The tables that I am using as the source, has an Identity column that is related and has foreign key constraints to each table that I want to use. I have the parent table in the destination filled with data. What would be the best and most efficient way to insert the other data based on the Identity column of the parent table? I would rather not use the IN clause. I am thinking that a JOIN would be the most efficient.I am using SELECT * INTO <DestinationDatabase>dbo.<TableName>FROM <SourceDatabase>.dbo.<TableName>so that SQL Server will create the tables for me.I appreciate any and all comments, assistance and suggestions.Thank you in advanceAndrew SQLDBA

I want to validate where each parent intermediary is also a parent to itself?

Posted: 27 May 2013 05:08 PM PDT

Hi All', I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any.--===== Create the test table CREATE TABLE Intermediary ( IntermediaryPK INT , IntermediaryID NVARCHAR(20), IntermediaryName NVARCHAR(200), IntermediaryTypeID NVARCHAR(1),, ParentIntermediaryID NVARCHAR(20), IntermediaryTypePK TINYINT, ParentIntermediaryPK INT GrandParentIntermediaryPK INT GrandParentIntermediaryID NVARCHAR(20) )== Insert the test data into the test tableINSERT INTO Intermediary (IntermediaryPK, IntermediaryID, IntermediaryName, IntermediaryTypeID, ParentIntermediaryID, IntermediaryTypePK, ParentIntermediaryPK, GrandParentIntermediaryPK, GrandParentIntermediaryID) SELECT '552',200244584261,'A''1''201841943403''1' '6459''6459''201841943403'UNION ALL SELECT '553','200983879092','B''1''200707895681''1' '6462''6459''200707895681'UNION ALL SELECT '554','200925413387','C''1''200707895681''1' '6462''6462''200707895681'UNION ALL SELECT '555','200472620781','D''1''200707895681''1''6462' '6462''200707895681'UNION ALL SELECT '556','201902784325','E''1''200707895681''1''6462' '6462''200707895681'UNION ALL SELECT '557','201874832909','F''1''200707895681''1''566' '6462''200707895681'UNION ALL SELECT '558','201264024229','G''1''200707895681''1' '566''6462''200707895681'UNION ALL SELECT '559','201725870455','H''1''201062751762''1''566''6462''200707895681'

OFAC Compliance queries

Posted: 06 Jan 2010 12:23 AM PST

Hi,I am trying to implement OFAC Compliance check using sql queries to search for data in our database. If anybody has done stuff similar to this, can you please advise how u did it?Thanks,sridhar.

need help with my SQL

Posted: 27 May 2013 11:47 AM PDT

greetings all,I'm trying to return a row of data for each licence and want that row to include the phone number columns for all numbers associated with that row using joins. However I seem to be getting a row per phone number. I have to use left joins as a phone number and email address might not exist for that licence. See query below and then a sample of the result. I'd appreciate some help. Thanks. Query: SELECT distinct licdet.LicNum ,licdet.CustomerId ,licdet.customerholder ,licdet.issuedate ,licdet.expirydate ,case licdet.LicStatusDesc when 'current' then 'C' else 'E' end as LicStatusDesc ,licdet.HolderAddress1 ,licdet.HolderAddress2 ,licdet.holdersuburb ,licdet.HolderState ,licdet.HolderPostcode ,CASE CONTACT_USAGE_ID WHEN '54076' THEN tel.formatted_number else '' END AS business ,CASE CONTACT_USAGE_ID WHEN '54075' THEN tel.formatted_number else '' END AS Mobile ,CASE CONTACT_USAGE_ID WHEN '54078' THEN tel.formatted_number else '' END AS Fax ,case CONTACT_USAGE_ID WHEN '54081' Then inter.ADDRESS else '' end as Internet FROM [Navigate38].[lt_dba].[dfv_pr_lic_detail] licdet inner join [Navigate38].[lt_dba].[LS_CLIENT] cli on licdet.CustomerId = cli.CLIENT_REF and licdet.LicStatusDesc <> 'cancelled' inner join [Navigate38].[lt_dba].[LS_CONTACT_METHOD] conmet on cli.CLIENT_ID = conmet.CLIENT_ID and CONTACT_USAGE_ID in (54076, 54075, 54078, 54081) left join [Navigate38].[lt_dba].[LS_TELEPHONE] tel on conmet.telephone_id = tel.TELEPHONE_ID left join [Navigate38].[lt_dba].[LS_INTERNET] inter on conmet.INTERNET_ID = inter.INTERNET_ID order by licdet.licnumresult example:LicNum CustomerId customerholder issuedate expirydate LicStatusDesc HolderAddress1 HolderAddress2 holdersuburb HolderState HolderPostcode business Mobile Fax InternetCBL1013 3489 MALEMBO PTY LTD 2013-01-01 00:00:00 2013-12-31 00:00:00 C GRD FLOOR, 14 FORESHORE DRIVE GERALDTON WA 6530 PLOW@WN.COM.AUCBL1013 3489 MALEMBO PTY LTD 2013-01-01 00:00:00 2013-12-31 00:00:00 C GRD FLOOR, 14 FORESHORE DRIVE GERALDTON WA 6530 08 9921 8121 CBL1013 3489 MALEMBO PTY LTD 2013-01-01 00:00:00 2013-12-31 00:00:00 C GRD FLOOR, 14 FORESHORE DRIVE GERALDTON WA 6530 0418 647 750 CBL1013 3489 MALEMBO PTY LTD 2013-01-01 00:00:00 2013-12-31 00:00:00 C GRD FLOOR, 14 FORESHORE DRIVE GERALDTON WA 6530 08 9921 1221
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog