Related questions

How can I find out which DB script these are?

2018-04-17 05:40 tind imported from Stackoverflow 
INSERT INTO accnt 
SELECT          Concat('EBB','-','@cntryCode', '-',Trim(mast.masterno))                      AS acct_srogt_id , 
                Trim(mast.masterno)                                                          AS acct_host_num ,
                mast.statusflag                                                              AS acct_sts_type_cd ,
                ''                                                                           AS prvsn_sts_type_cd ,
                ''                                                                           AS acct_auto_prvsn_cd ,
                ''                                                                           AS acct_ptfol_ctry_cd ,
                mast.armcode                                                                 AS arm_cd ,
                ''                                                                           AS acct_cury_cd ,
                ''                                                                           AS base_cury_cd ,
                ''                                                                           AS acct_blg_type_cd ,
                ''                                                                           AS acct_int_type_cd ,
                ''                                                                           AS acct_int_rt ,
                ''                                                                           AS anl_int_sprd_rt ,
                ''                                                                           AS eff_int_rt ,
                masteropendate                                                               AS acct_open_dt ,
                closingdate                                                                  AS acct_clse_dt ,
                ''                                                                           AS acct_appr_dt ,
                ''                                                                           AS acct_stm_cyc_day ,
                ''                                                                           AS acct_div_cd ,
                ''                                                                           AS effective_start_date ,
                ''                                                                           AS acct_sts_type_desc ,
                                Concat('EBB','-','@cntryCode','-',Trim(arm.bankid))          AS arm_emp_srogt_id , 
                'EBB_ACCT_01'                                                                AS prcs_id ,
                ''                                                                           AS acct_closr_rsn ,
                ''                                                                           AS prvsn_sts_type_desc ,
                ''                                                                           AS acct_acq_chnl_meth ,
                mast.segmentcode                                                             AS acct_segmt_cd ,
                seg.NAME                                                                     AS acct_segmt_desc ,
                ''                                                                           AS intrnl_inst_clas_cd ,
                ''                                                                           AS intrnl_instl_clas_cd_desc ,
                arm.NAME                                                                     AS arm_nm ,
                ''                                                                           AS acct_last_cr_tran_dt ,
                ''                                                                           AS acct_book_loc ,
                accttype                                                                     AS acct_cat_cd ,
                ''                                                                           AS acct_cat_desc ,
                ''                                                                           AS busn_cat_cd ,
                ''                                                                           AS busn_cat_cd_desc ,
                ''                                                                           AS acct_int_type_cd_desc ,
                ''                                                                           AS actg_meth ,
                ''                                                                           AS acct_sts_chg_dt ,
                ''                                                                           AS prvsn_sts_chg_dt ,
                ''                                                                           AS acct_shrt_nm ,
                ''                                                                           AS intnl_bank_acct_num ,
                ''                                                                           AS deal_lien_num ,
                ''                                                                           AS prod_cd ,
                ''                                                                           AS lend_type ,
                ''                                                                           AS lend_type_desc ,
                ''                                                                           AS orig_maturty_dt ,
                ''                                                                           AS maturty_dt ,
                ''                                                                           AS ln_prps_cd ,
                ''                                                                           AS ln_prps_cd_desc ,
                ''                                                                           AS ptfol_id_num ,
                ''                                                                           AS ptfol_desc ,
                ''                                                                           AS ln_bss_cd ,
                ''                                                                           AS revn_chrg_cd ,
                ''                                                                           AS revn_chrg_cd_desc ,
                ''                                                                           AS ln_type_cd ,
                ''                                                                           AS ln_type_desc ,
                ''                                                                           AS ln_scty_cd ,
                ''                                                                           AS ln_clas ,
                ''                                                                           AS ln_appl_dclne_cd ,
                ''                                                                           AS ln_risk_clas_cd ,
                ''                                                                           AS drp_acct_fl ,
                ''                                                                           AS impairment_fl ,
                ''                                                                           AS stm_cd ,
                ''                                                                           AS acct_stm_addressing_titl ,
                ''                                                                           AS acct_drmt_dt,
                                Concat('EBBS','-','@cntryCode','-',Trim(rel.relationshipno)) AS prty_srogt_id, 
                ''                                                                           AS drp_acct_num,
                                Concat('EBB','-','@cntryCode','-',Trim(mast.branchcode))     AS brnch_srogt_id , 
                t.riskcode                                                                   AS risk_cd,
                risk.description                                                             AS risk_cd_desc
