I have this data:
clientcreditCardamount
1A10
1A20
1B40
1C5
1C10
1C20
I need to write a query (Oracle) that will return the clientID and the creditCard type with the highest spending :
In this case:
clientcreditCard
1B
Does anyone have any strings or links I can use to help with creating a mod-10 stored proc?
I am really stuck on step 1, which is flipping the numbers (ex 1234 -> 4321)..
Any information pretaining to MOD-10 and MSSQL would be MUCH appriciated..
Is there any tool available that can encrypt Credit card numbers or other fields stored in the database? For example, when a customer enters his credit card number or his password on the site, is there a way by which the Front end can encrpt the field before storing it in the database and decrypt it when it the customer requests it, through the application?
Thank you in advance
Praveena
Hi ,
we have CreditCardRange table
create table CreditCardRange(
ccr_CudtomerID int,
ccr_CardRangeFrom char(16),
ccr_CardRangeTo char(16)
)
table Orders
ord_id int
ord_CudtomerID int,
ord_CardNo char(16)
Example values in table CreditCardRange
1 , 5000 0000 2000 1234 , 5000 0030 3000 1356
Example values in table Orders
1, 1, 5000 0010 2000 2333
Does sql provide any magic finction to help in following select
select T1.*
from Orders T1
join CreditCardRange T2
on T2.ccr_CudtomerID = t1.ord_CudtomerID
and
SQLMagicFunction(T1.ord_CardNo)
between
SQLMagicFunction(T2.ccr_CardRangeFrom )
and
SQLMagicFunction(T2.ccr_CardRangeTo )
Hi friends,
I am new to SQL developement ( am .Net developer). I need to implement a stored proc in sql 2005 on CC expiration task.
I have CC exiration date.
By using expiration date (like '01/08'),
Conditions are:
1. If CC expiring within 31 days. Stored proc will write the Customer data in EmailNotification table with message type =1. (Customer will not get second notification until 14 days before expiring)
2. If CC expiring within 14 days. Stored proc will write the customer data in EmailNotification table with message type =2. (Customer will not get second notification until 11 days before expiring)
3. If CC expiring within 11 days. Stored proc will write the customer data in EmailNotification table with message type =3. (Customer will not get second notification until 2 days before expiring)
4. If CC expiring within 2 days. Stored proc will write the customer data in EmailNotification table with message type and update orderStatusID to On Hold CC Expired in order table.
My Tables are
1.
TABLE [dbo].[Order](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[CustomerCode] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateOrdered] [datetime] NOT NULL,
[OrderTypeID] [int] NULL,
[OrderStatusID] [int] NULL,
[LastProcessDate] [datetime] NULL,
2. CREATE TABLE [dbo].[Payment](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[OrderPaymentType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PaymentType] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditCardNumber] [varbinary](max) NULL,
[Last4DigitsOfCreditCardNumber] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExpirationDate] [varbinary](max) NULL,
[CVV2] [varbinary](max) NULL,
[Amount] [numeric](18, 2) NOT NULL
3. CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerCode] AS ([dbo].[udf_FormatCode]('RS',[CustomerID])),
[Email] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiddleName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateModified] [datetime] NOT NULL,
[MembershipTypeID] [int] NULL
Please atleast basic structure of stored proc code. It will greatful to me.
Thanks in advance
srikanth
Hi friends,
i got an issue on my website regarding theft of cc details...sql db contains credit card details table ... it has been hacked by someone...we have analysed that latest cards are misused.. i have tried different scenarios(sql injection) ... what are all the key things have to check and implement .can any one advise please..
thanx in advance,
raj
I have typed a query by combining multiple tables to show sum of TotalReceivedAmount by MRN. It also have payment mode which is paid by cash or credit card. Now the query shows the sum for MRN combining all the payment done by credit card and cash. How am I supposed to show the amount paid by credit card and cash separately?
Here is the query :
SELECT Distinct dbo.CA_Payment.MRN, dbo.CA_Patient.FirstName, dbo.CA_PaymentModeMaster.Description AS PaymentMode,
CASE (dbo.CA_Payment.PaymentModeID) WHEN 2 THEN dbo.CA_Payment.CardNumber END AS CreditCardNumber, CASE (CA_Registration.PatientTypeID)
WHEN 1 THEN dbo.CA_PatientTypeMaster.Description WHEN 2 THEN dbo.CA_DebtorMaster.DebtorName END AS Debtor,
[Code ....
Hi,A co-worker is creating a vb.net web application which uses theCryptKeeper.dll to encrypt credit card numbers into a sql database.It is my job to pull these encrypted numbers and use the decryptmethod of this dll to show the credit card numbers on a ReportingServices report. I'm not even sure where this should be done. In thesql stored procedure or in the report itself? Any help would begreatly appreciated.Thanks in advance,Amy Bolden
View 1 Replies View Related
Hello Everyone
As part of UK compliance, we are going to start use an encrytption package (GNUPG), which will be running on a different Server to Reporting Services. A program has been written in C#, to handle requests (from mainframe and PC applications) to encrypt/decrypt credit card details. The encrypted card details are held in an NCR Teradata data warehouse, which Reporting Services can access. The encryption can be called using a http://server/folder/program?DATA=
In Reporting Services, a Data Source connection will be made, using an OLE DB .NET connection to the Teradata machine. SQL code will be included, which will link tables and retrieving the required fields for use in the report. I want to call the encryption program to decrypt the card number, so the actual card number can be printed in the body of the report.
There will probably be a requirement to write some parameter selection SQL as well, so a card nmuber can be entered as a report parameter field, which the SQL will have to either encrypt first, then process the SQL statement down the tables.
Has anybody use this technique before?
Thanks
Graham N.
Can anyone help me modify this sproc's Where clause or Joins to let (T) task records with a 0 to be returned?
If I enter a ClientID I want to return only those task records with the ClientID I entered (this works).
If I enter no ClientID I want to return all task records, even those with a 0 in the ClientID field.
ALTER PROCEDURE dbo.CMAdmin
@SID int
AS SELECT A.CompanyName, C.FirstName, C.LastName, C.ClientID,Convert(varchar(10), T.ActionDate, 10) AS [Action Date], T.Priority, T.Status, T.Subject, T.Note, T.CompletionDate, 10) AS Completed, T.DateEntered AS Entered, T.EnteredBy AS [Entered By],
CASE WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +' '+ C.[LastName] ELSE A.[CompanyName] END AS DRName
FROM tblClients C LEFT OUTER JOIN tblClientAddresses A ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T ON C.ClientID = T.ClientID
WHERE C.ClientID = Isnull(@SID,C.ClientID)
I have a fact, like this
DimCustomerKey, DimCardKey, Amount
1, 1, 20
2, 3, 30
1, 1, 40
3, 4, 50
2, 3, 50
I want a report like this
1, 1, 40
2, 3, 80
3, 4, 50
Logic :
- Each DimCustomerKey has only 1 DimCardKey
- If DimCardKey = 1, reported amount = max(amount)
- If DimcardKey other than 1, reported amount = sum(amount)
How can I implement this with MDX script ?
How can I implement this inside the CUBE (using scope or calculated member etc...)?
I have this query that returns the largest value in a row, but i need to know the column name that this value is in as well. any help in advance is appreciated
select clientID,
(select max(incomeValue)
from
(select earnings as incomeValue
union all
select unemployment
union all
select pensionRetirement
union all
select alimony
union all
select childSupport
union all
select dividendInterest
union all
select SS
union all
select SSI
union all
select SSDI
union all
select veteranBenefits
union all
select FIP
union all
select workStudy
union all
select other
union all
select otherHHWS) as income) as MaxIncomeValue
from tbl_income
Can someone tell me what the highest value (number) is for an int data type in sql server 2005?
View 2 Replies View Relatedstructuring a query to give me the CurrBal per CardNo.
I have table that looks like this:
CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-04 11:50 AM 79 0 6
2 2013-08-05 15:34 PM 52 100 41
2 2013-08-05 14:11 PM -48 0 40
3 2013-07-09 13:52 PM 49 0 12
3 2013-07-22 13:51 PM 11 0 14
1 2013-06-12 10:46 AM 63 0 7
3 2013-07-15 14:04 PM 33 0 13
2 2013-08-06 15:05 PM 39 0 42
2 2013-08-07 13:38 PM 30 0 43
I am looking to order this table by CardNo and then TransNo but i only want the query to display the record with the highest TransNo for each CardNo. In other words discard the records with the lower TransNo for each CardNo.
My desired result should hopefully look something like this:
CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-12 10:46 AM 63 0 7
2 2013-08-07 13:38 PM 30 0 43
3 2013-07-22 13:51 PM 11 0 14
I am using SQL 2012 Express but would also like this query to work in SQL 2005.
I have a stored proc which evaluates a table of 'tasks' to return the task with the highest priority for a given user - at the same time, once that task has been found - I want to allocate that user to the task.
I have created a stored proc that works...however I'm sure this requirement /pattern is common & I would like some best practice for this pattern of update and select within a transaction.
Here is some sample data:
use tempdb;
go
if OBJECT_ID('Tasks', 'U') is not null
drop table tasks;
go
create table tasks (
TaskId int identity primary key,
[Code] ....
And here's what my current stored proc looks like;
if OBJECT_ID('pGetNextTask', 'P') is not null
drop proc pGetNextTask;
go
create proc pGetNextTask (
@UserID char(10),
@TaskID int output
)
[Code] ....
Hi,i need an opinion on this,...i am using UNION ALL quite a bit in my queries..most of the time these are like 1 line select query,returning "different" type of stuff ..like say query 1 is returning one EmpName and query 2 is DOB and so on(keeping it simple for example)...i am combining it b/c i find it cumbersome to call 10 dfferent query to populate 1 structure(table etc) ,rather i wud call one and manipulate the result to fill up that one structure..Is that okay to combine different kind of return types in one (here first row of multi-select query result is empName,second row is DOB and so on...)?????thanks
View 5 Replies View RelatedI am calling a stored procedure and have an output parameter that I pass back to my asp page. The datatype of the value is nvarchar. When I run the page I get an error "Syntax error converting the nvarchar value 'OK' to a column of data type int". Can a return parameter be anything else other than an integer? Below is my code. Thanks for the help.
Parem = cmd.parameters.add("@Retval",SqlDbType.nvarchar,50)
Parem.Direction = ParameterDirection.Output
Hi everybody,VARCHAR has a higher precedence than CHAR. If you have a querySELECT COALESCE(c1,c2) FROM T1where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the returntype to be varchar(10) similiar to UNION. However it turns out tobe CHAR(10). Does anyone know why that is so?Thanks,Steffen
View 3 Replies View RelatedHi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.
I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)
The problem is that the function should return Integer and if the value is zero I need to return empty string.
It does't say any thing about returning the string but when it trying to use the format on a string it give me a worning and print #ERORR in the cell.
is there a solution for this?
Do you know the syntax to format the number in the function before the return?
(Sorry for the dumb question but I know C# not VB.net and there is no intelisance in that editor).
Thanks a lot!
How can I return a datetime type variable from a stored procedure in SQL Server to C# code?
View 4 Replies View RelatedHi,
I am trying to retrive some XML from my SQL server 2005 database. The XML is in a table called "myxml" and it is being stored a native XML. The Field type is "XML". The VB code returns nothing when I select SELECT xml. How ever if i SELECT NAME it displays the correct name from the name field. how do I return the XML to be viewed as XML throught the response.write?
Thanks!
Here is my Table definition:
Column Name
Data Type
Allow Nulls
Id
Int
xml
xml
Yes
NAME
Nvarchar(50)
yes
My XML that is stored in the XML field:
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE>
</CHAPTER>
</ROOT>
Here is my VB code:
Dim myconnection As SqlConnection
Dim mycommand As SqlCommand
myconnection = New SqlConnection()
myconnection.ConnectionString = _
ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString
Dim strSQL As String = "SELECT [xml] FROM myxml WHERE id = 76"
' create SQL command instance
mycommand = New SqlCommand(strSQL, myconnection)
Try
' open database connection
myconnection.Open()
'execute T-SQL command
Dim dr = mycommand.ExecuteReader()
While dr.read()
Response.Write("<p>" & dr(0).ToString() & "</p>")
End While
Response.Write("GOT IT")
Catch ex As Exception
Response.Write(ex.Message)
Response.Write(strSQL)
Response.End()
End Try
' Close connection
myconnection.Close()
Hello All,I have a scenario in which my stored procedure has to return fewvariables with their value and also the collection. Now in SQL their isno such as array, so the best is to return the table in place of.I am execting the stored procedure by having sql command in place.and created the various parameters(variables) those i need the valuesof and secondly wondering how should i be creating the parameter as atable returntype.Any help on this would be a million worth useful.I can excerpt the code if required.RegardsSandesh Kadam
View 1 Replies View RelatedHi all,
I am trying to figure out a way to analyze a stored procedure to deduce its output type.
A stored procedure can return values throught one of the following:
1) Using the Return keyword
2) Returning a recordset
3) Throught its Output parameters
4) A combination of 1, 2 or 3
Using the Information_Schema, you can access the SQL code of the stored procedure from, say, VB.Net. From there, I want to know what output type the stored procedure has.
A) for 1), I guess I can parse the code for the 'Return' keyword, but that wouldn't be efficient, for the 'Return' word could be inserted into string values...
B) for 2), it would be difficult to analyze the stored procedure's code to know FOR SURE whether a recordset is returned. You just can't parse for SELECT keywords in complex code...
C) 3) should be easy enough, getting the return parameters types from the Information_Schema.
So, is there an easy way that I missed for achieving this, some object in the .Net Framework that I could use to analyze SQL queries perhaps, or am I doomed to do it the hard way?
Thanks,
-Etienne
I am out of brain cells. I can not figure out how to upgrade instances of sql express to sql 2005 or even add an instance of sql 2005. I have installed/upgraded everything else (SSMS, etc.) to SQL 2005.
I am dazed and confused. I don't even know where to start in explaining what I have tried and done. I have 303 log files going back to 3/27/2007 from various attempts.
Where do I start in figuring this debacle out???
hi to all,
i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void)
i am creating sql function using SQL script
CREATE FUNCTION dbo.Amex ()
RETURNS NULL
AS CALLER
WITH EXECUTE
AS
EXTERNAL
NAME [AMEX].[UserDefinedFunctions].[Function1];
GO
but i am getting error.
there is something wrong in my sql syntax..can anybody help me?
when creating funtion having signature
public static void Function1()
i am getting error.......................
Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
can anybody help me?
thanks in advance
Chetan S. Raut
Hi,
I'm using quite odd combination of technology for my project, I'm using PHP and MSSQL 2000, at one certain page, I want to insert to a table where one of the column is TEXT data type, and I want to get the value from the TEXTAREA at the page, of course, with carriage return captured, I manage to get it done in MySQL, where it automatically store the carriage return keyed in by user at the TEXTAREA, while for MSSQL I no luck in finding solution for this, is there any settings I can set or I need to convert the carriage return keystroke to HTML tag at my PHP?
Thanks
Hi all,
I have a stored like this
CREATE PROCEDURE fts_insert_service_tasks( @status_no int output, @status_text nvarchar(255) output, @fts_employee char(100) , @fts_SCCode bigint, @fts_TaskDescription ntext) AS
declare @str_err nvarchar(255)
declare @err_no int
set @err_no=0
if ( isnumeric(@fts_SCCode) = 0 )
begin
set @str_err ='The fts Sccode is not a number'
set @status_text = @str_err
set @err_no=@err_no+1
return
end
if ( @fts_SCCode = '' )
begin
set @str_err ='The fts Sccode can not be null '
set @status_text = @str_err
set @err_no=@err_no+1
return
end
if ( len(@fts_employee) > 100)
begin
set @str_err ='Maximum Employee length allowed is 100 characters'
set @status_text = @str_err
set @err_no=@err_no+1
return
end
if ( @fts_employee = '' )
begin
set @str_err ='The employee fiedl can not be null'
set @status_text = @str_err
set @err_no=@err_no+1
return
end
if (@err_no=0)
begin
INSERT INTO fts_ServiceTasks (fts_employee , fts_Sccode, fts_taskdescription)
VALUES(@fts_employee, @fts_SCCode, @fts_taskdescription)
set @status_no=0
set @status_text = 'Add Service Task Ok'
end
else
begin
set @status_no=@err_no
set @status_text = @str_err
end
GO
and I called it from the ASP
<%function Add_Service_Task(fts_employee,fts_sccode, fts_TaskDescription)
cm.ActiveConnection = m_conn
cm.CommandType = 4
cm.CommandText = "fts_insert_service_tasks"
cm.Parameters.refresh
cm.Parameters(3).Value = fts_employee
cm.Parameters(4).Value = fts_sccode
cm.Parameters(5).Value = fts_TaskDescription
on error resume next
cm.Execute
if cm.Parameters(1)=0 then
exec_command=cm.Parameters(2).Value
else
call obj_utils.ErrMsg(cm.Parameters(2).Value,3000)
Response.End
end if
if err.number <> 0 then
call obj_utils.ErrMsg("System error at " & err.number & err.Description & ", please contact the administrator", 5000)
Response.End
end if
Add_Service_Task=exec_command
end function%>
I test with SQL 2k, Win2k3 OK
But with Win2k i got:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E30)
Type name is invalid.
/fmits/classes/cls_servicecall.asp, line 256
Please help me!
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function
GO
Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.
I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
The query in the Execute SQL Task is:
select 735.234, 2454.123
I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
I don't want to use a for loop enumerator since there is a single row returned.
I simply want to assign these two values to SSIS Double variables (double is the closest match)
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
Thanks for the help
how can i insert a carriage return when i update the field?
say i want to put the following inside a field:
firstline
secondline
how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'
the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines
i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'
but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline
thanks
I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).
For instance this works, and will return all applicable results.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](1000) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);
And this does not. It returns zero results what so ever.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](MAX) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);
Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?
Dear All,
I need to keep credit cardd details in a an SQL server table.
What's the best way to make sure data is secure? Is there any built in encryption that can be used so data is not stored as text?
Thanks
I have successfully finish Oracle OCA Exam. Currently I am Microsoft Certified Database administrator(MCDBA) I would like to know there is any way I could use OCA Credit to any Microsoft Exam. If any advice will be helpful.
Thanks
Faiz Farazi
www.databasetimes.net
Best learning center for Microsoft
http://www.lascomp.com