Clarity

 View Only
  • 1.  SQL to NSQL

    Posted Sep 05, 2013 10:53 AM
    Hi All,

    I have below Oracle SQL Code which should be converted to NSQL,,,, I am new to NSQL Please Help.....

    Select o.ODF_Parent_ID,
    o.nna_ll_type,
    o.nna_ll_cat,
    o.nna_ll_ph,
    o.nna_ll_ven,
    o.nna_ll_com,
    0.nna_ll_fu,
    i.CODE,
    i.MANAGER_ID,
    P.Fin_OBS from ODF_CA_NNA_LL o,INV_INVESTMENTS i,odf_ca_project P
    where I.ID=o.ODF_Parent_ID AND P.ID=I.ID


    Thank you !


  • 2.  RE: SQL to NSQL

    Posted Sep 05, 2013 11:01 AM

    Best advice is to "wrap" your SQL in NSQL...

    so you have SQL that looks like;

    SELECT something
    FROM somewhere
    WHERE something else

    Your NSQL should look like

    SELECT  @SELECT:DIM:USER_DEF:IMPLIED:***:my_sql.my_unique_id:column1@
    ,       @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.my_next_columnA:column2@
    ...
    ,       @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.my_next_columnZ:column27@
    FROM (
    SELECT something
    FROM somewhere
    WHERE something else
    ) my_sql
    WHERE @FILTER@

    There are lots of NSQL examples on these boards, and a few discussions about "wrapping" your SQL in NSQL as a good practice.



  • 3.  RE: SQL to NSQL
    Best Answer

    Posted Sep 05, 2013 11:34 AM
    So like this;
    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:***:my_sql.my_unique_id:uq_id@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.ODF_Parent_ID:ODF_Parent_ID@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_type:nna_ll_type@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_cat:nna_ll_cat@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_ph:nna_ll_ph@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_ven:nna_ll_ven@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_com:nna_ll_com@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_fu:nna_ll_fu@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.CODE:CODE@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.MANAGER_ID:MANAGER_ID@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.Fin_OBS:Fin_OBS@
    FROM
    (
    Select 
    i.ID||o.ID my_unique_id,             /* Note this is a GUESS for a unique identifier within your data */
    o.ODF_Parent_ID,
    o.nna_ll_type,
    o.nna_ll_cat,
    o.nna_ll_ph,
    o.nna_ll_ven,
    o.nna_ll_com,
    0.nna_ll_fu,
    i.CODE,
    i.MANAGER_ID,
    P.Fin_OBS from ODF_CA_NNA_LL o,INV_INVESTMENTS i,odf_ca_project P
    where I.ID=o.ODF_Parent_ID AND P.ID=I.ID
    ) my_sql
    WHERE @FILTER@


  • 4.  RE: SQL to NSQL

    Posted Sep 05, 2013 11:53 AM
    Thank you So much for your Efforts Dave yes your GUESS is absolutely correct.-_-


  • 5.  Re: SQL to NSQL

    Posted Aug 14, 2014 12:30 PM

    Dave,

     

    Sorry my question unprofessional. Im learning to create portlets now, and I have some difficulties for understand this script. I have a concept in SQL but I dont know NSQL. Why you insert ****** in these rows? Where can I find

     

    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:***:my_sql.my_unique_id:uq_id@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.ODF_Parent_ID:ODF_Parent_ID@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_type:nna_ll_type@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_cat:nna_ll_cat@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_ph:nna_ll_ph@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_ven:nna_ll_ven@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_com:nna_ll_com@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.nna_ll_fu:nna_ll_fu@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.CODE:CODE@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.MANAGER_ID:MANAGER_ID@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:***:my_sql.Fin_OBS:Fin_OBS@

     

    Tnks



  • 6.  Re: SQL to NSQL

    Posted Aug 14, 2014 12:46 PM

    Ok that post was a while ago - but I suspect I was suggesting that you could put "anything" in there, this comes out as the "dimension" in the query (think of it as a label grouping together similar data elements).


    So it would really look more like this (for example);

     

    SELECT   @SELECT:DIM:USER_DEF:IMPLIED:DIM1:my_sql.my_unique_id:uq_id@
    ,        @SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM1:my_sql.ODF_Parent_ID:ODF_Parent_ID@

    ...



  • 7.  Re: SQL to NSQL

    Posted Aug 14, 2014 01:51 PM

    Ok, Perfect! I searched the topic that talks about it.

     

    Very tnks!



  • 8.  Re: RE: SQL to NSQL

    Posted Aug 14, 2014 02:11 PM

    Actually I just editted an earlier comment in this thread to tidy-up formatting on the new Jive platform that these communities are now running on and I had originally entered *** - something in Jive's formatting must have converted that to ***

     

    EDIT : ha ha it happened again - what Jive is doing is converting "X X X" (three Xs in a row, no spaces) to "***" - how very strange, perhaps its an over-excited Jive swear filter?



  • 9.  Re: SQL to NSQL

    Posted Aug 14, 2014 02:24 PM

    Yes, that's exactly what's happening

     

    If you have a need for that as common example or something let me know and I can edit it.

     

    Thanks,

    Sam



  • 10.  Re: SQL to NSQL

    Posted Aug 14, 2014 02:47 PM

    No, not a common example or anything like that, just randomness. Not quite sure why X.X.X. would be deemed offensive tho'