FROM            @PREFIX2_MAST MAST 
LEFT OUTER JOIN @PREFIX2_ARM ARM 
ON              mast.armcode=arm.armcode 
LEFT OUTER JOIN @PREFIX2_SEG SEG 
ON              mast.segmentcode=seg.segmentcode 
LEFT JOIN       @PREFIX2_MASTREL MSTREL 
ON              mstrel.masterno=mast.masterno 
AND             mstrel.primaryflag='Y' 
LEFT JOIN       @PREFIX2_REL REL 
ON              mstrel.relationshipno=rel.relationshipno 
AND             rel.relationshiptype='C' 
LEFT JOIN 
                ( 
                       SELECT riskcode, 
                              base.masterno 
                       FROM   ( 
                                       SELECT   riskcode, 
                                                masterno , 
                                                Row_number() OVER (partition BY masterno ORDER BY seqno DESC) AS max_row
                                       FROM     @PREFIX2_RSKIND RSKND) base 
                       WHERE  base.max_row=1) t 
ON              mast.masterno=t.masterno 
LEFT JOIN       @PREFIX2_RISK RISK

How can I find out which DB script these are please?

1 answer

  • answered 2018-04-17 07:20 sujit

    This snippet seems to be an incomplete extract from a T-SQL script. Refer the @table specific documentation here

See also questions close to this topic

  • Mutliple strings containing duplicates

    I am required to do the following : 'The Address should be concatenated into a lower case string with no spaces and then compared against the other address'. I have the below sql - with no schema this will be hard to answer but i really just want to know how i can add in the part to concat the address into lower case with no spaces and then compare against other addresses. 

    with dups as (
 select distinct p.GtId
 from CORE.WeccoParty p
 join CORE.WeccoPartyAddress a ON p.GtId = a.GtId

 where exists (select 1
           from CORE.WeccoParty sub_p
           left join CORE.WeccoPartyAddress sub_a 
             on sub_p.GtId = sub_a.GtId and sub_a.AddressType = 'Mailing'
           where (p.FirstName     =  sub_p.FirstName
                  and p.LastName  =  sub_p.LastName
                  and p.Dob       =  sub_p.Dob
                  and p.GtId      <> sub_p.GtId)
                  and a.MailingAddress =  sub_a.MailingAddress
                  and a.GtId           <> sub_a.GtId
              ) 
)
  • Redshift - Using output from a nested query as input to another query

    I have a nested query that goes like 

    with q1 as (select * from table1), 
q2 as (select * from table2) 
select q1.col1, q2.col2,listagg(q1.id || ' - ' || q2.id, ', ') as unique_id  
from q1 join q2 on q1.id = q2.id;

    I am trying to use unique_id obtained from the above query and query another table.

    Could anyone guide me how could I use this new column created in the above query used as an input to another query. Thanks

    I am using Redshift DB. Thanks

  • Multiple values in decode Oracle SQL

    I have simple question but I cannot find the solution. I need:

    WHERE
