Parameters/variables

Avatar
Steve Clark
  • updated
  • Completed
I'm using fly-Speed SQL Query tool Version 2.10.1.8 I declare a variable, set the value and use the variable in the query but nothing happens. What do I do to make it work because its not behaving like a normal sql script. (the connection is to a MS SQL database)

Thanks, Steve
Replies 10
Avatar
0
Anonymous

Select *
From registrations
Where
registrations.lastname Like :lastname And
registrations.date_reg Between :date_start And :date_end


Avatar
0
Kelly

Could you show an example where the Extended information displays something other than [Unknown]?

Avatar
0
Anonymous

There is no setup for this information. This information is taken from the query. It's shown to let the user know how this parameter is used in the query.

Avatar
0
Kelly

Where is the Extended information setup for a query parameter?

Avatar
0
Anonymous

Please replace date values with parameters as shown on the screenshot below. Then click the Execute button. A dialog prompt for parameter values will pop up. Enter values and click the Ok button to get query results.

Avatar
0
Steve Clark
I want to use parameters on this line of code to run the full query for different date ranges:

MarketingEmailJobContacts.DateTimeSent Between '20140403' And '20140405'

Change those constants to a parameter like :First_email_Date etc this query does what I want it to do but when I try to use parameters it throws an error.



full SQL Statement here:

Select
'Control Group Summary' As 'Title',
'April 3rd and 4th' as 'Date Sent',
Count (Distinct (Emailresults.contactid))As 'Emails sent',
Sum(Case When Emailresults.[# of Opened emails] = 0 Then 0 Else 1
End) As '# of Opened emails',
Sum(Case When Emailresults.[# of Opened emails] = 0 Then 0 Else 1
End)
/ Cast(Count (Distinct (Emailresults.contactid)) as Decimal (10,4))*100 AS 'OPEN RATE',
Sum(EmailResults.[# of Unsubscribes])as 'unsub',
Sum(EmailResults.[# of Total Clicks]) as 'clicks',
sum(CASE When EmailResults.[# of Unique Clicks] = 0 Then 0 Else 1 End) as 'unique clicks',
sum(CASE When EmailResults.[# of Unique Clicks] = 0 Then 0 Else 1 End) /
sum (Cast(EmailResults.[# of Emails] as Decimal (10,5)))*100 AS 'CTR',
sum(CASE When EmailResults.[# of Unique Clicks] = 0 Then 0 Else 1 End) /
Cast(Sum(Case When Emailresults.[# of Opened emails] = 0 Then 0 Else 1
End) as Decimal (10,5))*100 AS 'CTOR'
From
(Select
MarketingEmailJobContacts.contactId,
Count(Distinct (MarketingEmailJobContacts.ContactId)) As '# of Emails',
Sum(Case When MarketingEmailJobContacts.OpenedEmail = 'Yes' Then 1 Else 0
End) As '# of Opened emails',
Count(MarketingEmailJobContacts.DateUnsubscribed) As '# of Unsubscribes',
Sum(MarketingEmailJobContacts.NumberOfTotalClicks) As '# of Total Clicks',
Sum(MarketingEmailJobContacts.NumberOfUniqueClicks) As '# of Unique Clicks'
From
MarketingEmailJobContacts
Where
MarketingEmailJobContacts.DateTimeSent Between '20140403' And '20140405' AND
MarketingEmailJobContacts.AccountNumber In ('171561-0', '173265-0',
'171251-0')
Group By
MarketingEmailJobContacts.ContactId
) As EmailResults
Avatar
0
Anonymous
Please provide the sample query.
Avatar
0
Steve Clark
Is there any documentation on usage of or restrictions of the parameter notation. I can't seem to find very much. Thanks
Avatar
0
Steve Clark
Thanks for the suggestion. I used this method within a derived table and the dialog box did not open for me to assign the parameter value and I received an error message stating that the parameter was not properly defined. Is that because I was trying to use it within a derived table? 
Avatar
0
Anonymous
Declaring variables does not work in FlySpeed SQL Query. The tool executes single queries, not SQL scripts. I recommend you to use query parameters instead of variables. For example, on executing the following statements you will be prompted for parameter values:

SELECT * FROM Customers WHERE CustomerName Like :Name

SELECT * FROM Orders WHERE OrderDate >= :Date_Start and OrderDate <= :Date_End

Customer support portal powered by UserEcho