SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert crystal to SQL


convert crystal to SQL

Author
Message
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59157 Visits: 13297
Try with a single =.

Double == is SSIS expression language Blush


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
Its say now this.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'case'.


I have change a little bit (select and from statement) And the '0000' (in crystal) that the numbers must begin from 0 1 2 3 and if it line down its 0 #0 if it is the then 10.. Its weird i know.. Smile
Attachments
Capture9.JPG (4 views, 26.00 KB)
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
Koen Verbeeck (8/6/2013)
Try with a single =.

Double == is SSIS expression language Blush


if i do that i get a syntax error.
Attachments
Capture89.JPG (8 views, 29.00 KB)
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59157 Visits: 13297
karim.boulahfa (8/7/2013)
Koen Verbeeck (8/6/2013)
Try with a single =.

Double == is SSIS expression language Blush


if i do that i get a syntax error.


I think you are missing a bracket ) at the end.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
I just trying that but is solve only the 2 and the third telling that the syntax is incorrect.
Attachments
Capture87.JPG (5 views, 27.00 KB)
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59157 Visits: 13297
karim.boulahfa (8/7/2013)
I just trying that but is solve only the 2 and the third telling that the syntax is incorrect.


You forgot to put the "P" before the + in the last expression.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
Still same.
Attachments
Capture5678.JPG (6 views, 14.00 KB)
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59157 Visits: 13297
I see three iif functions in your expression, but there should only be two. Remove the last one.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
pgt892
pgt892
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 303
Below are instructions for handling your concern in SSRS, the case expression should handle it in SQL.

I set up a simple data set to make this work, containing the following:
select
'c' as event_type,
1 as incident_ref



What I gathered from the original Crystal report is that it checks the value of the "event_type" and if it is "c" then it takes the value in "incident_ref" and applies some formatting to it.

Below is the code to make it do that in SSRS (note the ".value" after both the event_type and incident_ref field references):

=iif(Fields!Event_Type.Value = "c","R" + Format(Fields!incident_ref.Value,"00000"),iif(Fields!Event_Type.value = "i",Format(Fields!incident_ref.Value,"00000"),"P" + Format(Fields!incident_ref.Value,"00000")))



With the sample data the report returns the expected result as you can see in the picture.
Attachments
SSRS_Report.JPG (7 views, 9.00 KB)
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
pgt892 (8/7/2013)
Below are instructions for handling your concern in SSRS, the case expression should handle it in SQL.

I set up a simple data set to make this work, containing the following:
select
'c' as event_type,
1 as incident_ref



What I gathered from the original Crystal report is that it checks the value of the "event_type" and if it is "c" then it takes the value in "incident_ref" and applies some formatting to it.

Below is the code to make it do that in SSRS (note the ".value" after both the event_type and incident_ref field references):

=iif(Fields!Event_Type.Value = "c","R" + Format(Fields!incident_ref.Value,"00000"),iif(Fields!Event_Type.value = "i",Format(Fields!incident_ref.Value,"00000"),"P" + Format(Fields!incident_ref.Value,"00000")))



With the sample data the report returns the expected result as you can see in the picture.



The Expression works but the value dont change (Look pictures):

This is my sql query:

SELECT inc_data.event_type, incident.incident_ref, bldng_room.bldng_room_sc, incident.usr_ref, inc_cat.inc_cat_sc, product.product_sc, usr.usr_n, incident.inc_status, incident.date_logged, incident.inc_resolve_act, incident.inc_close_date, incident.inc_resolve_sla, sectn_dept.sectn_dept_sc, inc_prior.inc_prior_sc, incident.callback_rmk, inc_data.total_service_time, sectn_dept.dept_sc, sla.sla_sc, incident.inc_resolve_due, inc_data.u_date1, bldng.address2, sectn_dept.sectn_sc, item.item_sc, item.item_keya
FROM Assyst.dbo.bldng bldng
INNER JOIN ((((Assyst.dbo.sectn_dept sectn_dept INNER JOIN
(Assyst.dbo.inc_cat inc_cat INNER JOIN
(Assyst.dbo.inc_prior inc_prior INNER JOIN
((Assyst.dbo.product product INNER JOIN
Assyst.dbo.item item ON product.product_id=item.product_id)
INNER JOIN (Assyst.dbo.inc_data inc_data
INNER JOIN Assyst.dbo.incident incident ON inc_data.incident_id=incident.incident_id) ON item.item_id=incident.item_id) ON inc_prior.inc_prior_id=incident.inc_prior_id) ON inc_cat.inc_cat_id=incident.inc_cat_id) ON sectn_dept.sectn_dept_id=incident.sectn_dept_id)
INNER JOIN Assyst.dbo.usr usr ON incident.aff_usr_id=usr.usr_id)
INNER JOIN Assyst.dbo.sla sla ON incident.sla_id=sla.sla_id)
INNER JOIN Assyst.dbo.bldng_room bldng_room ON incident.bldng_room_id=bldng_room.bldng_room_id) ON bldng.bldng_id=bldng_room.bldng_id
WHERE NOT (inc_data.event_type='n' OR inc_data.event_type='p')
AND NOT (inc_cat.inc_cat_sc=N'RFC BACK-OFFICE' OR inc_cat.inc_cat_sc=N'RMA')
AND incident.date_logged<{ts '2013-08-07 00:00:00'} and incident.incident_ref = '64169'

this is a qeury that gets the value in expression1(Picture).jpg
Attachments
expression1.JPG (10 views, 10.00 KB)
expression2.JPG (9 views, 9.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search