Could anyone please assist me on the following SQL (MSS) to be converted in to Dynamic TypeList? (don't get scared since its big sql, but its repeating CASE statement though!)

select id_num_vp_holder,
    sa_vp_cde,
    vp_num,
case when vp_veh_year is not null and length(rtrim(vp_veh_year)) > 0 then
        rtrim(ltrim(vp_veh_year))
    else
        ''
    end
    +
    case when vp_veh_make is not null and length(rtrim(vp_veh_make)) > 0 then
        case when vp_veh_year is not null and length(rtrim(vp_veh_year)) > 0 then
            ' ' + rtrim(ltrim(vp_veh_make))
        else
            rtrim(ltrim(vp_veh_make))
        end
    else
        ''
    end
    +
    case when vp_veh_modle is not null and length(rtrim(vp_veh_modle)) > 0 then
        case when (vp_veh_year is not null and length(rtrim(vp_veh_year)) > 0)
             or   (vp_veh_make is not null and length(rtrim(vp_veh_make)) > 0) then
            ' ' + rtrim(ltrim(vp_veh_modle))
        else
            rtrim(ltrim(vp_veh_modle))
        end
    else
        ''
    end as VehicleDescription,
    case when cm_sa_vehcl_reg.loc_cde is not null then
        rtrim(ltrim(loc_desc)) 
    else 
        '' 
    end
    +
    case when cm_sa_vehcl_reg.bldg_cde is not null then 
        case when cm_sa_vehcl_reg.loc_cde is not null then
            ' - ' + rtrim(ltrim(building_desc))
        else 
            rtrim(ltrim(building_desc)) 
        end 
    else
        ''
    end as ParkingLocation,
    rtrim(ltrim(td_sa_vp_type_view.description)) as TypeOfPermit,
    rtrim(ltrim(cm_sa_vehcl_reg.sa_vp_cde)) + '-' + 
rtrim(ltrim(cm_sa_vehcl_reg.vp_num)) as PermitNumber, 
    case when vp_end_dte is null then
        'No expiration date'
    when vp_end_dte < getdate() then
        'Permit has expired'
    else
        convert(varchar(15), vp_end_dte, 101)
    end as PermitExpirationDate,    
    rtrim(ltrim(vp_veh_licns_num)) as LicensePlateNumber
from  cm_sa_vehcl_reg left outer join location_master
    on cm_sa_vehcl_reg.loc_cde = location_master.loc_cde
left outer join building_master
    on cm_sa_vehcl_reg.bldg_cde = building_master.bldg_cde
join cm_sa_v_prmt_mstr
    on cm_sa_vehcl_reg.sa_vp_cde = cm_sa_v_prmt_mstr.sa_vp_cde
left outer join td_sa_vp_type_view
    on cm_sa_v_prmt_mstr.sa_vp_type = td_sa_vp_type_view.value
where id_num_vp_holder = strhostid

Just give me a code sample for the following...

case when vp_veh_year is not null and length(rtrim(vp_veh_year)) > 0 then
        rtrim(ltrim(vp_veh_year))
    else
        ''
    end
    +
    case when vp_veh_make is not null and length(rtrim(vp_veh_make)) > 0 then
        case when vp_veh_year is not null and length(rtrim(vp_veh_year)) > 0 then
            ' ' + rtrim(ltrim(vp_veh_make))
        else
            rtrim(ltrim(vp_veh_make))
        end
    else
        ''
    end
    +
    case when vp_veh_modle is not null and length(rtrim(vp_veh_modle)) > 0 then
        case when (vp_veh_year is not null and length(rtrim(vp_veh_year)) > 0)
             or   (vp_veh_make is not null and length(rtrim(vp_veh_make)) > 0) then
            ' ' + rtrim(ltrim(vp_veh_modle))
        else
            rtrim(ltrim(vp_veh_modle))
        end
    else
        ''
    end as VehicleDescription,

Appreciate your help on this. Thanks.

Don't beleive this can be converted to a typed list as is, you are using a lot of system functions that aren't currently including the case statement.

We have 1000's of procs that do similar code that can not be converted at this time =(

Some of this you may be able to do on the client side by adding additional columns to the typed list using client side expressions, I do a lot of that where I really wanted to get rid of a proc but it is not very bandwith friendly as you must return all the fields then combine them on the client.

John

That kind of tricky case statements will probably never be able to formulate in llblgen pro code.

A lot of the case code is for UI usage, which can be solved in the UI as well, though sometimes needs some work. Case is planned, but in what form is not yet designed.

Well, that be the case I will go with a Typed View or SP I guess!. Thanks for your quick replys.

It seems like we have to stay away from Typed View or SP, since we will be supporting MSS and Oracle (its hard to maintain in different dbs as you all know). We are thinking of otherways to accomplish this issue!

(a) Handle the CASE statement in UI (time consuming though!!!). (b) Or Is there is any way we can just slam the SQL through LLBLGen for multiple db and get the result set as dataset? (c) Or Add ADO support layer to slam these kind of SQL.

We understand that there is lots of pros and cons associated to this but because of the complexity that we are dealing with and because of the time issue, we got to come up with a solution that can be robust enough to go through these kind of sql in exceptional scenarios.

Thanks.

I don't think gui formatting is that time consuming in all cases. the main advantage of using the client, is that you have more db power left for the query, and SQL isn't a fast performer in non-set operations, C#/VB.NET are.

The problem with CASE is that it replaces a field. You COULD try the following: implement IExpression (interface defined in the ormsupport classes, please see the Expression class sourcecode for details) on your own class which represents a CASE expression. Then in the ToQueryText() routine you build up the CASE clause using the parameters you can set yourself in the expression object.

The DQE calls for a field which has an expression set, the Expression object's ToQueryText routine. Try a small example first, then you can try to build it out further. If you run into problems, please let me know so I can guide you through it.

It's probably helpful here to take a step back and ponder that a select statement is a programming construct and not SQL. Transact SQL is a combination of SQL and a programming language. I wouldn't expect an ORM tool to have any "support" for something like this.