SteveG
Posts: 56
|
| Posted: 03/30/2005, 7:25 AM |
|
I'm trying to use a custom mySQL statement as a data source. Everything works okay, except that the Rebuild button is greyed out/deactivated, and so it's not very easy to reset all the sources for the grid controls because ccs is not reading the columns. Is there something wrong with the characters (tabs, breaks) in the sql statement, or is there something else I can try?
Thanks for all the help in advance, here is my sql statement:
SELECT
subscriptions.Subscription_Id AS Subscription_Id,
subscriptions.Creator AS Receiver,
CONCAT_WS(' ',subscribers.First_Name,subscribers.Middle_Name,subscribers.Last_Name) AS subscribers_Full_Name,
subscribers.Email_Address AS subscribers_Email_Address,
discussions.Discussion_Id AS Subscription_Type_Record_Id,
Concat(
"New post to the ",
discussions.Subject,
" discussion."
) AS Subject,
Concat(
"At ",
DATE_FORMAT(posts.Created_On,
"%a %b %D, %h:%i %p"),
" ",
if( posts.Post_Anonymously = 1, "Anonymous", participants.UserName),
" said:\n\n", posts.Content
) AS Message,
Concat(
" (link: http://FairChoice.org/Vote_Sizing/Discussions.php?subjt...cussionid=";,
discussions.Discussion_Id,
")"
) AS Message_Footer
FROM (((
subscriptions INNER JOIN
posts ON discussions.Discussion_Id = subscriptions.Discussion_Id) INNER JOIN
members subscribers ON subscribers.Member_Id = subscriptions.Creator) INNER JOIN
members participants ON posts.Creator = participants.Member_Id) INNER JOIN
discussions ON discussions.Discussion_Id = posts.Discussion_Id
WHERE
subscriptions.Frenquency_Id = {Frequency_Id}
AND subscriptions.Notification_Id < {Notification_Id}
AND posts.Created_On >= '{Last_Daily_Message_Subscription_Delivery}'
ORDER BY
Subject, posts.Created_On desc
- Steve
|
 |
 |
Nicole
Posts: 586
|
| Posted: 03/31/2005, 12:25 AM |
|
Steve,
What Rebuild button do you mean? I just cannot find it in CCS...
About your query: have you tested it with default test values of WHERE parameter against MySQL? Does it return the data you need? Also I recommend that you test it from CCS too – open Data Source dialog of a form and click on Review data button. Do you get the valid recordset there?
If you cannot select fields from controls Control Source list it indicates that sql query may not be executed because of an error.
_________________
Regards,
Nicole |
|
|
|
DonB
|
| Posted: 03/31/2005, 6:02 PM |
|
Once you switch from Table to SQL for the data source, CCS can't parse the
data source to give you the list of columns anymore. Well, it could in many
cases, but I suspect they took the conservative approach and disabled it.
There might be any sort of complexity in the query you'd build by hand and
that would make it tough to guarantee that parsing it works 100% of the
time.
--
DonB
http://www.gotodon.com/ccbth
"SteveG" <SteveG@forum.codecharge> wrote in message
news:2424ac4db11b23@news.codecharge.com...
> I'm trying to use a custom mySQL statement as a data source. Everything
works
> okay, except that the Rebuild button is greyed out/deactivated, and so
it's not
> very easy to reset all the sources for the grid controls because ccs is
not
> reading the columns. Is there something wrong with the characters (tabs,
> breaks) in the sql statement, or is there something else I can try?
>
> Thanks for all the help in advance, here is my sql statement:
>
> SELECT
> subscriptions.Subscription_Id AS Subscription_Id,
> subscriptions.Creator AS Receiver,
> CONCAT_WS('
> ',subscribers.First_Name,subscribers.Middle_Name,subscribers.Last_Name) AS
> subscribers_Full_Name,
> subscribers.Email_Address AS subscribers_Email_Address,
> discussions.Discussion_Id AS Subscription_Type_Record_Id,
> Concat(
> "New post to the ",
> discussions.Subject,
> " discussion."
> ) AS Subject,
> Concat(
> "At ",
> DATE_FORMAT(posts.Created_On,
> "%a %b %D, %h:%i %p"),
> " ",
> if( posts.Post_Anonymously = 1, "Anonymous", participants.UserName),
> " said:\n\n", posts.Content
> ) AS Message,
> Concat(
> " (link:
>
http://FairChoice.org/Vote_Sizing/Discussions.php?subjt...cussionid=";,
> discussions.Discussion_Id,
> ")"
> ) AS Message_Footer
> FROM (((
> subscriptions INNER JOIN
> posts ON discussions.Discussion_Id = subscriptions.Discussion_Id) INNER
JOIN
> members subscribers ON subscribers.Member_Id = subscriptions.Creator)
INNER
> JOIN
> members participants ON posts.Creator = participants.Member_Id) INNER JOIN
> discussions ON discussions.Discussion_Id = posts.Discussion_Id
> WHERE
> subscriptions.Frenquency_Id = {Frequency_Id}
> AND subscriptions.Notification_Id < {Notification_Id}
> AND posts.Created_On >= '{Last_Daily_Message_Subscription_Delivery}'
> ORDER BY
> Subject, posts.Created_On desc
>
> - Steve
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