(tableABC.name = (DECODE (:p_type,'Morning', 'GOODMORNING',
                                  'Afternoon', 'GOODAFTERNOON',
                                  'Day', IN('GOODMORNING','GOODAFTERNOON'))

    Unfortunately it does not work. I cannot use any other table because it is used in report and "Morning, Afternoon, Day" are imagined words (it should be user friendly). The seconds exist really in table but there are also many next values for this column (e.g.: 'GOODEVENING', 'GOODNIGHT'). I need select only two values from the particular column with many different values which will be selected under "Day" selection.

    Thanks a lot, D.

  • error:can not find Symbol method execAction(String)

    I tried to use open source project for school app. However, I coded my own database and deleted privious one. When I try to run my app, log cat displays following error: error:can not find Symbol method execAction(String). There several execAction function in the project and each of them has redline underlined.Please somebody see my code and tell me what the issue is. Appbase.execAction Here is what I tried:

    AppBase.class

    package com.andisofttechnology.myapplication;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.GridView;

import java.util.ArrayList;

public class AppBase extends AppCompatActivity {

    ArrayList<String> basicFields;
    gridAdapter adapter;
    public static ArrayList<String> divisions ;
    GridView gridView;
    public static DatabaseHandler handler;
    public static Activity activity;

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        super.onCreateOptionsMenu(menu);
        MenuInflater inflater = getMenuInflater();
        inflater.inflate(R.menu.mai_menu, menu);
        return true;
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.base_layout);
        basicFields = new ArrayList<>();
        handler = new DatabaseHandler(this);
        activity = this;

        getSupportActionBar().show();
        divisions = new ArrayList();
        divisions.add("S1 COMPUTER SCIENCE");
        divisions.add("S2 COMPUTER SCIENCE");
        divisions.add("S3 COMPUTER SCIENCE");
        divisions.add("S4 COMPUTER SCIENCE");
        divisions.add("S5 COMPUTER SCIENCE");
        divisions.add("S6 COMPUTER SCIENCE");
        divisions.add("S7 COMPUTER SCIENCE");
        gridView = (GridView)findViewById(R.id.grid);
        basicFields.add("ATTENDANCE");
        basicFields.add("SCHEDULER");
        basicFields.add("NOTES");
        basicFields.add("PROFILE");
        basicFields.add("CGPA CALCULATOR");
        adapter = new gridAdapter(this,basicFields);
        gridView.setAdapter(adapter);
    }

    public void loadSettings(MenuItem item) {
        Intent launchIntent = new Intent(this,SettingsActivity.class);
        startActivity(launchIntent);
    }

    public void loadAbout(MenuItem item) {
        Intent launchIntent = new Intent(this,About.class);
        startActivity(launchIntent);
    }
}

    Scheduler.class

    package com.andisofttechnology.myapplication;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.ContentUris;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.support.design.widget.FloatingActionButton;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;

import java.util.ArrayList;

public class scheduler extends AppCompatActivity implements AdapterView.OnItemLongClickListener{

    ListView listView;
    ArrayAdapter adapter;
    ArrayList<String> subs;
    ArrayList<String> subx;
    ArrayList<String> times;
    Activity activity = this;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_scheduler);

        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab_sch);
        assert fab != null;
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Intent launchIntent = new Intent(getBaseContext(), make_schedule.class);
                startActivity(launchIntent);
            }
        });

        subs = new ArrayList<>();
        times = new ArrayList<>();
        subx = new ArrayList<>();
        listView = (ListView)findViewById(R.id.schedulerList);
        loadSchedules();
        listView.setOnItemLongClickListener(this);
    }

    private void loadSchedules() {
        subs.clear();
        times.clear();
        String qu = "SELECT * FROM SCHEDULE ORDER BY subject";
        Cursor cursor = AppBase.handler.execQuery(qu);
        if(cursor==null||cursor.getCount()==0)
        {
            Toast.makeText(getBaseContext(),"No Schedules Available",Toast.LENGTH_LONG).show();
        }else
        {
            cursor.moveToFirst();
            while(!cursor.isAfterLast())
            {
                subx.add(cursor.getString(1));
                subs.add(cursor.getString(1) + "\nfor " + cursor.getString(0) + "\nat " + cursor.getString(2) + " : " + cursor.getString(3));
                times.add(cursor.getString(2));
                cursor.moveToNext();
            }
        }
        ArrayAdapter adapter = new ArrayAdapter(getBaseContext(),android.R.layout.simple_list_item_1,subs);
        listView.setAdapter(adapter);
    }

    @Override
    public boolean onItemLongClick(AdapterView<?> parent, View view, final int position, long id) {
        AlertDialog.Builder alert = new AlertDialog.Builder(activity);
        alert.setTitle("Delete Schedule?");
        alert.setMessage("Do you want to delete this schedule ?");

        alert.setPositiveButton("YES", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                String qu = "DELETE FROM SCHEDULE WHERE subject = '" + subx.get(position) + "' AND timex = '" + times.get(position) + "'";
                if (AppBase.handler.execAction(qu)) {
                    Toast.makeText(getBaseContext(), "Deleted", Toast.LENGTH_LONG).show();
                    loadSchedules();
                } else {
                    Toast.makeText(getBaseContext(), "Failed", Toast.LENGTH_LONG).show();
                    loadSchedules();
                }
                dialog.dismiss();
            }
        });
        alert.setNegativeButton("NO", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                dialog.dismiss();
            }
        });
        alert.show();
        return true;
    }

    public void refresh(MenuItem item) {
        loadSchedules();
    }
