aspdotnetrealtime: October 2013

Tuesday, 22 October 2013

how to join multiple tables with left join using postgresql querry

select K.*,L.* from
(select I.*,J. columnname,J.columnnamefrom
(SELECT G.*,H.columnname,H.columnnameFROM
(SELECT E.*,F.columnname,F.columnnameFROM
(select c.*,d.columnname,D.columnnamefrom
(
select B.columnname,A.columnname,A.columnnamefrom tabbranchnames B
LEFT JOIN
(
    SELECT columnname,COUNT(A.columnname) AS INVOICENO,COALESCE(SUM(A.columnname),0) AS PAIDAMOUNT
    FROM tablename A,tablename B WHERE A.columnname= B.columnname
    AND CAST(B.columnname AS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
    AND CAST(A.columnnameAS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
    AND A.columnnameNOT LIKE 'RCQ%' group by vchbranchname
   ) A on B.columnname=A.columnname
)C
LEFT JOIN
(
     SELECT columnname,COUNT(A.columnname) AS preinvno,COALESCE(SUM(A.columnname),0) AS precash
     FROM tablename A,tablename B
     WHERE A.columnname= B.columnname
     AND CAST(B.columnname AS DATE) <'01-SEP-2013' AND CAST(B.DDC_LOGINDATE AS DATE) >= '21-JAN-2013'
     AND CAST(A.columnnameAS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
     AND A.columnnameNOT LIKE 'RCQ%'
     group by columnname
)D on C.columnname=D.columnname
)E
LEFT JOIN
(
      SELECT b.columnname,COUNT(A.columnname) as chqinvoiceno,COALESCE(SUM(A.columnname),0) as chqamount
      FROM tablename A,tablename B,tablename C
      WHERE A.columnname= B.columnnameAND A.columnname= C.columnnameAND columnname= 'Y'
      AND CAST(B.columnname AS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
      AND C.columnname<= '30-SEP-2013' AND A.columnname LIKE 'RCQ%'
      group by columnname
)F
ON E.columnname=F.columnname) G
LEFT JOIN
(
SELECT columnname,COUNT(A.columnname) AS preinvoiceno,COALESCE(SUM(A.columnname),0) AS prechqamt
      FROM tablename A,tablename B,tablename C
      WHERE A.columnname= B.columnnameAND A.columnname= C.columnnameAND c.columnname= 'Y'
      AND CAST(B.columnname AS DATE) <'01-SEP-2013' AND CAST(B.columnname AS DATE) >= '21-JAN-2013'
      AND C.columnnameBETWEEN '01-SEP-2013' AND '30-SEP-2013'
      AND A.columnnameLIKE 'RCQ%'
      group by columnname
) H on G.columnname=H.columnname) I
LEFT JOIN
(
SELECT B.columnname,COUNT(A.columnname) AS jvno,COALESCE(SUM(A.columnname),0) AS jvamt
      FROM tablename A,tablename B
      WHERE A.columnname= B.columnname
      AND CAST(B.columnname AS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
      AND CAST(A.columnnameAS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
      group by columnname
) J on I.columnname=J.columnname) K
LEFT JOIN
(SELECT B.columnname,COUNT(A.columnname) AS prvjvno,COALESCE(SUM(A.columnname),0) AS prvjvamt
      FROM tablename A,tablename B
      WHERE A.columnname= B.columnname
      AND CAST(B.columnname AS DATE) <'01-SEP-2013' AND CAST(B.DDC_LOGINDATE AS DATE) >= '21-JAN-2013'
      AND CAST(A.columnnameAS DATE) BETWEEN '01-SEP-2013' AND '30-SEP-2013'
      group by columnname) L on L.columnname=K.columnname

      where K.columnnameis not null or k.columnnameis not null or l.columnnameis not null
      or l.columnnameis not null or k.columnnameis not null or k.columnnameis not null or k.columnnameis not null or k.chqamount is not null
      or k.columnnameis not null or k.columnnameis not null or k.columnnameis not null or k.columnnameis not null

jQuery to block paste in a textbox

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Block Paste</title>
    
    <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>    
     <script type="text/javascript">
          // hook to the document ready function
         $(document).ready(function () {
             
           // bind the paste operation
           $('#<%=tbAccept.ClientID%>').bind('paste',
                function (e) {
                     // prevent the default operation
                  e.preventDefault();
                    // warn the user
                     alert("Paste disabled in this textbox");
               });


          });
      
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Please enter your name: 
        <!-- the textbox you want to disable paste on -->
        <asp:TextBox runat="server" ID="tbAccept">
        </asp:TextBox>
    </div>
    </form>
</body>
</html>

Monday, 21 October 2013

how to write functions in postgresql

CREATE OR REPLACE FUNCTION getfoo(integer)
RETURNS SETOF foo AS
$BODY$
SELECT * FROM foo WHERE fooid = $1;
$BODY$
LANGUAGE sql VOLATILE;

JQUERRY GRID USEFULL FOR NEW GRIDVIEWS DESGN

Refer this link for grid we use it and share with u

http://www.datatables.net/index

and also dojo grid also usefull now a days

http://dojotoolkit.org/documentation/tutorials/1.8/working_grid/demo/rowclick.php

how to dump one database tables data into another database tables in postgresql

we use one function for dump table data into another database table is DBLINK

CREATE OR REPLACE FUNCTION dblink(text, text)
RETURNS SETOF record AS
'$libdir/dblink', 'dblink_record'
LANGUAGE c VOLATILE STRICT;
ALTER FUNCTION dblink(text, text)
OWNER TO system;

then we write query to do this task

INSERT INTO tablename
SELECT * FROM dblink('host=ip address dbname=databasename user=userid password=pwd port=portno ','select * from tablename')
AS (column names );

run the above query we get all the data in table is copied into another database table with same name and same columns.