coverting conditional statements to sql

hi all

I am building an application where the user enters a conditional statement and then i want to perform some action.

Eg

user input > if customer.name = sandro then send email

My program will read the user input and then constructs an sql statement to retrieve the required info... if the sql returns data then a separate method is invoked.

My problem is to transform this statement into the appropriate SQL...since this need to be constructed at run time and for any type of user input..

The language i am using is java, however i dont expect any code...I was only wondering if someone has any ideas of how this can be done..

Thank you in anticipation.What language are you writing the application in ?|||Originally posted by rnealejr
What language are you writing the application in ?

I am using JAVA...|||What type of user input are you expecting ? - give some examples. What input from the user will the sql statements be based on ?|||Originally posted by rnealejr
What type of user input are you expecting ? - give some examples. What input from the user will the sql statements be based on ?

The users are expected to choose from a form which will display the available table attributes and the conjunctives AND OR and NOT.

eg of the typical input

If customer.name = "sandro" and department.name = "maintence" then send email.

what i need to do is to dynamically generate an sql that extracts the required info (based on the user input)......... if the dataset returned from the generated query contains data then this implies that the conditional part succeeds... and in turn i can invoke a method which sends the email...

Hope this is clear.........

Thank you for your interest

Sandro|||After the user makes the selections just do an executeQuery within java with the string that you create. So you might separate the sql statement into a select, from, where ... strings and combine these and pass to the executeQuery using the parameter selection by the user. By separating the parts of the sql statement you can create reusable components. Do you need to know the contents of the returned result set or just that something exists ? If it is the latter, just do a select count(*) to validate. I am a little unclear as to what your problem is, so I hope this answers your question.|||Originally posted by rnealejr
After the user makes the selections just do an executeQuery within java with the string that you create. So you might separate the sql statement into a select, from, where ... strings and combine these and pass to the executeQuery using the parameter selection by the user. By separating the parts of the sql statement you can create reusable components. Do you need to know the contents of the returned result set or just that something exists ? If it is the latter, just do a select count(*) to validate. I am a little unclear as to what your problem is, so I hope this answers your question.

Thank you very much for your advice...... it s pretty much similar to what i had in mind...

By any chance do u know how i can calculate the genereted query computational complexity? i.e. its order O()

Counting People in time

I'm trying to count the number of people that I have in a building at any
given hour from a table that basically looks like this...
CREATE TABLE InOutTimes
(
In_Date smalldatetime NOT NULL,
Out_Date datetime NOT NULL
)
go
INSERT InOutTimes VALUES ('8/1/01 08:00:00', '8/1/01 10:00:00')
INSERT InOutTimes VALUES ('8/1/01 08:00:00', '8/1/01 09:00:00')
INSERT InOutTimes VALUES ('8/1/01 13:00:00', '8/1/01 13:30:00')
INSERT InOutTimes VALUES ('8/1/01 17:00:00', '8/1/01 20:00:00')
INSERT InOutTimes VALUES ('8/2/01 09:00:00', '8/2/01 12:00:00')
INSERT InOutTimes VALUES ('8/2/01 10:00:00', '8/2/01 11:00:00')
INSERT InOutTimes VALUES ('8/2/01 16:00:00', '8/2/01 23:00:00')
INSERT InOutTimes VALUES ('8/2/01 17:00:00', '8/3/01 01:00:00')
I need something that will output something like this...
00 1
01 1
02 0
03 0
04 0
05 0
06 0
07 0
08 2
09 3
10 3
11 2
12 1
13 1
14 0
15 0
16 1
17 3
18 3
19 3
20 3
21 2
22 2
23 2
I have the 0 - 23 hour in memory that I can update, but my issue is when the
in/out times span a day. Is there a way I can do all this without a cursor
and case statements?
ANY help is appreciated.
Thanks
Scott
BTW - Wish me luck with the hurricane!!Hello, Scott
Try this:
CREATE TABLE #Numbers (N int PRIMARY KEY)
INSERT INTO #Numbers
SELECT DISTINCT number FROM master..spt_values
WHERE number BETWEEN 0 AND 23
SELECT N as TheHour, (
SELECT COUNT(*) FROM InOutTimes
WHERE DATEDIFF(d,In_Date,Out_Date)=0
AND N BETWEEN DATEPART(hour,In_Date) AND DATEPART(hour,Out_Date)
OR DATEDIFF(d,In_Date,Out_Date)=1
AND (N>=DATEPART(hour,In_Date) OR N<=DATEPART(hour,Out_Date))
OR DATEDIFF(d,In_Date,Out_Date)>1
) AS Cnt
FROM #Numbers ORDER BY N
Razvan
PS. Good luck with the hurricane! You are in Lafayette LA, right?|||That look like just what I needed! Awesome! Many thanks. I'll post in a
few days and let you know if it works out in the report :)
Yeah, Lafayette. They are expecting it to hit about 70 miles West of us so
I'm hauling to Atlanta in a bit. Have good one!
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1127407645.880484.6580@.g14g2000cwa.googlegroups.com...
> Hello, Scott
> Try this:
> CREATE TABLE #Numbers (N int PRIMARY KEY)
> INSERT INTO #Numbers
> SELECT DISTINCT number FROM master..spt_values
> WHERE number BETWEEN 0 AND 23
> SELECT N as TheHour, (
> SELECT COUNT(*) FROM InOutTimes
> WHERE DATEDIFF(d,In_Date,Out_Date)=0
> AND N BETWEEN DATEPART(hour,In_Date) AND DATEPART(hour,Out_Date)
> OR DATEDIFF(d,In_Date,Out_Date)=1
> AND (N>=DATEPART(hour,In_Date) OR N<=DATEPART(hour,Out_Date))
> OR DATEDIFF(d,In_Date,Out_Date)>1
> ) AS Cnt
> FROM #Numbers ORDER BY N
> Razvan
> PS. Good luck with the hurricane! You are in Lafayette LA, right?
>

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle
my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of custome
rs and number of different resources.
When the end user is creating a pivot table with Region en time on the axis,
the count of resources should reflect on the crossings. And I really don't
know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please helpF.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/d...r />
inct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tack
le my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of custo
mers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axi
s, the count of resources should reflect on the crossings. And I really don'
t know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of customers and number of different resources.
When the end user is creating a pivot table with Region en time on the axis, the count of resources should reflect on the crossings. And I really don't know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please help
F.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/de.../distinct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tackle my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of customers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axis, the count of resources should reflect on the crossings. And I really don't know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>