@Override
    public boolean onCreateOptionsMenu(Menu menu) {
        super.onCreateOptionsMenu(menu);
        MenuInflater inflater = getMenuInflater();
        inflater.inflate(R.menu.scheduler_menu, menu);
        return true;
    }
}
  • Data schema to store heterogeneous data in parquet files inside HDFS

    We want to store data like:

    {"event":"click", "click_url":..., ...},
{"event":"view","view_item":...., ...}

    Each event (click/view/load/hit....) have different fields.

    Currently, we group all kind of events in same parquet files, this end up with 90 fields, most the time null (sparse data because for a view event, all click_* fields are null).

    As we plan to add more and more events, this is not scalable (I cant image a parquet file with more than 128 columns!).

    We use partition already: year=2018/month=8/day=20, a single Hive table and Apache Spark to query.

    What could be the best architecture (maybe a partition by event with a Hive table associated) to fit this?

  • Oracle ODBC read fails with left join

    I have a small Problem but its annoying me extremly. i want to left join a table but i get only the error that the odbc read fails. When i comment out the left join block the query works.

    im pretty sure that theres no typo and im frustated..

    any ideas?

    select 
*
from
SYSADM.KATARTIKEL

left join SYSADM.TBLFALL
on SYSADM.KATARTIKEL.ITBROWID = SYSADM.TBLFALL.ITBROWID

where rownum <= 150;

    heres the error its in german..

    Fehler beim Lesen über ODBC
select 
*
from
SYSADM.KATARTIKEL

left join SYSADM.TBLFALL
on SYSADM.KATARTIKEL.ITBROWID = SYSADM.TBLFALL.ITBROWID

where rownum <= 150

    EDIT: The Solotion for this case is to create aliases:

    select 
    K.*,

    T.ITBROWID as ITID
from
    SYSADM.KATARTIKEL K

left join SYSADM.TBLFALL T
on K.ITBROWID = T.ITBROWID

where 
    rownum <= 150;
  • Fehler Bei Aufruf von DBMS_SCHEDULER.ENABLE

    I execute this code:

    CURSOR JOB_RUNS_cur (P_JOB_NAME IN VARCHAR2)
  IS
     SELECT COUNT ( * )
       FROM ALL_SCHEDULER_RUNNING_JOBS
      WHERE OWNER = 'BATCH'
        AND JOB_NAME = P_JOB_NAME;

   DerAktuelleJob   := 'BATCH_SST_AENDERUNGEN_JOB';

     OPEN JOB_RUNS_cur (DerAktuelleJob);

     FETCH JOB_RUNS_cur INTO Anzahl;

     CLOSE JOB_RUNS_cur;

     -- Bei Anzahl=0 läuft der Job aktuell nicht und kann gestartet werden
     IF Anzahl = 0 THEN
        **DBMS_SCHEDULER.ENABLE ('BATCH.BATCH_SST_AENDERUNGEN');**
        DBMS_SCHEDULER.ENABLE ('BATCH.' || DerAktuelleJob);
     END IF;

    and I sometimes I get this error at DBMS_SCHEDULER.ENABLE ('BATCH.BATCH_SST_AENDERUNGEN');:

    Error in sql query: ORA-01013: Benutzer hat Abbruch des aktuellen Vorgangs angefordert
>ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-01013: Benutzer hat Abbruch des aktuellen Vorgangs angefordert
ORA-06512: in "SYS.DBMS_ISCHED", Zeile 4790
ORA-06512: in "SYS.DBMS_SCHEDULER", Zeile 3020

    Who can help me to analyze/resolve the problem?

  • Passing list to oracle stored procedure in Java but getting only one element in list type object

    I'm passing a list object [1, 2, 3, 4, 5] to Oracle stored procedure by creating a custom type in oracle package as below - 

    create type index_num_rec as object (index_number varchar2(1000));

create type index_num_arr as table of index_num_rec ;

    Procedure code - 

    procedure get_info (i_index_list in index_num_arr)
