?
Solved

Please help with database question

Posted on 2011-10-19
22
Medium Priority
?
490 Views
Last Modified: 2012-05-12
HI,

I have a list of customers. Each customer has purchased tickets and these are presented as different ticket numbers. The ticket numbers represent the content for example: the promotion(11), the venue (22) and the time (33) will give a ticket number 112233. A customer can purchase more than one ticket therefore can have more than one ticket number. The file will be provided pipe delimited as

email | ticketnumber
jim@email.com | 112233,443322
alex@email.com | 223311, 112233, 334422

i.e. the ticketnumber part will be comma seperated and quantity can vary.

We will have another table already provided in our database which will represent what these numbers mean

ticket number | promotion | venue | time
112233 | free | cinema | 12:00
223311 | half_price | cinema | 13.00

This data will be coming into our email system and we would like to setup a table which holds all email addresses for people who will be going to the cinema venue

I assume I can use SQL but it is how to manage the cell which has a number of comma seperated data which confuses me.

thanks for your help
0
Comment
Question by:jecommera
  • 10
  • 9
20 Comments
 

Author Comment

by:jecommera
ID: 36995743
sorry example typo above ... of course the second part should something like:

442255 | half_price | cinema | 13.00

(cinema is 22)
0
 
LVL 28

Expert Comment

by:strickdd
ID: 36995772
This is a pretty inefficient way to handle this scenario, but a split User-Defined Function that returns a table is a pretty good option:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

Open in new window


However, if you parse the value and store it in a set of tables, you can perform simple joins on the data which is much more efficient when querying and a little more intensive when storing.

PersonTable
   ID int PK
   Email varchar(250) not null

PersonTicketTable
   ID int PK
   PersonID int --FK to PersonTable
   Promotion int
   Venue int
   Time int
   --Alternatively, combine last three columns
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36995789
What database product?  How to handle this will vary.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:jecommera
ID: 36995816
Hi,

thanks for this but I don't have access to script only Oracle SQL on the email system.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36995906
So you are saying this is only Oracle related?
0
 

Author Comment

by:jecommera
ID: 36995921
The Email system allows you to create new tables by selecting existing tables then the next screen asks you to enter "oracle" SQL to create the new table.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36995959
I'm trying to figure out the best Zones to have this question in.  To do this, I need to know what you are using.

If you are happy with the current zones then I'll leave it alone.
0
 

Author Comment

by:jecommera
ID: 36996014
I am using a SAAS email solution which provides the facility to enter SQL query in order to create a new table using the tables you already have
0
 

Author Comment

by:jecommera
ID: 36996219
hmm...

I found the following link:
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

And attempted the following SQL to normalize a table to work off:

uploaded an csv file with

IND
1
2
3
etc.

email_address, ticket_id

Email list : $A$
index_file: $B$

SELECT $A$.email_address_,    NullIf(SubString(','+$A$.TICKET_ID+ ',' ,$B$.IND,CharIndex(',' , ','+ $A$.TICKET_ID+ ',' , $B$.IND)-$B$.IND) , '') AS Ticket FROM $B$, $A$  WHERE $B$.IND <= Len(',' + $A$.TICKET_ID + ',') AND SubString(',' + $A$.TICKET_ID + ',' , $B$.IND-1, 1) = ','  AND CharIndex(',' , ',' + $A$.TICKET_ID + ',' , $B$.IND)-$B$.IND>0

I get the following error when I test the query:

ExplainPlan cannot be retrieved - java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36999533
>>I get the following error when I test the query:

The problem is that is SQL Server SQL in that article.

I'm not familiar with SASS Email which is why I added the additional Zones.  I can probably help with the Oracle tables/SQL to get you what you need but not sure how it will work with your app.

If you can translate your requirements into simple tables/data and expected results I'll try to come up with something but it will likely be specific to Oracle.
0
 

Author Comment

by:jecommera
ID: 37002210
Sorry OK - too much information I suppose :)

I have a table

email | ticketnumber
jim@email.com | 112233,443322
alex@email.com | 223311, 112233, 334422

Using SQL, I want to  a new table as:

email | ticketnumber
jim@email.com | 112233
jim@email.com | 443322
alex@email.com | 223311
alex@email.com | 112233
alex@email.com | 334422
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37002255
Look for a function called str2tbl.  There are many examples of it around.

Here's the first I found:
http://www.experts-exchange.com/Database/Oracle/Q_24126363.html
0
 

Author Comment

by:jecommera
ID: 37002281
The issue is I have placed this in the wrong zone.
You have answered my question correctly but I am not looking for Oracle function only SQL

I will raise new question rephrased correctly

thanks for your help and time
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37002337
I forgot you needed SASS Email SQL that emulates Oracle.

Let me see what I can find that is strictly SQL based.

do you know what version of Oracle SQL it supports?  Can I use Oracle XML SQL functions?
0
 

Author Comment

by:jecommera
ID: 37002386
It doesn't tell me. Here is an example of an SQL query I used before which has worked:

SELECT $A$.email_address_, $A$.ITEM from $A$ JOIN $B$ ON lower($A$.EMAIL_ADDRESS_) = lower($B$.EMAIL_ADDRESS_) WHERE $A$.ITEM = 'product' AND lower($B$.EMAIL_PERMISSION_STATUS_)='i' AND lower($B$.EMAIL_DELIVERABILITY_STATUS_)='d'

This was used to see what customers purchased a product
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37002484
I'm not sure you can do this without some Oracle specific options.

Do you have product support for SASS?  you might need to contact them.

I'll see what SQL tricks I might be able to come up with.
0
 

Author Comment

by:jecommera
ID: 37002506
What about the link I provided above ... it uses SQL in the same way as above. I think that my syntax in incorrect
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37002672
That uses SQL Server syntax and a TALLY table that has a list of numbers to use as a 'loop'.

Do you have this TALLY table to join against?

I can try to port the SQL Server syntax but I'm not a SQL Server person.
0
 

Author Comment

by:jecommera
ID: 37002702
Yes - the email system allows you to create your own table which I have done and named IND and contains numbers 1 to 8000 in a single column
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37002997
Here is what I have that works in straight Oracle.  It was tested using sqlplus and Oracle 10.2.0.1.

I had to use an inline view.  I hope it ports over OK.
drop table tab1 purge;
create table tab1(email varchar2(20), ticketnumber varchar2(30));

drop table ind purge;
create table ind(id number);

insert into ind (select level from dual connect by level <= 10);

insert into tab1 values('jim@email.com','112233,443322');
insert into tab1 values('alex@email.com','223311, 112233, 334422');
commit;

select id,email,myval from (
select id, email,
substr(',' || replace(ticketNumber,' ',''),
	instr(',' || replace(ticketNumber,' ','') || ',' ,',',1,id)+1,
	(instr(',' || replace(ticketNumber,' ','') || ',' ,',',1,id+1)-2) - instr(',' || replace(ticketNumber,' ','') ,',',1,id)+1) myval
from tab1, ind
)
where myval is not null
/

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question