Click here to Skip to main content
15,889,096 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SELECT salesmast.*,
       ledgermast.ledgername          AS Customer,
       areamast.areaid,
       areamast.areadesc,
       LedgerMast_1.ledgername        AS SalesLedger,
       ledgermast.phone,
       ledgermast.subareaid           AS SubAreaID2,
       ledgermast.salesmanid          AS SalesmanID2,
       salestypemast.salestypedesc,
       salestypemast.salestypestatus,
       ( Iif(Isnull(salestypestatus), 'S', salestypestatus) & '/' &
         Format(Val(
         salesmast.invno), "00000") ) AS STDcNo,
       tablesmast.tablecode,
       ordertypemast.ordertypedesc,
       operators.operatorname,
       salesmanmast.salesmanname,
       doctormast.doctorname
FROM   (((((((salesmast
              LEFT JOIN (ledgermast
                         LEFT JOIN areamast
                                ON ledgermast.areaid = areamast.areaid)
                     ON salesmast.customerledgerid = ledgermast.ledgerid)
             LEFT JOIN ledgermast AS LedgerMast_1
                    ON salesmast.salesledgerid = LedgerMast_1.ledgerid)
            LEFT JOIN salestypemast
                   ON salesmast.salestypeid = salestypemast.salestypeid)
           LEFT JOIN tablesmast
                  ON salesmast.table_id = tablesmast.table_id)
          LEFT JOIN ordertypemast
                 ON salesmast.ordertypeid = ordertypemast.ordertypeid)
         LEFT JOIN operators
                ON salesmast.operatorid = operators.operatorid)
        LEFT JOIN salesmanmast
               ON salesmast.salesmanid = salesmanmast.salesmanid)
       LEFT JOIN doctormast
              ON salesmast.doctorid = doctormast.doctorid; 


What I have tried:

SELECT salesmast.*,
       ledgermast.ledgername          AS Customer,
       areamast.areaid,
       areamast.areadesc,
       LedgerMast_1.ledgername        AS SalesLedger,
       ledgermast.phone,
       ledgermast.subareaid           AS SubAreaID2,
       ledgermast.salesmanid          AS SalesmanID2,
       salestypemast.salestypedesc,
       salestypemast.salestypestatus,
       ( Iif(Isnull(salestypestatus), 'S', salestypestatus) & '/' &
         Format(Val(
         salesmast.invno), "00000") ) AS STDcNo,
       tablesmast.tablecode,
       ordertypemast.ordertypedesc,
       operators.operatorname,
       salesmanmast.salesmanname,
       doctormast.doctorname
FROM   (((((((salesmast
              LEFT JOIN (ledgermast
                         LEFT JOIN areamast
                                ON ledgermast.areaid = areamast.areaid)
                     ON salesmast.customerledgerid = ledgermast.ledgerid)
             LEFT JOIN ledgermast AS LedgerMast_1
                    ON salesmast.salesledgerid = LedgerMast_1.ledgerid)
            LEFT JOIN salestypemast
                   ON salesmast.salestypeid = salestypemast.salestypeid)
           LEFT JOIN tablesmast
                  ON salesmast.table_id = tablesmast.table_id)
          LEFT JOIN ordertypemast
                 ON salesmast.ordertypeid = ordertypemast.ordertypeid)
         LEFT JOIN operators
                ON salesmast.operatorid = operators.operatorid)
        LEFT JOIN salesmanmast
               ON salesmast.salesmanid = salesmanmast.salesmanid)
       LEFT JOIN doctormast
              ON salesmast.doctorid = doctormast.doctorid; 
Posted
Updated 7-Jun-17 5:04am

Assuming you are using SQL Server, then you have to specify the replacement value, see ISNULL (Transact-SQL) | Microsoft Docs[^].
 
Share this answer
 
Comments
Member 12905644 7-Jun-17 3:54am    
Above i dont replacement value
CPallini 7-Jun-17 4:07am    
SQL Server IsNull function requires a replacement value.
i think you need test IS NULL rather than the function ISNULL()

see IS NULL (Transact-SQL) | Microsoft Docs[^]
 
Share this answer
 
Replace:
Iif(Isnull(salestypestatus), 'S', salestypestatus)

with:
IsNull(salestypestatus, 'S')
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900