is
......
......
begin
.....
select * from index_data where index_number in (select index_number from table (i_index_list));
.....
end;

    Java Code - 

    public List getIndexDetails(){
List objList = null;
    try {
        Session session = HibernateUtil.currentSession();
        StoredProcUtil util = new StoredProcUtil();
        List inoutList = new ArrayList();
        int num = 1;

        SQLArray sqlArr = new SQLArray("INDEX_NUM_ARR", "INDEX_NUM_REC");   
        sqlArr.setListElementJavaClassName("com.pearson.ebook.persistence.storedproc.IndexNumRec");
        List indexNumRecList = [1, 2, 3, 4, 5];

        sqlArr.setObjectList(indexNumRecList );         
        sqlArr.setPropertyArray("INDEX_NUMBER");
        StoredProcParam param1 = new StoredProcParam(StoredProcParam.SQLARRAY_TYPE, sqlArr, num,
                    StoredProcParam.PARAMETER_TYPE_IN);
        inoutList.add(param1);
        num++;

        util.executeStoredProc(session.connection(), "DEMO_PKG.GET_INFO", inoutList);
    } catch (SQLException sql) {
        logger.error("SQLException occurred in getRegions: ", sql);

        throw new DataAccessException("SQLException occurred in getRegionsV2: ", sql);
    }

    return objList;

}

    Now, instead of index_number in (1,2,3,4,5)

    I'm getting last element as - index_number in (5)

    Please guide, if I missed something.

  • Support indexing and clustering for a spark dataframe

    I have a spark dataframe that needs to be written to HDFS as a hive table. We know that SparkSQL doesn't support 'Create Index' and bucketing SQL queries. However I need to create a table with both of them enabled, so that the table can be accessed by other applications in a performance efficient way.

    What are the options available now?

    For eg, can we execute the query via a JDBC connection to hiveServer2? Is there a way to execute a single query for all the records?

    Please note that, my question is not about why spark doesn't support indexing. But rather about how can I use JDBC or other means in a performance efficient way to write a dataframe to a hive table with indexing and clustering.

  • How do I select count of distinct persons across all orders?

    So this is my use case. I have a table that has 3 columns: person_id, product, and country. The same person could have ordered different product at different times. My requirement is to get a count of how many distinct persons have ordered in this country across all of the products.

    0001 12345 usa
0001 12346 usa
0001 12347 usa
0001 12348 usa
0001 12349 usa
0002 12345 usa
0002 12347 usa
0003 12345 usa

    So with this data set, I should get 3 as the number of distinct persons who ordered across all of these products. How do I get this via hive query?

  • hive query fix- with inequalities

    I have 2 two tables with id as in common and rest are unmutual.

    Table 1:
ID  Adate   BDate
Test    18  21
Test    25  28

Table 2:
ID  Wdate desdate
Test    18  22
Test    25  29

    When I apply inequilties in hive, it's not working as per my required logic.

    logic: IF Wdate<=BDate include that record with hard coded value as '1', if it fails for Wdate>Bdate with that population with hard coded '0'

    And also BDate is actually Adate+3 days added to it Desired output:

    ID  Adate   BDate   Wdate   desdate Info
Test    18  21  18  22  1
Test    25  28  25  29  1

    With current query I'm getting 3 records,Can any help on how to fix;

  • AWS : Redshift vs traditional dbms

    In traditional DBMS when we try to execute a insert into table and delete from the same table (delete just the data not table) I remember it will result in a deadlock.

    With Redshift when I delete data and when I insert Data at the same time I’m able to do it.

    How is this possible is the Redshift architecture different from traditional RDBMS when it comes to this type of Deadlock situations.

  • teradata How could i present this format "FORMAT TIMESTAMPE "09:12:47 18 JUL 2016"

    How could i present this format "FORMAT TIMESTAMPE "09:12:47 18 JUL 2016"

  • Spark SQL writing DF to Teradata table using overwrite mode drops table though truncate is True

    I'm trying to write the DF records to Teradata table using Spark JDBC.

    Sample code:

    df.write.format("jdbc")\
        .option("url", jdbcUrl)\
        .option("driver", "com.teradata.jdbc.TeraDriver") \
        .option("dbtable", dbTableName) \
        .option("user", userName)\
        .option("password", passWord) \
        .option("numPartitions", 3) \
        .option("truncate", True) \
        .mode('overwrite')\
        .save()

    It is always drop the table and create the table again. When it creates the table again, its creating the table as SET table instead of MULTISET table.

    As per Spark documentation, when i pass truncate as True, it should truncate the table instead of drop the table.

    This is a JDBC writer related option. When SaveMode.Overwrite is enabled, this option causes Spark to truncate an existing table instead of dropping and recreating it. This can be more efficient, and prevents the table metadata (e.g., indices) from being removed. However, it will not work in some cases, such as when the new data has a different schema. It defaults to false. This option applies only to writing.