Solved

Posted on 2011-10-19
489 Views
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.

0
Question by:jecommera

Author Comment

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

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
``````

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 76

Expert Comment

What database product?  How to handle this will vary.
0

Author Comment

Hi,

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

LVL 76

Expert Comment

So you are saying this is only Oracle related?
0

Author Comment

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 76

Expert Comment

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

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

hmm...

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

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

IND
1
2
3
etc.

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 76

Expert Comment

>>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

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 76

Expert Comment

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

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 76

Expert Comment

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

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 76

Expert Comment

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

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 76

Expert Comment

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

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 76

Accepted Solution

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
/
``````
0

## Featured Post

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.